Formatting ratios
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
The custom number format applied to the current ratio is set by using Format>Cells>Number
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.
Thank you! The ratio set up worked perfectly!!!!!!!!!!!!!!!!!!!!
Paul
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
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
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!
Thank you
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.
Hi, this custom type does not exist in Excel 2007; any idea how to display a ration in this version?
You have to create custom types yourself (although MS creates some to get you started). Choose Custom and type this in the textbox.
Thank you so much for posting this. It was a big help with my accounting project!
Here is another way of doing this
=TEXT(B1/B2,”0.0″)&” : 1″
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)
what if the denominator is 0? It gives me an error.
Hi
I’m new to this blog page.
I’m am desperately looking for a ratio on how to attribute a full dollar amount to various departments as a percentage.
The cost of stationery is $1200 and I have many departments to allocate a percentage to based on what they have spent.
e.g.
$1200 has been spent by the Admin Department and I have to % allocated the cost as user pays to each deparment.
The total is $1200 to distribute
HR spent $410.00
Computing $250.00
soccer $315.00
Netball $90.00
Gardening $45.00
etc.
What is the excel sum to correctly attribute a percentage of the $1200 cost to the various departments.
Michelle
Mvick@value.net.nz
Hi Michelle -
It looks to me like it would “department spent/total spent”. That is
$410/$1200, $250/$1200, $315/$1200, $90/$1200, $45/$1200 etc.
And since that only totals 93%, etc pays 7%
Am I missing something here?
…mrt
This equation formats the cell to read for example 10:1. How can I get it the other way around - as in 1:10? Thanks
@Khuwair - Just reverse it:
“1 : “0.0
Thanks Gordon. It worked perfectly!
I tried reversing it but got the order of the quotation marks wrong. I did 1:”0.0″ and the result was 1:00!
HI Everyone.
It doesn’t appear to be working for what I require. Maybe the way I communicated the query.
If I have a total spent expense and I need to divide it over the various departments that have lesser or higher spent. Then the total % has to add up to 100% with none left over. So when I sum up the percentages against the departments it should equal 100%.
Example
total money spent
1966
Department 1 has spent 850 ? xxx% of 1966
Department 2 has spent 950
D. 3 has spent 50
D. 4 has spent 64
D. 5 has spent 52
the percentage ought to value to 100%
Cheers
Hello Michael.
This is what you wrote:
It looks to me like it would “department spent/total spent”. That is
$410/$1200, $250/$1200, $315/$1200, $90/$1200, $45/$1200 etc.
And since that only totals 93%, etc pays 7%
Am I missing something here?
My answer. Your answer worked out perfectly in this order.
I used your idea and finally it worked. =total spent/total of all departments (absolute) / 100
I tried to post the sum graphic but unable to.
many thanks.
Thanks - just what I was looking for.