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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Oriana G" <Oriana wrote in message
...
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, I'm dumb...how do I enter the codes for Y and N in b1:f1?

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Oriana G" <Oriana wrote in message
...
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?

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Oriana G" <Oriana wrote in message
...
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?

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Oriana G" <Oriana wrote in message
...
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?