Access 2010 VBA How To Calculate the Ratio between two Numbers

Hi Experts,

I have two long integer fields: YouthNbr and AdultNbr.  I need to calculate the ration of Youth To Adults.

e.g. Youth = 8, Adults = 2,  Ratio = 4:1 displayed this way.

I want to do this in an Event using VBA, not using a Query.

How do I do it?  Is there a Function?

Thanks,
Bob C.
Bob CollisonSystem ArchitectAsked:
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.

Ryan ChongCommented:
quick try:
Public Function getRatio(ByVal v1 As Integer, ByVal v2 As Integer) As String
    Dim total As Integer
    Dim divider As Integer
    If v1 > v2 Then
        divider = v2
    Else
        divider = v1
    End If
    
    total = v1 + v2
    getRatio = Int(v1 / divider) & ":" & Int(v2 / divider)
End Function

Open in new window

0
Ryan ChongCommented:
this is the revised version to handle zero:

Public Function getRatio(ByVal v1 As Integer, ByVal v2 As Integer) As String
    Dim divider As Integer
    
    If v1 > v2 Then
        divider = v2
    Else
        divider = v1
    End If
    If divider = 0 Then
        getRatio = ""
    Else
        getRatio = Int(v1 / divider) & ":" & Int(v2 / divider)
    End If
End Function

Open in new window

0
HuaMin ChenProblem resolverCommented:
Try        
    Dim Youth As Integer, Adulss As Integer, Ratio As String
    Youth = 8: adults = 2
    Ratio = Trim(CStr(Youth / adults)) & ":1"
    Debug.Print Ratio

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
@HuaMinChen

This doesn't work well if:

Youth = 2 and adults = 8
0
Rgonzo1971Commented:
Hi,

pls try
Function fRatio(ByVal n1 As Long, ByVal n2 As Long) As String
    If n1 = 0 Or n2 = 0 Then
        getRatio = ""
    Else
        nGCD = fGCD(n1, n2)
        fRatio = n1 / nGCD & ":" & n2 / nGCD
    End If
End Function
Function fGCD(ByVal n1 As Long, ByVal n2 As Long) As Long
    Do While n2 <> 0
        i = n2
        n2 = n1 Mod n2
        n1 = i
    Loop
    fGCD = n1
End Function

Open in new window


@Ryan Chong does not work with 8:3 gives 2:1 wrong

Regards
1
Ryan ChongCommented:
revised to this, should work for 8:3 now.

Public Function getRatio(ByVal v1 As Integer, ByVal v2 As Integer) As String
    Dim divider As Integer
    
    If v1 > v2 Then
        If v1 Mod v2 > 0 Then
            getRatio = v1 & ":" & v2
            Exit Function
        End If
        divider = v2
    Else
        If v2 Mod v1 > 0 Then
            getRatio = v1 & ":" & v2
            Exit Function
        End If
        divider = v1
    End If
    If divider = 0 Then
        getRatio = "Invalid Ratio"
    Else
        getRatio = Int(v1 / divider) & ":" & Int(v2 / divider)
    End If
End Function

Open in new window

0
Ryan ChongCommented:
revised again to this to handle zero:

Public Function getRatio(ByVal v1 As Integer, ByVal v2 As Integer) As String
    Dim divider As Integer
    If v1 = 0 Or v2 = 0 Then
        getRatio = "Invalid Ratio"
        Exit Function
    End If
    
    If v1 > v2 Then
        If v1 Mod v2 > 0 Then
            getRatio = v1 & ":" & v2
            Exit Function
        End If
        divider = v2
    Else
        If v2 Mod v1 > 0 Then
            getRatio = v1 & ":" & v2
            Exit Function
        End If
        divider = v1
    End If
    getRatio = Int(v1 / divider) & ":" & Int(v2 / divider)
End Function

Open in new window

0
Rgonzo1971Commented:
@Ryan Chong but does not handle well 8:6 should be 4:3
0
Gustav BrockCIOCommented:
If you just want an integer ratio to one (n:1 or 1:n), a one-liner will do:

Ratio = IIf(YouthNbr * AdultNbr = 0, "0:0", IIf(YouthNbr > AdultNbr, YouthNbr\AdultNbr & ":1", "1:" & AdultNbr\YouthNbr))

Open in new window

But what if values are 4 and 3? Should the ratio be 4:3 or 1.33:1?

/gustav
0
Ryan ChongCommented:
@Rgonzo1971,

noted for that, apparently my method didn't apply GCD.

i have done another version to handle that case but don't think I wil post it here, it just look similar to what you have posted.
0
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

I have taken a brief look at all of the comments between yourselves.

Gustav, you are correct that a if there are 4 Youth and 3 Leaders then the Ratio would need to be the number of Youth per Leader or as you have stated 1.33 Youth Per 1 Leader.  I would also like it to be rounded down (not up or off) to one decimal. e.g. 1.33 would be 1.3, 1.78 would be 1.7.  How would that be coded?

By the way. The end result of calculating the Ratio will be to compare it to a Specified Value (e.g. 4:1) to determine if the Ratio is equal to or less that the Specified Value.

Thanks all,
Bob C.
0
Gustav BrockCIOCommented:
Then you could use:

Ratio = IIf(YouthNbr * AdultNbr = 0, "0:0", IIf(YouthNbr > AdultNbr, (YouthNbr * 10 \ AdultNbr) / 10 & ":1", "1:" & (AdultNbr * 10 \ YouthNbr) / 10))

Open in new window

That will round down to one decimal maximum.

/gustav
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
HuaMin ChenProblem resolverCommented:
Try        
Sub A_Test()
    Dim Youth As Integer, Adulss As Integer, t1 As Double, Ratio As String
    Youth = 7: adults = 2: t1 = Round(Youth / adults, 2)
    Ratio = Trim(CStr(t1)) & ":1"
    Debug.Print Ratio
    
End Sub

Open in new window

0
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,

Thanks for all your suggestions.  All three appear to do what I need but I prefer Gustav's solution.

Thanks,
Bob C.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
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 Access

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.