Calculate a Ratio in Excel

In Excel, if you divide 2 by 8, the result is 0.25. if you format the cell as a fraction, the cell might show 1/4 as the result.

What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4? There may be other ways, but here's the formula that I used.

=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2) 

This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions. It divides each cell by the greatest common divisor (GCD), and puts a colon between the two numbers. Would you use a different formula? __________________

18 comments to Calculate a Ratio in Excel

  • Ziggle

    Thank! I have an immediate use for this.

  • elizabeth Muwonge

    Works well if numbers have a common denominator or divisor.

    Without a GCD the actuall numbers display for example

    17 and 20 will display as 17:20

    Any suggestions on how to resolve this?

  • chris

    Thanks for the info Debra. IIt was good to be able to follow your example. Some of my numbers haven't divided easily either but it helped to have the ratio formula as a starter and benchmark.

  • Thanks Chris, glad the example helped you.

    Elizabeth, what result would you want to see, instead of 17:20?

  • Mark

    elizabeth:

    17:20 (17/20) can't be reduced anymore. What needs to be resolved?

  • totbean

    Elizabeth – when the result is irreducible, just estimate – if you can get away with it : 17:20 = 17/20 = 0.85 = 1:1.85. Not good enough for precision engineering but adequate for other applications.

  • Satya Kumar

    If you require the value to be precisely as what excel calculates, just replace the GCD function with MIN stastical function. The only thing you need to take care is that the numbers should be in ascending order, if you want it in the format "1:XX". Otherwise, the result would be "XX:1? if the numbers are in descending order.
    And totbean, just for your information 1:1.85 is not the same as 0.85. "0.85 = 1:1.1764?
    I am not so good at maths

  • A

    @ Debra: can u pls give any other formula...the formula u gave here is not working

  • Try copying the formula again -- perhaps the " marks were copied as curly quotes last time, instead of straight quotes.

  • JOhn

    Great timp Stya. How do you control the number of decimal places the MIN statistical function calculates to? Something in the cell format?

  • JOhn

    I meant 'tip' :[ Sorry.

  • Satya Kumar

    John, Cell format is something that is used to control our view what excel does. so there is no way that you can control that using cell format. Even you change the format, the result does not vary.
    what you can do is to use the "Round" function with either "up" or "down" or as it is to control the result. One another way to keep control over excel calculation is to change it from excel options itself, the precision calculation. I am not sure about this, but did come across elsewhere.

  • AJ

    Hi Debra, I tried your formula and it did not work. It is "#NAME?" instead of a figure. And no, my quotes are not the curley ones, they are the straight ones just like yours.

    Please advise. Thank You:)

  • AJ, do you have the Analysis Toolpak installed? That's where the GCD formula is, in Excel 2003 and earlier versions.

  • AJ

    Hi Debra,

    Thanks a million. It works now and this is great:)

  • Rick Rothstein (MVP - Excel)

    If you know the largest number will never have more than 7 digits in it (seems to be an Excel limit for this method), I think you can use the following formula (which does not require the Analysis ToolPak add-in) to calculate your ratio...

    =SUBSTITUTE(TEXT(B2/C2,"#######/#######"),"/",":")

  • Rick Rothstein (MVP - Excel)

    A **quick** test seems to show that I don't need all those # signs in the numerator for the formula I posted earlier, this seems to work the same way...

    =SUBSTITUTE(TEXT(B2/C2,"#/#######"),"/",":")

  • Dawn

    Rick- can you make it a 1:XXX with the above formula?