how do I format a fraction as a ratio in excel

How do I format a fraction as a ratio in excel?
cocoma123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
No VBA code is required for this.  Native Excel functions will do.

For example, if the notional numerator is in A2 and the denominator in B2, then use a formula such as:

=TRIM(SUBSTITUTE(TEXT(A2/B2,"???/???"),"/",":"))

The ???/??? indicates that the reduced numerator and denominator can have up to three digits each.  Use ??/?? or ?/? as desired if you wish.  Please do note that for, say 30 and 1, even a ?/? format string will show a result of 30:1.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmrodrigCommented:
You need to use an auxiliary function GCD (Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder)

Function GCD(numerator As Integer, denominator As Integer)
  If denominator = 0 Then
    GCD = numerator
  Else
    GCD = GCD(denominator, numerator Mod denominator)
  End If
End Function

And use it in your sheet like this:
             ColumnA   ColumnB   ColumnC (Ratio)
1           24               4                   =A1/GCD(A1; B1) & ":" & B1/GCD(A1; B1)
2           72               8                   =A2/GCD(A2; B2) & ":" & B2/GCD(A2; B2)
3             6               2                   =A3/GCD(A3; B3) & ":" & B3/GCD(A3; B3)

Will result like this:
ColumnA    ColumnB         Ratio
24                4                         6:1
72                8                         9:1
  6                2                         3:1

Hope it helps !
0
cocoma123Author Commented:
THANK YOU SO MUCH
0
Patrick MatthewsCommented:
No, you do NOT need to use any VBA at all for this.  Consider the following cases:

Item  Num  Den  Fraction   Ratio3   Ratio2   Ratio1      GCD
   1   89  257    89/257   89:257     9:26      1:3   89:257
   2  407  471   407/471  407:471    70:81      6:7  407:471
   3  483  510   161/170  161:170    18:19      1:1  161:170
   4  645  614   645/614  645:614   104:99      1:1  645:614
   5  573  398   573/398  573:398    36:25     13:9  573:398
   6  824  606   412/303  412:303    34:25      4:3  412:303
   7  920  627   920/627  920:627   135:92      3:2  920:627
   8  486  541   486/541  486:541    53:59      8:9  486:541
   9  184  681   184/681  184:681    10:37      1:4  184:681
  10  421  116   421/116  421:116    98:27     29:8  421:116
  11   49   26     49/26    49:26    49:26     17:9    49:26
  12   27   53     27/53    27:53    27:53      1:2    27:53
  13   96    4      24/1     24:1     24:1     24:1     24:1
  14   85   27     85/27    85:27    85:27     22:7    85:27
  15   84   44     21/11    21:11    21:11      2:1    21:11
  16   76   65     76/65    76:65    76:65      7:6    76:65
  17   42   39     14/13    14:13    14:13      1:1    14:13
  18   94   37     94/37    94:37    94:37      5:2    94:37
  19   22   61     22/61    22:61    22:61      1:3    22:61
  20   56   48       7/6      7:6      7:6      7:6      7:6
  21    4    6       2/3      2:3      2:3      2:3      2:3
  22    5    5       1/1      1:1      1:1      1:1      1:1
  23    8    5       8/5      8:5      8:5      8:5      8:5
  24    8    4       2/1      2:1      2:1      2:1      2:1
  25    7    1       7/1      7:1      7:1      7:1      7:1
  26   10    8       5/4      5:4      5:4      5:4      5:4
  27    6    5       6/5      6:5      6:5      6:5      6:5
  28   20    2      10/1     10:1     10:1     10:1     10:1
  29   18    6       3/1      3:1      3:1      3:1      3:1
  30   15    8      15/8     15:8     15:8     15:8     15:8

Open in new window


The formulae:

Fraction:  =A2/B2, with custom Number Format of ???/???
Ratio3:  =TRIM(SUBSTITUTE(TEXT(A2/B2,"???/???"),"/",":"))
Ratio2:  =TRIM(SUBSTITUTE(TEXT(A2/B2,"??/??"),"/",":"))
Ratio1:  =TRIM(SUBSTITUTE(TEXT(A2/B2,"?/?"),"/",":"))
GCD:  =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)

Note how my Ratio3 formula gives the same result as GCD when we use 3-digit numbers, the Ratio2 formula gives the same result when we use 2-digit numbers, and Ratio1 gives the same result as GCD when the denominator is a 1-digit number.

Indeed, depending on how accurate the ratio has to be, the various formulae I provide above may be more convenient.  Consider a few of the cases from above:

Item  Num  Den  Fraction   Ratio3   Ratio2   Ratio1      GCD
   6  824  606   412/303  412:303    34:25      4:3  412:303
   7  920  627   920/627  920:627   135:92      3:2  920:627
  12   27   53     27/53    27:53    27:53      1:2    27:53
  16   76   65     76/65    76:65    76:65      7:6    76:65

Open in new window


In #6, if what we really need is a quick estimate of the ratio, I would argue that the Ratio1 result of 4:3 is far more helpful than the GCD result of 412:303.  Likewise, the Ratio1 result of 3:2 for #7 is probably a lot more useful than the GCD result of 920:627.

Your mileage may vary, of course, but in my opinion using VBA for this is not an improvement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.