How do I display a ratio in Excel in the format A B

Question

I have an Excel spreadsheet of data like   ColumnA ColumnB 33        11 25        5 6         4   What i would like to do is add a third column which shows the ratio of columnA to columnB in the format of A B  For example   ColumnA ColumnB   Ratio 33        11       3 1 25        5    5 1 6         4    3 2   Does anyone know of a way to achieve this   Thanks in advance

User · Answer

Lets assume you have data in D and E cells    Here is an easiest ratio displaying fn by my frnd  Karthik    ROUND D7 E7  2   amp      amp   E7 E7

User · Answer

At work we only have Excel 2003 available and these two formulas seem to work perfectly for me     ROUND SUM B3 C3  0   amp   1    or   B3 GCD B3 C3  amp     amp C3 GCD B3 C3

User · Answer

At work we only have Excel 2003 available and these two formulas seem to work perfectly for me     ROUND SUM B3 C3  0   amp   1    or   B3 GCD B3 C3  amp     amp C3 GCD B3 C3

User · Answer

The second formula on that page uses the GCD function of the Analysis ToolPak  you can add it from Tools   Add-Ins    A1 GCD A1 B1  amp     amp B1 GCD A1 B1   This is a more mathematical formula rather than a text manipulation based on

User · Answer

Thanks ya ll  I used this    CONCATENATE  number1 GCD number1 number2         number2 GCD number1 number2       If you ve got 2007 this works great

User · Answer

Below is the formula I use  I had a problem using GCD  because I use fairly large numbers to calculate the ratios from  and I found ratios such as  209 1024  to be less useful than simply rounding so it displays either  1   or   1   I also prefer not to use macros  if at all possible  Below is the result    IF A1 gt B1   ROUND A1 B1 0   amp     amp  B1 B1     A1 A1  amp     amp  ROUND B1 A1 0       Some of the formula is unnecessary  e g    A1 A1    but I included it to show the logic behind it  Also  you can toggle how much rounding occurs by playing with the setting on each ROUND function

User · Answer

The second formula on that page uses the GCD function of the Analysis ToolPak  you can add it from Tools   Add-Ins    A1 GCD A1 B1  amp     amp B1 GCD A1 B1   This is a more mathematical formula rather than a text manipulation based on

User · Answer

Try this formula    SUBSTITUTE TEXT A1 B1                   Result   A   B   C 33  11  3 1 25  5   5 1 6   4   3 2   Explanation    TEXT A1 B1        turns A B into an improper fraction SUBSTITUTE      replaces the     in the fraction with a colon   This doesn t require any special toolkits or macros  The only downside might be that the result is considered text--not a number--so you can easily use it for further calculations     Note  as  Robin Day suggested  increase the number of question marks     as desired to reduce rounding  thanks Robin

User · Answer

Lets assume you have data in D and E cells    Here is an easiest ratio displaying fn by my frnd  Karthik    ROUND D7 E7  2   amp      amp   E7 E7

User · Answer

I found this to be the easiest and the shortest  I however rounded off to zero decimal places    quot 1 quot   amp   quot   quot   amp  ROUND  A1 B1  0  Note the spaces before and after  amp   What this means is that  quot 1 quot  and  quot   quot  are seen as additional non-formula information to the overall formula  The ROUND function rounds off A1 B1 that is the basic formula to 0 decimal places  you can try changing to 1 2 3    decimal places  I hope I made this clear

User · Answer

You are looking for the greatest common divisor  GCD    You can calculate it recursively in VBA  like this   Function GCD numerator As Integer  denominator As Integer    If denominator   0 Then     GCD   numerator   Else     GCD   GCD denominator  numerator Mod denominator    End If End Function   And use it in your sheet like this      ColumnA   ColumnB   ColumnC 1  33        11         A1 GCD A1  B1   amp       amp  B1 GCD A1  B1  2  25         5         A2 GCD A2  B2   amp       amp  B2 GCD A2  B2    It is recommendable to store the result of the function call in a hidden column and use this result to avoid calling the function twice per row      ColumnA   ColumnB   ColumnC        ColumnD 1  33        11         GCD A1  B1     A1 C1  amp       amp  B1 C1 2  25         5         GCD A2  B2     A2 C2  amp       amp  B2 C2

User · Answer

You are looking for the greatest common divisor  GCD    You can calculate it recursively in VBA  like this   Function GCD numerator As Integer  denominator As Integer    If denominator   0 Then     GCD   numerator   Else     GCD   GCD denominator  numerator Mod denominator    End If End Function   And use it in your sheet like this      ColumnA   ColumnB   ColumnC 1  33        11         A1 GCD A1  B1   amp       amp  B1 GCD A1  B1  2  25         5         A2 GCD A2  B2   amp       amp  B2 GCD A2  B2    It is recommendable to store the result of the function call in a hidden column and use this result to avoid calling the function twice per row      ColumnA   ColumnB   ColumnC        ColumnD 1  33        11         GCD A1  B1     A1 C1  amp       amp  B1 C1 2  25         5         GCD A2  B2     A2 C2  amp       amp  B2 C2

