1. ## Ratio (2003)

I want to calculate the ratio between male and females over several months. How can I get Excel to calculate and display the ratio formatted as x:x (ie. 3:2)?
Jan Feb Mar
Male 24 44 55
Female 51 62 79
Ratio: X:X X:X X:X

2. ## Re: Ratio (2003)

It depends on how you define the ratio that you want. With the numbers in your example, 24 males and 51 females, then 24:51 is the lowest you can get since 24 and 51 have no common devisors. 44 males and 62 females could be reduced to 22:31. If you take the common definition of ratio as 1:n, then 24 males and 51 females would give a ratio of 1:2.1. If this is what you want, then the attached worksheet should show you how to calculate and display it.

3. ## Re: Ratio (2003)

Yes, Legare.. once again you have saved me. Thank you! That's exactly what I was looking for.

4. ## Re: Ratio (2003)

I was working on this along the same lines as Legare, but I assumed you want only whole numbers in the ratio. If your numbers contain primes, then it's difficult to simplify them, and if they don't it's difficult to determine the lowest common denominator. This approach lets you set the LCD on the 4th row, I changed the numbers to expand the demonstration, but Legare's warning still applies. Legare's example always sets the numerator as '1', my approach doesn't.

<table border=1><td align=right></td><td>Jan</td><td>Feb</td><td>Mar</td><td>Male</td><td align=right>24</td><td align=right>44</td><td align=right>55</td><td>Female</td><td align=right>51</td><td align=right>62</td><td align=right>89</td><td>Ratio:</td><td align=right>=ROUND(B2/10/B5,0)&":"&ROUND(B3/10/B5,0)</td><td align=right>=ROUND(C2/10/C5,0)&":"&ROUND(C3/10/C5,0)</td><td align=right>=ROUND(D2/10/D5,0)&":"&ROUND(D3/10/D5,0)</td><td>LCD:</td><td align=right>1</td><td align=right>2</td><td align=right>3</td></table>

5. ## Re: Ratio (2003)

John,

What is the LCD row? If intended to be a lowest common denominator, I don't see why 1 is the answer for Jan since both 24 and 51 are divisible by 3. Similarly I don't understand the 3 for March.

Fred

6. ## Re: Ratio (2003)

John

I'm not sure why you say it's difficult to determine a LCD. The (related) greatest common divisor of two ints can be determined by a fairly simple function like this one:
<pre>' Return the greatest common divisor (GCD) of a and b.
Public Function GCD(ByVal a As Long, ByVal b As Long) As Long
Dim tmp As Long
Dim remainder As Long

' Make a >= b.
a = Abs(a)
b = Abs([img]/forums/images/smilies/cool.gif[/img]
If a < b Then
tmp = a
a = b
b = tmp
End If

' Pull out remainders.
Do
remainder = a Mod b
If remainder = 0 Then Return b
a = b
b = remainder
Loop
End Function
</pre>

I'm also unsure of what the LCD values in your table represent. ???

Alan

7. ## Re: Ratio (2003)

It might be easier (with no macor) to add the analysis toolpack addin and use the "Greatest Common Divisor" function.The greatest common divisor is the largest integer that divides all the numbers without a remainder.

=GCD(number1,number2, ...)

Steve

8. ## Re: Ratio (2003)

The Analysis Toolpak add-in that comes with Excel contains GCD and LCM worksheet functions.

BTW, Return b is not standard Visual Basic, it should be GCD = b.

9. ## Re: Ratio (2003)

Noted. I dug this out of my repository for "everything" and didn't check its source.
Now I'm wondering where "Return b" comes from. Maybe .NET?

Alan

10. ## Re: Ratio (2003)

I was thinking of adding it, or a related ratio function, as a UDF (something I know little about).

Alan

11. ## Re: Ratio (2003)

You can add UDFs to that workbook or if used a lot add them to personal.xls. If in personal.xls you must preceed the function name:
=personal.xls!MyFunc()
to tell excel where they are. Functions in Addins do not require this (one of their advantages).

UDFs are much slower than the builtin analysis toolpacks so if you have lots of them in your spreadsheet (thousands) you will notice the UDFs will make it more sluggish. It is almost always faster to use built-in formulas (including the provided toolpacks) than UDFs. Addins created with VB have no other advantages than your own UDF in the workbook or personal.xls.

Steve

12. ## Re: Ratio (2003)

Cheers Steve. Didn't think about the relative slowness of UDF/ VBA code.

Alan

13. ## Re: Ratio (2003)

Last night I couldn't find a built in Excel method to determine the LCD, and I didn't have time to write a custom function. I looked at Excel's LCM function and didn't see how it would help. Look at my formula, replace the 2's and 3's on the 4th row with a 1, you'll see I'm trying to set the divisor to obtain an LCD for the numerator and denominator. Can you show me how you would use either your LCD function or Excel's built in LCM function to turn a 2:4 ratio into 1:2, or a 14:16 ratio into 7:8? Better, can you show me how you would use -any- built in Excel functions to do it?

BTW, there are worse problems with my approach which you should be critical of, for example the device of dividing by 10 will fail to recognize that 35 females and 70 males is exactly a 2:1 ratio. But I was trying to answer the original question as put with the constraints that the OP was using.

Also, I later thoght of using Excel's built in fraction formats, but didn't get to it. Anyone tried that approach?

14. ## Re: Ratio (2003)

If B2 contains 18, and B3 contains 24, the formula
<code>
=B2/GCD(B2:B3)&":"&B3/GCD(B2:B3)
</code>
will return 3:4 as a text string. It requires the Analysis Toolpak to be installed.

15. ## Re: Ratio (2003)

That's it, Hans. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> I wasn't thinking that GCD would give me what I needed, focusing on "lowest" I only looked at LCM.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•