Bob Collison

asked on

# 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.

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.

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
Try

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

@HuaMinChen

This doesn't work well if:

Youth = 2 and adults = 8

This doesn't work well if:

Youth = 2 and adults = 8

Hi,

pls try

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

Regards

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
```

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

Regards

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
```

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
```

@Ryan Chong but does not handle well 8:6 should be 4:3

If you just want an integer ratio to one (n:1 or 1:n), a one-liner will do:

/gustav

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

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

@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.

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.

ASKER

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.

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.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Hi Experts,

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

Thanks,

Bob C.

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

Thanks,

Bob C.

You are welcome!

/gustav

/gustav

Open in new window