User · Answer

The second formula on that page uses the GCD function of the Analysis ToolPak  you can add it from Tools   Add-Ins    A1 GCD A1 B1  amp     amp B1 GCD A1 B1   This is a more mathematical formula rather than a text manipulation based on

User · Answer

I found this to be the easiest and the shortest  I however rounded off to zero decimal places    quot 1 quot   amp   quot   quot   amp  ROUND  A1 B1  0  Note the spaces before and after  amp   What this means is that  quot 1 quot  and  quot   quot  are seen as additional non-formula information to the overall formula  The ROUND function rounds off A1 B1 that is the basic formula to 0 decimal places  you can try changing to 1 2 3    decimal places  I hope I made this clear

User · Answer

Try this formula    SUBSTITUTE TEXT A1 B1                   Result   A   B   C 33  11  3 1 25  5   5 1 6   4   3 2   Explanation    TEXT A1 B1        turns A B into an improper fraction SUBSTITUTE      replaces the     in the fraction with a colon   This doesn t require any special toolkits or macros  The only downside might be that the result is considered text--not a number--so you can easily use it for further calculations     Note  as  Robin Day suggested  increase the number of question marks     as desired to reduce rounding  thanks Robin

User · Answer

You are looking for the greatest common divisor  GCD    You can calculate it recursively in VBA  like this   Function GCD numerator As Integer  denominator As Integer    If denominator   0 Then     GCD   numerator   Else     GCD   GCD denominator  numerator Mod denominator    End If End Function   And use it in your sheet like this      ColumnA   ColumnB   ColumnC 1  33        11         A1 GCD A1  B1   amp       amp  B1 GCD A1  B1  2  25         5         A2 GCD A2  B2   amp       amp  B2 GCD A2  B2    It is recommendable to store the result of the function call in a hidden column and use this result to avoid calling the function twice per row      ColumnA   ColumnB   ColumnC        ColumnD 1  33        11         GCD A1  B1     A1 C1  amp       amp  B1 C1 2  25         5         GCD A2  B2     A2 C2  amp       amp  B2 C2

User · Answer

Below is the formula I use  I had a problem using GCD  because I use fairly large numbers to calculate the ratios from  and I found ratios such as  209 1024  to be less useful than simply rounding so it displays either  1   or   1   I also prefer not to use macros  if at all possible  Below is the result    IF A1 gt B1   ROUND A1 B1 0   amp     amp  B1 B1     A1 A1  amp     amp  ROUND B1 A1 0       Some of the formula is unnecessary  e g    A1 A1    but I included it to show the logic behind it  Also  you can toggle how much rounding occurs by playing with the setting on each ROUND function

User · Answer

The second formula on that page uses the GCD function of the Analysis ToolPak  you can add it from Tools   Add-Ins    A1 GCD A1 B1  amp     amp B1 GCD A1 B1   This is a more mathematical formula rather than a text manipulation based on

User · Answer

You are looking for the greatest common divisor  GCD    You can calculate it recursively in VBA  like this   Function GCD numerator As Integer  denominator As Integer    If denominator   0 Then     GCD   numerator   Else     GCD   GCD denominator  numerator Mod denominator    End If End Function   And use it in your sheet like this      ColumnA   ColumnB   ColumnC 1  33        11         A1 GCD A1  B1   amp       amp  B1 GCD A1  B1  2  25         5         A2 GCD A2  B2   amp       amp  B2 GCD A2  B2    It is recommendable to store the result of the function call in a hidden column and use this result to avoid calling the function twice per row      ColumnA   ColumnB   ColumnC        ColumnD 1  33        11         GCD A1  B1     A1 C1  amp       amp  B1 C1 2  25         5         GCD A2  B2     A2 C2  amp       amp  B2 C2

User · Answer

Try this formula    SUBSTITUTE TEXT A1 B1                   Result   A   B   C 33  11  3 1 25  5   5 1 6   4   3 2   Explanation    TEXT A1 B1        turns A B into an improper fraction SUBSTITUTE      replaces the     in the fraction with a colon   This doesn t require any special toolkits or macros  The only downside might be that the result is considered text--not a number--so you can easily use it for further calculations     Note  as  Robin Day suggested  increase the number of question marks     as desired to reduce rounding  thanks Robin

User · Answer

Thanks ya ll  I used this    CONCATENATE  number1 GCD number1 number2         number2 GCD number1 number2       If you ve got 2007 this works great

User · Answer

Try this formula    SUBSTITUTE TEXT A1 B1                   Result   A   B   C 33  11  3 1 25  5   5 1 6   4   3 2   Explanation    TEXT A1 B1        turns A B into an improper fraction SUBSTITUTE      replaces the     in the fraction with a colon   This doesn t require any special toolkits or macros  The only downside might be that the result is considered text--not a number--so you can easily use it for further calculations     Note  as  Robin Day suggested  increase the number of question marks     as desired to reduce rounding  thanks Robin

[excel] How do I display a ratio in Excel in the format A:B?

Examples related to excel