Jan 19 2022 05:49 AM
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}.
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.
Thanks.
Jan 20 2022 05:28 AM
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
Else
End If
Else
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.