Halloween present -a little vba function for use in Excel automation to calculate varying WOM values:
Code: Select all
Public Function CalculateWOM(back1 As Single, back2 As Single, back3 As Single, _
lay1 As Single, lay2 As Single, lay3 As Single, _
Optional primaryWeightFactor As Single) As Single
Dim totalPrices As Single, backPriceTotal As Single, layPriceTotal As Single
Dim weightFactor_1 As Single, weightFactor_2 As Single, weightFactor_3 As Single
Const ARBITARY_FACTOR_DIVVY = 0.675
If Not IsMissing(primaryWeightFactor) Then
weightFactor_1 = primaryWeightFactor
weightFactor_2 = (1 - primaryWeightFactor) * ARBITARY_FACTOR_DIVVY ' arbitary %AGE of remaining weight
weightFactor_3 = 1 - (weightFactor_1 + weightFactor_2)
Else
' defaults as per BA
weightFactor_1 = 0.34
weightFactor_2 = 0.33
weightFactor_3 = 0.33
End If
' exit if blank cells or no value on main back/lay
If back1 = 0 Or lay1 = 0 Then
CalculateWOM = 0
End If
' get our 3 main moving values
backPriceTotal = (back1 * weightFactor_1) + (back2 * weightFactor_2) + (back3 * weightFactor_3)
layPriceTotal = (lay1 * weightFactor_1) + (lay2 * weightFactor_2) + (lay3 * weightFactor_3)
totalPrices = backPriceTotal + layPriceTotal
' avoid divide by 0 error
If (totalPrices = 0) Then
CalculateWOM = 0
Else
CalculateWOM = backPriceTotal / totalPrices
End If
End Function
usage on sheet (put into one of the columns after AH) - this is referencing first row of runners in cell AI10. repeat as required:
Code: Select all
=CalculateWOM(G10,F10,E10,H10,I10,J10,$I$4)
note, $I$4 is a variable on the sheet, but could also simply be hardcoded as: 0.6 or whatever [=CalculateWOM(G10,F10,E10,H10,I10,J10,0.6)]
it could also simply be ignored, which would action the BA style defaults: [=CalculateWOM(G10,F10,E10,H10,I10,J10)]
enjoy...