# Rounding up in excel before running gcd Microsoft Microsoft excel 2000
May 21, 2010 at 12:08:30
Specs: Windows XP
 I have a spreadsheet that depends on two cells and produces a ratio but it only produces an exact ratio but I need it to be a rough ratio. Example:Cell Q31=500cell S31=1482The result = 1482:500 in cell Y31 as a ratioBut I need it to say 3:1 ratio (because it's close to 3 to 1). I tried to use round, mround and roundup so it will round up the numbers prior to producing the ratio with the gcd but I just can't seem to get it right.This is what I used=S31/GCD(Q31,S31)&":"&Q31/GCD(Q31,S31)then I tried to fix the ratio by trying to force the roundup prior to the raio=(RoundUp(s31,0),Roundup(q31,0):S31/GCD(Q31,S31)&":"&Q31/GCD(Q31,S31)I just don't understand the complex functions. ThanksTricia See More: Rounding up in excel before running gcd

#1 May 21, 2010 at 16:45:23
 How rough are you willing to settle for?This will always return a Something to One ratio:=ROUND(S31/Q31,0) & ":1"If that's too rough, give us a few more inputs and the desired outputs and we'll see what we can do.

Report •

#2 May 21, 2010 at 22:16:07
 Derby:If rounding for a range for plotting or graphing wouldn't ROUNDUP be more appropriate,

Report •

#3 May 22, 2010 at 03:41:18
 wizard-fred:That's a question for the OP.P.S. I doubt my simple solution is what the OP is looking for anyway.

Report •

Related Solutions

#4 May 22, 2010 at 13:13:26
 The only way I can get a 1:3 ratio,is to round up the 1482 to 1500``` A B C 1) 1482 2) 3) 500 1500 3:1 ```A1 is the real number 1482,A3 is the real number 500B3 is the formula: =ROUNDUP((A1/1000),1)*1000C3 is the formula: =B3/GCD(A3:B3)&":"&A3/GCD(A3:B3)Result is 3:1Reverse the formula for 1:3=A3/GCD(A3:B3)&":"&B3/GCD(A3:B3)MIKE

Report •

#5
May 24, 2010 at 05:16:15
 Background/reasoning: If cell y31 is less than 3:1 or more then 3:1 (say 3:5) then the result is unfavorable. The chemical in the oil would be suspect if not atleast 3:1 raio.So, that being said the round up is good but how do I fit everything into cell y31. One cell is the only one available to type the roundup and then the ratio function. Is it possible to force the roundup and then the ratio all in one cell? If I type both the roundup and GCD with a colon in between the two functions I just get an error message saying there's a problem. If I type the function into one of the other cells where my number is, then I can not type in the number without deleting the function.

Report •

#6 May 24, 2010 at 07:02:42
 In your OP you included one example of inputs and the desired output (3:1). Thank you for that.Could you please give us a few more example of inputs and outputs, both good and bad, so I can get a better understanding off what you what are looking for?Thanks!

Report •

#7
May 24, 2010 at 07:28:48
 I'm not sure I can explain it in more detail but here is the page example of what I'm creating.Ethylene acetylene Acetylene / Ethylene (cell q31) (cell s31) (Ratio of s31:q31)500 1487 1487:500 (i know this result is 3:1 because I rounded up to 1500 but the cell won't round up so I'm getting 1487:500)The numbers that I am using in S31 & Q31 are a direct result from lab test results so I have no control over them but according to the lab, if the ratio is over 3:1 (meaning acetylene amt over ethylene amt) there is a problem thats possibly some kind of electrical problem. So, S31 & Q31 could be any number combo but the ratio is what we would look at for judging whether there is a problem we need to fix or not. If Ethylene ratio is above acetylene that's a problem too (which would be 1:3).Does this help explain or just confuse?Thanks

Report •

#8 May 24, 2010 at 08:13:13
 I think you would be better served by using DerbyDad03's solution, rather than mine.Using mine, you would need to round up either one or both quantities, but since a Ratio simply shows how many times the first quantity is contained in the second, simple division works.The actual ratio of 1482 & 500 is 2.964So modifying DerbyDad03's formula to:=IF(ROUND(A1/A3,0)=0,"1:"&ROUND(A3/A1,0),ROUND(A1/A3,0)&":1")should get you what you need in a single cell.MIKE

Report •

#9
May 24, 2010 at 08:54:44
 Thank you all for your help.

Report • 