Bonjour tout le monde,
Je souhaiterai supprimer la macro ci-dessous (voir ci-joint également) et le remplacer par une Formule excel.
Vous trouverez ci-joint un fichier excel avec les différents éléments de la macro et le résultat affiché à la colonne "R".
Je vous remercie vivement de votre aide.
Cordialement,
Laurent
Function ExcessMileage(PenaltyType As String, AvgBurnrate As Double, StdBurnrate As Double, HoldingPeriod As Double, Penalty As Double, MileageLimit As Double, Penalty2 As Double, MileageLimit2 As Double, Penalty3 As Double, MileageLimit3 As Double) As Double
Dim LimitBurnrate As Double
Dim LimitBurnrate2 As Double
Dim LimitBurnrate3 As Double
Dim FreeFleet As Double
Dim FreeFleet2 As Double
Dim FreeFleet3 As Double
Dim AvgMileageNonFreeFleet As Double
Dim AvgMileageNonFreeFleet2 As Double
Dim AvgMileageNonFreeFleet3 As Double
Dim Pi As Double
Pi = WorksheetFunction.Pi
If HoldingPeriod = 0 Then
ExcessMileage = 0
Else
If PenaltyType = "Fixed amount" Then
ExcessMileage = Penalty
Exit Function
End If
If PenaltyType = "Total Mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / HoldingPeriod
LimitBurnrate2 = MileageLimit2 / HoldingPeriod
LimitBurnrate3 = MileageLimit3 / HoldingPeriod
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Or FreeFleet2 = 1 Or FreeFleet3 = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate
End If
If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
ExcessMileage = 0
Exit Function
End If
If PenaltyType = "Monthly mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / 30.5
LimitBurnrate2 = MileageLimit2 / 30.5
LimitBurnrate3 = MileageLimit3 / 30.5
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate
End If
If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
ExcessMileage = 0
Exit Function
Exit Function
End If
End If
End Function
Je souhaiterai supprimer la macro ci-dessous (voir ci-joint également) et le remplacer par une Formule excel.
Vous trouverez ci-joint un fichier excel avec les différents éléments de la macro et le résultat affiché à la colonne "R".
Je vous remercie vivement de votre aide.
Cordialement,
Laurent
Function ExcessMileage(PenaltyType As String, AvgBurnrate As Double, StdBurnrate As Double, HoldingPeriod As Double, Penalty As Double, MileageLimit As Double, Penalty2 As Double, MileageLimit2 As Double, Penalty3 As Double, MileageLimit3 As Double) As Double
Dim LimitBurnrate As Double
Dim LimitBurnrate2 As Double
Dim LimitBurnrate3 As Double
Dim FreeFleet As Double
Dim FreeFleet2 As Double
Dim FreeFleet3 As Double
Dim AvgMileageNonFreeFleet As Double
Dim AvgMileageNonFreeFleet2 As Double
Dim AvgMileageNonFreeFleet3 As Double
Dim Pi As Double
Pi = WorksheetFunction.Pi
If HoldingPeriod = 0 Then
ExcessMileage = 0
Else
If PenaltyType = "Fixed amount" Then
ExcessMileage = Penalty
Exit Function
End If
If PenaltyType = "Total Mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / HoldingPeriod
LimitBurnrate2 = MileageLimit2 / HoldingPeriod
LimitBurnrate3 = MileageLimit3 / HoldingPeriod
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Or FreeFleet2 = 1 Or FreeFleet3 = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate
End If
If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
ExcessMileage = 0
Exit Function
End If
If PenaltyType = "Monthly mileage limit + km penalty" Then
LimitBurnrate = MileageLimit / 30.5
LimitBurnrate2 = MileageLimit2 / 30.5
LimitBurnrate3 = MileageLimit3 / 30.5
FreeFleet = WorksheetFunction.NormDist(LimitBurnrate, AvgBurnrate, StdBurnrate, True)
FreeFleet2 = WorksheetFunction.NormDist(LimitBurnrate2, AvgBurnrate, StdBurnrate, True)
FreeFleet3 = WorksheetFunction.NormDist(LimitBurnrate3, AvgBurnrate, StdBurnrate, True)
If FreeFleet = 1 Then
ExcessMileage = 0
Exit Function
Else
AvgMileageNonFreeFleet3 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate3 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet3) + AvgBurnrate
AvgMileageNonFreeFleet2 = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate2 - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet2) + AvgBurnrate
AvgMileageNonFreeFleet = (StdBurnrate / Sqr(2 * Pi)) * Exp(-((LimitBurnrate - AvgBurnrate) ^ 2) / (2 * StdBurnrate ^ 2)) / (1 - FreeFleet) + AvgBurnrate
End If
If Penalty3 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet3 - LimitBurnrate3) * HoldingPeriod * (Penalty3 - Penalty2) * (1 - FreeFleet3) + (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty2 > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet2 - LimitBurnrate2) * HoldingPeriod * (Penalty2 - Penalty) * (1 - FreeFleet2) + (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
If Penalty > 0 Then
ExcessMileage = (AvgMileageNonFreeFleet - LimitBurnrate) * HoldingPeriod * (Penalty) * (1 - FreeFleet)
Exit Function
End If
ExcessMileage = 0
Exit Function
Exit Function
End If
End If
End Function