NeoGen   10 #1 Posted August 10, 2015 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. Share this post Link to post Share on other sites Share this content via...
altus   540 #2 Posted August 10, 2015 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. Share this post Link to post Share on other sites Share this content via...
jagsthecat   10 #3 Posted August 10, 2015 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")) Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #4 Posted August 16, 2015 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 Share this post Link to post Share on other sites Share this content via...
The Mush   10 #5 Posted August 17, 2015 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 Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #6 Posted August 18, 2015 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 Share this post Link to post Share on other sites Share this content via...
RabM Â Â 16 #7 Posted August 18, 2015 Mrexcel is my usual site for excel quedtions: e.g. http://www.mrexcel.com/forum/excel-questions/535450-ratios-div-0-errors.html Share this post Link to post Share on other sites Share this content via...
The Mush   10 #8 Posted August 19, 2015 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.....?! Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #9 Posted August 19, 2015 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 Share this post Link to post Share on other sites Share this content via...
The Mush   10 #10 Posted August 19, 2015 Cool. Glad it worked Share this post Link to post Share on other sites Share this content via...