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?
--
Kevin Vaughn


"Oriana G" wrote:

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?

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?


"Kevin Vaughn" wrote:

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?
--
Kevin Vaughn


"Oriana G" wrote:

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?

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)

--
Kevin Vaughn


"Oriana G" wrote:

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?


"Kevin Vaughn" wrote:

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?
--
Kevin Vaughn


"Oriana G" wrote:

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?

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)
IF(C123="Y",B124*0.2,B124*0.1)
IF(D123="Y",B124*0.02,0)
IF(E123="Y",B124*0.02,0)
IF(F123="Y",B124*0.01,0)
SUM(C124:F124)

--
Kevin Vaughn


"Kevin Vaughn" wrote:

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)

--
Kevin Vaughn


"Oriana G" wrote:

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?


"Kevin Vaughn" wrote:

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?
--
Kevin Vaughn


"Oriana G" wrote:

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?