Forum Discussion

cameronmarshall's avatar
cameronmarshall
Copper Contributor
Jan 19, 2022

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

excel
Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 20, 2022

    cameronmarshall 

    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.

Resources