Multiple Variables in a Commission Structure...a tough one!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's my problem, I'm trying to create a worksheet to calculate our
commsiion structure, but can't figure out a way to attack it. We have many
variables (5) in our commission structure based on each order. Here's how I
set it up so far:

(In Cloumns)
A= Order Amount
B= "Y" is A-15%; "N"=A
C= "Y" is B*20%; "N" is B*10%
D= "Y" is B+2%; "N" is B
E= "Y" is B+2%; "N" is B
F="Y" is B+1%; "N" is B
G= SUM(A:F)

For example, if the order is $1000, and I answer y,y,y,y,y=$212.5

How do I create the formulas so I can just put in the order amount and the
appropriate letter to get the correct commission structure?
 
Something seems to be wrong. Can you specify what the numbers are at each
step? Also, if A is 1000 and you sum a - f, how are you coming up with 212.5?
 
check out the nested if's there are many examples I am sure that is the
way you want to go
type "nested if's" in the search bar and you will get lot's of examples
 
I cannot see how you compute $212.50
Bur here is a start, assuming you are finding commission on only one amount.
Use B1:F1 to enter the codes y or n (Excel will not care if you use upper or
lower case)
In A2 enter the order amount
In B2 use something like =IF(B1="Y",A2*(100%-15%),A2) (looks like $850
already!)
In C1 =IF(C1="Y",B2*20%,B2*10%)
etc
I you clarify the method, I will refine the answer
 
Sorry, I wasn't very clear,

If the order is $1000,
and I have to subtract 15% of the gross= $850 (Net).
multiply that by 20% =$170.
add 2% of net 1st bonus=$17
add 2% of net 2nd bonus=$17
add 1% of net 3rd bonus=$8.5

170+17+17+8.5=212.5

How do I create the formula to where I can insert the order amount, and
answer "Y" or "N" to the variables and get the final commission total?
 
Update based on your clarification:
Use B1:F1 to enter the codes y or n (Excel will not care if you use upper or
lower case)
In A2 enter the order amount
In B2 use something like =IF(B1="Y",A2*(100%-15%),A2)
In C1 =IF(C1="Y",B2*20%,B2*10%)
In D2 =IF(D1="Y",B2*2%,0)
In E2 =IF(E1="Y",B2*2%,0)
In F2 =IF(F1="Y",B2*1%,0)
In G2 =SUM(C2:F2)

If you have a column of A's starting in A5:
Use column H for code, so in example enter yyyyy or YYYYY
A5 1000
B5 =IF(MID($H5,1,1)="Y",A5*(100%-15%),A5)
C5 =IF(MID($H5,2,1)="Y",$B5*20%,$B5*10%)
D5 =IF(MID($H5,3,1)="Y",$B5*2%,0)
E5 =IF(MID($H5,4,1)="Y",$B5*2%,0)
F5 =IF(MID($H5,3,1)="Y",$B5*1%,0)
G5 =SUM(C5:F5)
copy B5:F5 down and fill in values for A and H
 
Ok, then the below seems to do the job. However, I would very closely check
the "Else" values because although my formulas appear to reflect what you
originally stated if the Ys were Ns, I doubt if what I used was really what
you intended. For instance, if I change all Y to N then the formula comes up
with 3100

Here are the formulas:

IF(B123="Y",A123-(A123*0.15),A123)
IF(C123="Y",B124*0.2,B124*0.1)
IF(D123="Y",B124*0.02,B124)
IF(E123="Y",B124*0.02,B124)
IF(F123="Y",B124*0.01,B124)
SUM(C124:F124)
 
Looking at how Bernard answered the question, and when I look again at
original requirements, I would change formulas as follows (changing reference
to b to 0)

IF(B123="Y",A123-(A123*0.15),A123)
 
Back
Top