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

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

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)&":"&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:

="1" & ":" & ROUND((A1/B1),0)

Note the spaces before and after &.

What this means is that "1" and ":" 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

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)&":"&B1/GCD(A1,B1)

This is a more mathematical formula rather than a text manipulation based on.

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

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

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

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

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

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

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

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)&":"&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:

="1" & ":" & ROUND((A1/B1),0)

Note the spaces before and after &.

What this means is that "1" and ":" 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

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)&":"&B1/GCD(A1,B1)

This is a more mathematical formula rather than a text manipulation based on.

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

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

The answer is

Examples related to excel

Tags