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=500

cell S31=1482

The result = 1482:500 in cell Y31 as a ratio

But 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.

Thanks

Tricia

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.

Derby:

If rounding for a range for plotting or graphing wouldn't ROUNDUP

be more appropriate,

wizard-fred: That's a question for the OP.

P.S. I doubt my simple solution is what the OP is looking for anyway.

The only way I can get a 1:3 ratio,

is to round up the 1482 to 1500A B C 1) 1482 2) 3) 500 1500 3:1A1 is the real number 1482,

A3 is the real number 500

B3 is the formula: =ROUNDUP((A1/1000),1)*1000

C3 is the formula: =B3/GCD(A3:B3)&":"&A3/GCD(A3:B3)

Result is 3:1

Reverse the formula for 1:3

=A3/GCD(A3:B3)&":"&B3/GCD(A3:B3)

MIKE

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.

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!

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

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

Ratiosimply shows how many times the first quantity is contained in the second, simple division works.The actual ratio of 1482 & 500 is 2.964

So 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

Thank you all for your help.

Ask Your Question

Weekly Poll