Things You Do In Your Lunch Break - How to get the Ratio between two numbers in Excel

A while ago I was asked how to get the ratio between two numbers in Excel. One lunchbreak and two VBA functions later, this is what I came up with.

I ended up creating two functions. One returns the highest common denominator for two numbers HighDenom and the second, Ratio, returns a string of the ratio between two numbers (e.g. 5:1).

First off you need to add a couple of functions to your spreadsheet...

1. Open the workbook you're working on

2. Go into VBA (Alt+F11)

3. Insert a new module (Insert > Module)

4. Paste the following 2 functions into your module: -

Public Function HighDenom(intNum1 As Integer, intNum2 AsInteger) As Integer

' This procedure returns the Highest common denominator fortwo numbers.

If intNum1 > intNum2 Then
intMax = intNum1
Else intMax = intNum2
End If

For i = intMax To 1 Step -1
If (intNum1 / i = Int(intNum1 / i) And (intNum2 / i =Int(intNum2 / i))) Then
HighDenom = i
Exit Function
End If
Next

End Function

Public Function Ratio(intNum1 As Integer, intNum2 AsInteger) As String

' This procedure returns the Highest common denominator fortwo numbers.

intHD = HighDenom(intNum1, intNum2)
intDiv1 = intNum1 / intHDintDiv2 = intNum2 / intHD
Ratio = intDiv1 & ":" & intDiv2
End Function

5. Get out of VBA (Press Alt+Q)

6. Use the functions (They will appear in the Paste Function dialogbox (Shift+F3) under the "User Defined" category.