Function calcScore(homeActual, awayActual, homePrediction, awayPrediction, Optional ByVal switch As Boolean)
' to capture the following scoring regime in a custom function
'Exact score 60
'Case 1: Exact score away win 100
'case2: Correct result; One team right other team <= 1 goal off 30
'case 2a : Correct result away win
'case3: Correct result; One team right with other team 2 goals off 25
'case4: Correct result; One team right with other team >2 goals off 15
'case5: Wrong result; One team right 10
'
'case6: Correct result; Both scores individually wrong, but  total is =<1 goal off 20
'case7: Correct result; Both scores individually wrong, but  2 goals off 15
'case8: Correct result; Both scores individually wrong, but   >2 goals off 10
'
'Wrong result; no teams right 0

Dim actHome, actAway, predHome, predAway As Integer
Dim exact, case2, case3, case4, case5, case6, case7, case8 As Integer

'set numbers for the cases to be put into the scores cell
exact = 60
case1 = 100
case2 = 30
case3 = 25
case4 = 15
case5 = 5
case6 = 20
case7 = 15
case8 = 10

actHome = homeActual
actAway = awayActual
predHome = homePrediction
predAway = awayPrediction

'exit function if any of the variables are zero
If IsEmpty(actHome) Or IsEmpty(actAway) Or IsEmpty(predHome) Or IsEmpty(predAway) Then
    'MsgBox ("Missing Actual or Predicted values")
    calcScore = "-"
    Exit Function
End If


' first the case when the score prediction is completely correct
If actHome = predHome And actAway = predAway Then
    If actHome < actAway Then 'exact prediction but away win so worth more
        If switch Then: calcScore = "case1": Else: calcScore = case1
    Else
        If switch Then: calcScore = "exact": Else: calcScore = exact
    End If
Else
    'check if result is correct 3 cases here with a draw also being correct
    If (actHome > actAway) And (predHome > predAway) Or _
       (actHome < actAway) And (predHome < predAway) Or _
       (actHome = actAway) And (predHome = predAway) Then
        'now we have a correct result we need to see which of case2, case3, case4, case6, case7 or case8
        If (actHome = predHome) Or (actAway = predAway) Then
            'one score is correct we need to check how far off the other team is first Home is same
            If (actHome = predHome) Then
                If (actAway + 1 = predAway) Or (actAway - 1 = predAway) Then
                    'this is case 2
                    If switch Then: calcScore = "case2": Else: calcScore = case2
                Else
                    If (actAway + 2 = predAway) Or (actAway - 2 = predAway) Then
                        'this is case 3
                        If switch Then: calcScore = "case3": Else: calcScore = case3
                    Else
                        'this is case 4
                        If switch Then: calcScore = "case4": Else: calcScore = case4
                    End If
                End If
            End If
            'one score is correct we need to check how far off the other team is now Away is same
            If (actAway = predAway) Then
                If (actHome + 1 = predHome) Or (actHome - 1 = predHome) Then
                    'this is case 2
                    If switch Then: calcScore = "case2": Else: calcScore = case2
                Else
                    If (actHome + 2 = predHome) Or (actHome - 2 = predHome) Then
                        'this is case 3
                        If switch Then: calcScore = "case3": Else: calcScore = case3
                    Else
                        'this is case 4
                        If switch Then: calcScore = "case4": Else: calcScore = case4
                    End If
                End If
            End If
        Else
        'so neither home or away scores match need to check case 6, 7 & 8
            If (actHome + actAway + 1 = predHome + predAway) Or _
               (actHome + actAway - 1 = predHome + predAway) Or _
               (actHome + actAway = predHome + predAway) Then
               ' total goals the same or within 1 so case 6
               If switch Then: calcScore = "case6": Else: calcScore = case6
            Else
                If (actHome + actAway + 2 = predHome + predAway) Or _
                    (actHome + actAway - 2 = predHome + predAway) Then
                    'total goals within 2 so case 7
                    If switch Then: calcScore = "case7": Else: calcScore = case7
                Else
                    'must be case 8
                    If switch Then: calcScore = "case8": Else: calcScore = case8
                End If
            End If
        End If
    Else
        ' wrong result so need to decide between case5 and zero score
        If (actHome = predHome) Or (actAway = predAway) Then
            If switch Then: calcScore = "case5": Else: calcScore = case5
        Else
            If switch Then: calcScore = "zero": Else: calcScore = 0
        End If
    End If
End If

End Function