Home/ Questions/Q 8339
Next
Answered
computing
  • 2
Asked: 2021-11-28T10:10:30+00:00 In:

Rounding Up In Excel Before Running Gcd

  • 2

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

Share

    1 Answer

    1. Best Answer
      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 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

      http://www.skeptic.com/

      • 0