Ratios

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Hi,

I am looking how to show a ratio. For instance, if I have 100 apples and 20
of them are rotten then the ratio is 5:1, i.e. 5 good apples to every bad
apple. If I have 100 and 19 are bad the ratio is 5.26:1 or something like
that? I'm sure there would be a formula to get the ratio to show in a cell
automatically but I cannot find it. I need one decimal point. It is a while
since I have used excel to make formulas etc. Thanks for any help. Mel
 
With the total in A1 and the defects in A2 try:

=ROUND(A1/A2,2)&":1"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Since the first part of the ratio is the actual division, 100/19, all
you need is a way to tack on a ":1"
Like so?

=TEXT(100/19,"0.0")&":1"

Beege
 
Hi Mel,

You may want to rethink your mathematics.

If you have 100 apples and 20 of them are rotten, then you have
80 good apples and 20 bad ones, a ratio of 4:1, not 5:1.

With this in mind, and with
A1=100
B1=20
Then in C1 put this
=ROUND((A1-B1)/B1,2)&":1"

HTH
Martin
 
Re-reading your post that formula should be
=ROUND((A1-B1)/B1,1)&":1"

or if you require it to show 4:1 as 4.0:1 use this
=FIXED((A1-B1)/B1,1)&":1"

HTH
Martin
 
Thanks everyone for the suggestions, will try them all to get the required.
Martin thank you but I am doing this for a friend and I think what they want
is the ration of say bad apples to all apples, not to the good apples.

So I think that is then if 20 rotten apples to every 100 as a percentage
would be 20% or as a ratio 1:5. or 5:1 for 100 : 20. I think this is
correct. If it is rotten to good apples then yes 4:1 I think.??
 
Me again,

I can see that I have not been very clear in the first place.

I need the ratio of the rotten apples to all the apples, but reduced.

x : 1 where x is the total apples.

e.g. if I have five apples and one is rotten then 5.0 : 1

Thanks
Mel
 
OK, so what you are looking for is this
A1 100
B1 20
C1 =FIXED(A1/B1,1)&":1"

You may want to add some spaces for visual reasons, so maybe
=FIXED(A1/B1,1)&" : 1"

HTH
Martin
 
When doing maths on a text value that can be interprited as a number, XL
will convert the text number into a real number - if it can.

0:1 can be interprited as zero hours and 10 minutes. Times are just
numbers and 0:10 equates to 0.000694444444444444 in General format.

0.1:1 however cannot be interprited as a time so remains as text and you
cannot do maths on text so you get a #VALUE! error

1:1 is seen by XL as 1:10 or 1 hour and 10 minutes so again is a real
number.

Does that make sense?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ahhh! my mind is feeling like its just been put in a blender. I have not
worked with formulas for some time and definitely very scratchy.

I have struck the situation where the Zero does cause problems.

What I have to do is this. For each day of the week, people in a group are
handed out calls to make to previous customers, total number of phone calls
made by individuals, I am also given the total of sales then converted from
these phone calls.

E.g.: Monday Bill is given 65 people to call to see if they wish to buy
product, at the end of his shift he hands me the amount of people who are
buying the product he is selling, therefore successful sales in the total
65, so lets say he has 25 successful sales. Wendy is given 45 people to call
and say 13 people buy the product, etc

Each person has there own sheet in excel.

I need a formula that will give the result as a ratio to 1 sale. e.g. for
Bill it is 65/25 2.6 : 1. For Wendy it is 45/13 so 3.5 (rounded up) : 1.

If however Wendy got no sales from her 45 then it is 45 : 0..so no sales in
the 45

These then also sum across for the week and give the weekly ratios

I also tried the following =IF(B7=0,"Nil",(B5/B7)& " :1 ") but for something
like 45 possible and 7 products sold the ratio is 6.428571428 etc and I need
only to one decimal space

The columns are A explanation, B5 amount of possible successes and B7 the
actual sales.

If there are no sales and the word nil comes up then it gives an error in
"Value" with the =FIXED(D25/D27,1)&": 1" the error is one in regards to Div
by 0.

Help appreciated.

Thanks
Mel
 
Hi Mel,

See if this spreadsheet will give you something to work with.
http://www.savefile.com/files/1835164

The pale green cells are input cells and the ratio cells containing
the formulas are display only.

For the weekly ratio, the trick is to do your mathematics on the
input cells and not on the display cells.

HTH
Martin
 
You may also want to change the ratio formula to this.
=IF(A3=0,"",IF(B3=0,A3&".0"&" : 0",FIXED(A3/B3,1)&" : 1"))

It adds the .0 when the ratio is to 0

HTH
Martin
 
Thank you so much for all your help. I tried and tried but kept getting
either invalid in logic or other problems. Putting the ) or ( in the wrong
place. Hope my grey matter comes back :-)
 
You're welcome Mel, I take it that means we finally got
what you were looking for.

Cheers
Martin
 
Back
Top