Not Sure How To Title This

[rsrasc]

I'm new and would like to know how the following conditions can be expressed with a formula, a macro or a VBA code. If someone would like to communicate personally with me, I would appreciate it.

I posted this information on another web site dedicated to Excel but no one showed any interest. Hopefully, I may get lucky here.

The following information is based on a proposed payout plan.

Each month the advertising executive will get two goals, a digital goal and non-digital goal. These two goals are going to be compared to the actual sales for the month.

Four your convenience I have attached the excel file.

Thank you in advance for your assistance and cooperation.




Cheers!!!
Roberto

[Paul_Hossler]

I couldn't follow the algorithm completely, since it seemed like you were changing terminology

My suggestion would be to use a User Defined function and then just call it with the 8 different inputs

Let the VBA handle the logic


Option Explicit

Function Commission( _
ActualDigital As Double, ActualNDigital As Double, DigitalGoal As Double, NDigitalGoal As Double, _
TeamActualDigital As Double, TeamActualNDigital As Double, TeamDigitalGoal As Double, TeamNDigitalGoal As Double) As Double

Dim bDigital As Boolean, bNDigital As Boolean, bTeamDigital As Boolean, bTeamNDigital As Boolean

bDigital = (ActualDigital >= DigitalGoal)
bNDigital = (ActualNDigital >= NDigitalGoal)
bTeamDigital = (TeamActualDigital >= TeamDigitalGoal)
bTeamNDigital = (TeamActualDigital >= TeamNDigitalGoal)


If (bDigital And bNDigital) Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.05 * (ActualDigital + ActualNDigital)
Else
Commission = 0.03 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If

ElseIf bDigital Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.04 * (ActualDigital - DigitalGoal) + 0.05 * (ActualDigital - DigitalGoal)
Else
Commission = 0.02 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If

ElseIf bNDigital Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.04 * (ActualNDigital - NDigitalGoal) + 0.05 * (ActualNDigital - NDigitalGoal)
Else
Commission = 0.02 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If


End If

End Function


Only some of the steps work, but it might give you some ideas on how to finish

Paul

[rsrasc]

Thank you Paul for your great posting. I wasn't expecting an answer or some guidance. Will work on this, and see what happens.

I re-arranged the columns so I can work it out as soon as I get a chance, but I added a column to check if the "Yes" and "No" conditions are met.

Thanks again for your assistance and cooperation.

Cheers,

Roberto




I couldn't follow the algorithm completely, since it seemed like you were changing terminology

My suggestion would be to use a User Defined function and then just call it with the 8 different inputs

Let the VBA handle the logic


Option Explicit

Function Commission( _
ActualDigital As Double, ActualNDigital As Double, DigitalGoal As Double, NDigitalGoal As Double, _
TeamActualDigital As Double, TeamActualNDigital As Double, TeamDigitalGoal As Double, TeamNDigitalGoal As Double) As Double

Dim bDigital As Boolean, bNDigital As Boolean, bTeamDigital As Boolean, bTeamNDigital As Boolean

bDigital = (ActualDigital >= DigitalGoal)
bNDigital = (ActualNDigital >= NDigitalGoal)
bTeamDigital = (TeamActualDigital >= TeamDigitalGoal)
bTeamNDigital = (TeamActualDigital >= TeamNDigitalGoal)


If (bDigital And bNDigital) Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.05 * (ActualDigital + ActualNDigital)
Else
Commission = 0.03 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If

ElseIf bDigital Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.04 * (ActualDigital - DigitalGoal) + 0.05 * (ActualDigital - DigitalGoal)
Else
Commission = 0.02 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If

ElseIf bNDigital Then
If (bTeamDigital And bTeamNDigital) Then
Commission = 0.04 * (ActualNDigital - NDigitalGoal) + 0.05 * (ActualNDigital - NDigitalGoal)
Else
Commission = 0.02 * (ActualDigital + ActualNDigital) + 0.05 * (ActualDigital + ActualNDigital - DigitalGoal - NDigitalGoal)
End If


End If

End Function


Only some of the steps work, but it might give you some ideas on how to finish

Paul