Help using formulas to find corresponding values.

Occasional Contributor

So I am developing a spreadsheet with football results and betting odds. 


So I have input the scores, then an automated result output where {0 = not yet played, 1 =. home win, 2 = draw, and 3 = away win}.

Screenshot 2022-01-19 at 13.44.22.png


I then have a column to output the odds of the favourite if the favourite won, and 0 if not. 

This is by using:

=IF(G2="","n/a",IF(VALUE(OFFSET(F2,0,VALUE(F2)))=MIN(G2:I2),MIN(G2:I2),0)) etc.


I can then work out say at the bottom of the column the sum of odds to calculate the return given an equal stake on each bet. 


However, is there a quicker way perhaps using VLOOKUP or CHOOSE that I could write the code to sum all the values corresponding to the MINIMUM odds and the correct result, and then negate when the MINIMUM odds does not correspond to the given result. 



1 Reply


Sub FootballOdds()

Dim j As Double
Dim i As Double
Dim sum As Double
Dim result As Double

sum = 0

For i = 2 To 21
For j = 7 To 9

result = Application.WorksheetFunction.Min(Range(Cells(i, 7), Cells(i, 9)))

If Cells(i, j).Value = result Then

If Cells(i, 6).Value = j - 6 Then
sum = sum + Cells(i, j).Value
End If

End If

Next j
Next i

Cells(25, 1).Value = sum

End Sub


Maybe with this macro. However it's not quicker than a simple SUM formula that adds up column J because the SUM formula works dynamically whereas the macro has to be executed to update the result.