Write your own worksheet Function

Public Function XCTimePenalties(OptimumTime As Double, ActualTime As Double, UnderAllowance As Double, OverAllowance As Double) As Double

'Calculates Time penalties for an Eventing Cross country event.  The designer calculates the
'optimum time based on the distance and class. Any variation from this time generates penalties
'however the formulae allows for a local variation for margins about the optimum time
'The function has a type of double (floating point).  The value of the function is returned
'by setting the function name to the value required.

Dim TimeDiff As Double
'Exit if there is no actual time
If ActualTime = 0 Then
    Exit Function
End If
If ActualTime <= OptimumTime Then
    TimeDiff = OptimumTime - ActualTime
    If TimeDiff <= UnderAllowance Then
'Function returns Zero penalties
        XCTimePenalties = 0
        Exit Function
    Else
        XCTimePenalties = (TimeDiff - UnderAllowance) * 0.4
        Exit Function
    End If
Else
    TimeDiff = ActualTime - OptimumTime
    If TimeDiff <= OverAllowance Then
        XCTimePenalties = 0
        Exit Function
    End If
    XCTimePenalties = TimeDiff * 0.4
    Exit Function
End If
    

End Function