Forum Discussion
cameronmarshall
Jan 19, 2022Copper Contributor
Help using formulas to find corresponding values.
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 = a...
OliverScheurich
Jan 20, 2022Gold Contributor
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.