« Crossfooting
Beginning VBA: Common Objects, Properties, and Methods »

Formatting ratios

29th April 2004, 12:05 pm by Dick Kusleika

To show the result of division as a ratio, you can use a custom number format. For example, the current ratio is current assets divided by current liabilities. Typically, this ratio is shown as x:1, or like this

Ratio

The custom number format applied to the current ratio is set by using Format>Cells>Number

RatioFC

This format works because the current ratio is always shown compared to one. If you want to show a ratio, but the denominator is unknown, you can use tip81 from The Spreadsheet Page.

Category: Formatting  |  Comment (RSS)

11 Comments

  1. Paul Edmunds:

    Thank you! The ratio set up worked perfectly!!!!!!!!!!!!!!!!!!!!
    Paul

    11 May 2006, 9:15 pm

  2. Dan Oltman:

    This reminded me exactly what to do. I knew it was right the second I saw it. Thank you very much, job well done.
    dano

    20 July 2007, 7:41 am

  3. Nubiedu:

    I have been trying to figure out for days how to do ratios in excel and I was so glad when I came across this…Thanks to whoever posted this

    4 September 2007, 10:32 am

  4. Leonard:

    Thank you for making this function so simple and so easy. I was able to perform a task which would normally have taken me an hour to research in only a matter of five minutes. Much appreciated!

    28 November 2007, 2:56 pm

  5. kc:

    Thank you

    23 February 2008, 7:51 am

  6. frank:

    Another amazing formatting trick: type a ratio, e.g. 6/5, and precede it by a 0, that is: 0 6/5. See what Excel displays.

    27 February 2008, 12:43 am

  7. Gail K:

    Hi, this custom type does not exist in Excel 2007; any idea how to display a ration in this version?

    21 March 2008, 8:30 am

  8. Dick Kusleika:

    You have to create custom types yourself (although MS creates some to get you started). Choose Custom and type this in the textbox.

    21 March 2008, 1:44 pm

  9. Sarah:

    Thank you so much for posting this. It was a big help with my accounting project!

    22 November 2008, 10:34 pm

  10. Ifthar:

    Here is another way of doing this
    =TEXT(B1/B2,”0.0″)&” : 1″

    25 November 2008, 12:41 am

  11. Keith:

    I came up with this when wanting to show the ratio between two numbers

    =”1:”&(A1/B1)

    then refined it to show the ratio to only two decimal places

    =”1:”&ROUND(A1/B1, 2)

    2 December 2008, 6:09 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment