Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Ratio (2003)

  1. #1
    5 Star Lounger
    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

  2. #2
    Uranium Lounger
    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

  3. #3
    5 Star Lounger
    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.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 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

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  6. #6
    Platinum Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #9
    Platinum Lounger
    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

  10. #10
    Platinum Lounger
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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 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. #12
    Platinum Lounger
    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

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 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

Page 1 of 2 12 LastLast

Posting Permissions

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