Ratio's in Excel

[NeoGen 10]

Hi all,

 

I'm trying to set a column up that shows the ratios of 2 cells, e.g.

 

A B C

 

10 5 2:1

5 15 1:5

 

 

I can get it to do the first one by using this method:

 

=ROUND(A1/B1,0)&":"&B1/B1

 

But I can't figure out how to get it if the number in the B column is greater to show the ratio as 1:2;

 

Is there a way to do this or not?

 

Many thanks

 

N.

[altus 540]

Hi all,

I can get it to do the first one by using this method:

 

=ROUND(A1/B1,0)&":"&B1/B1

 

Your formulae won't even work for A1 is greater than B1 - what happens if the values are 15 and 10?

 

You need to use the find the greatest common divisor and divide both values by that. In LibreOffice it's:

 

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

 

If it's not the same in Excel it will be something similar - look up the greatest common divisor function.

[jagsthecat 10]

Try this, most likely a long way around of doing it but still.

 

=IF(A1<B1,ROUND((A1/A1),2)&":"&ROUND((B1/A1),2),IF(B1<A1,ROUND((A1/B1),2)&":"&ROUND((B1/B1),2),"1:1"))

[NeoGen 10]

Your formulae won't even work for A1 is greater than B1 - what happens if the values are 15 and 10?

 

You need to use the find the greatest common divisor and divide both values by that. In LibreOffice it's:

 

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

 

If it's not the same in Excel it will be something similar - look up the greatest common divisor function.

 

Yes, this was the problem I was having altus, it's why I needed to find a better solution.

 

---------- Post added 16-08-2015 at 13:19 ----------

 

Try this, most likely a long way around of doing it but still.

 

=IF(A1<B1,ROUND((A1/A1),2)&":"&ROUND((B1/A1),2),IF(B1<A1,ROUND((A1/B1),2)&":"&ROUND((B1/B1),2),"1:1"))

 

This done the trick, there is still a minor problem with it if I am dealing with 0, it then gives the #Div/0! error, but for the purposes I need I can deal with this unless you know a fix for this to shoe e.g. 0:1 etc.,

 

But great and many thanks

 

Regards

 

Nick

[The Mush 10]

It's not particularly elegant (or correct) but you could just enclose the formula above in an IFERROR formula? So the end result would look a little like this:

 

=IFERROR(IF(A1<B1,ROUND((A1/A1),2)&":"&ROUND((B1/A1),2),IF(B1<A1,ROUND((A1/B1),2)&":"&ROUND((B1/B1),2),"1:1")),A1&":"&B1)

 

This means if any 0 is entered (in either cell) you will get the two cells stuck together (0:1, 0:2, 10:0 etc. etc.)

 

Depends if this is how you want the cell to deal with a zero value.

 

Cheers

[NeoGen 10]

It's not particularly elegant (or correct) but you could just enclose the formula above in an IFERROR formula? So the end result would look a little like this:

 

=IFERROR(IF(A1<B1,ROUND((A1/A1),2)&":"&ROUND((B1/A1),2),IF(B1<A1,ROUND((A1/B1),2)&":"&ROUND((B1/B1),2),"1:1")),A1&":"&B1)

 

This means if any 0 is entered (in either cell) you will get the two cells stuck together (0:1, 0:2, 10:0 etc. etc.)

 

Depends if this is how you want the cell to deal with a zero value.

 

Cheers

 

Thanks The Mush,

 

This is how I want it to deal if there is a 0 however the above formula just gives me : and no numbers at all, would it be this part ,A1&":"&B1?

 

Many thanks for your help

 

 

 

Nick

[RabM 16]

Mrexcel is my usual site for excel quedtions: e.g.

http://www.mrexcel.com/forum/excel-questions/535450-ratios-div-0-errors.html

[The Mush 10]

Thanks The Mush,

 

This is how I want it to deal if there is a 0 however the above formula just gives me : and no numbers at all, would it be this part ,A1&":"&B1?

 

Many thanks for your help

 

 

 

Nick

 

Hi Nick

 

That's really weird - I've tried it on my work computer (Excel 2013) and my home one (Excel 2010) and it works fine in both. I can't seem to recreate the results you're getting. Without seeing the sheet its hard to work out why its not working.....?!

[NeoGen 10]

Hi Nick

 

That's really weird - I've tried it on my work computer (Excel 2013) and my home one (Excel 2010) and it works fine in both. I can't seem to recreate the results you're getting. Without seeing the sheet its hard to work out why its not working.....?!

 

Hi The Mush,

 

My fault and it was the A1&":"&B1 part as this is what it said in all my formulas, once I'd changed it to the correct cell reference it worked.

 

So many thanks for that it is exactly what I want now.

 

Regards

 

 

 

Nick

[The Mush 10]

Cool. Glad it worked