View Full Version : Not Sure How To Title This
rsrasc
04-02-2011, 07:49 AM
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
04-03-2011, 03:42 PM
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
04-04-2011, 03:13 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.