Results 1 to 15 of 20
Thread: Ratio (2003)

20050307, 01:53 #1
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050307, 02:39 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Legare Coleman

20050307, 03:09 #3
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Ratio (2003)
Yes, Legare.. once again you have saved me. Thank you! That's exactly what I was looking for.

20050307, 03:12 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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>John ... I float in liquid gardens
UTC 7ąDS

20050307, 12:08 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
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

20050307, 13:12 #6
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050307, 13:17 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20050307, 13:18 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Ratio (2003)
The Analysis Toolpak addin that comes with Excel contains GCD and LCM worksheet functions.
BTW, Return b is not standard Visual Basic, it should be GCD = b.

20050307, 13:36 #9
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050307, 13:43 #10
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Ratio (2003)
I was thinking of adding it, or a related ratio function, as a UDF (something I know little about).
Would that be possible/ viable, as opposed to loading an addin for just one function?
Alan

20050307, 14:25 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 builtin 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

20050307, 14:31 #12
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Ratio (2003)
Cheers Steve. Didn't think about the relative slowness of UDF/ VBA code.
Alan

20050307, 14:50 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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?John ... I float in liquid gardens
UTC 7ąDS

20050307, 14:59 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20050307, 15:49 #15
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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.
John ... I float in liquid gardens
UTC 7ąDS