Macro to Calculate Efficiency of Spend

This macro was used in a spreadsheet to calculate the efficiency of marketing spend across many different channels. You can add easily adapt this macro to work for your own particular calculations.
' EfficiencyOfSpend Macro
' Pending the type of metric (e.g., CPI, visits), this function will calculate whether or not you are on or off target.
'
' Keyboard Shortcut: Ctrl+Shift+I
'

 
If KpiType = "CPI" Then
 
    'If Target = 0 Then EfficiencyOfSpend = 0

    If Target > ActualValue Then
    EfficiencyOfSpend = (Target - ActualValue) / Target
 
 
    ElseIf Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
    EfficiencyOfSpend = -1 * EfficiencyOfSpend
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
 
    End If
End If
 
If KpiType = "Visits" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Impressions" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Clicks" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
 
 
End Function
Posted in VB