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

[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?

 

[Guest]

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?

 

[damorrison]

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

 

[Bernard Liengme]

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

 

[Guest]

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?

 

[Guest]

Ok, I'm dumb...how do I enter the codes for Y and N in b1:f1?

 

[Bernard Liengme]

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

 

[Guest]

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)

 

[Guest]

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)

 

[Bernard Liengme]

type Y in B1, Y in C1 ....