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

Question

Hi,

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.

Answer 1

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!).

Answer 2

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.

Answer 3

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) & ":" & B1/GCD(A1; B1)
2  25         5        =A2/GCD(A2; B2) & ":" & 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 & ":" & B1/C1
2  25         5        =GCD(A2; B2)   =A2/C2 & ":" & B2/C2

Answer 4

Dear Sir,

I am also puup the formula but sorry to inform you that my result is not shwon in excel file.

so, pls advice me, how can i get the ratio (i.e. 8/4 ) in excel file.

with regards,

deepak Kumar

Answer 5

Thanks ya'll. I used this:

=CONCATENATE((number1/GCD(number1,number2)),":",((number2/GCD(number1,number2))))

If you've got 2007 this works great.

Answer 6

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

Hope this helps.