software

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= quot;Yquot; is A-15%; quot;Nquot;=A
C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
D= quot;Yquot; is B 2%; quot;Nquot; is B
E= quot;Yquot; is B 2%; quot;Nquot; is B
F=quot;Yquot; is B 1%; quot;Nquot; 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 Vaughnquot;Oriana Gquot; wrote:

gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; variables (5) in our commission structure based on each order. Here's how I
gt; set it up so far:
gt;
gt; (In Cloumns)
gt; A= Order Amount
gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; G= SUM(A:F)
gt;
gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt;
gt; How do I create the formulas so I can just put in the order amount and the
gt; appropriate letter to get the correct commission structure?
gt;
gt;
gt;

check out the nested if's there are many examples I am sure that is the
way you want to go
type quot;nested if'squot; in the search bar and you will get lot's of examplesI 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=quot;Yquot;,A2*(100%-15%),A2) (looks like $850
already!)
In C1 =IF(C1=quot;Yquot;,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

quot;Oriana Gquot; lt;Oriana gt; wrote in message
...
gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; variables (5) in our commission structure based on each order. Here's how
gt; I
gt; set it up so far:
gt;
gt; (In Cloumns)
gt; A= Order Amount
gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; G= SUM(A:F)
gt;
gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt;
gt; How do I create the formulas so I can just put in the order amount and the
gt; appropriate letter to get the correct commission structure?
gt;
gt;
gt;
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 quot;Yquot; or quot;Nquot; to the variables and get the final commission total?quot;Kevin Vaughnquot; wrote:

gt; Something seems to be wrong. Can you specify what the numbers are at each
gt; step? Also, if A is 1000 and you sum a - f, how are you coming up with 212.5?
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Oriana Gquot; wrote:
gt;
gt; gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; gt; variables (5) in our commission structure based on each order. Here's how I
gt; gt; set it up so far:
gt; gt;
gt; gt; (In Cloumns)
gt; gt; A= Order Amount
gt; gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; gt; G= SUM(A:F)
gt; gt;
gt; gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt; gt;
gt; gt; How do I create the formulas so I can just put in the order amount and the
gt; gt; appropriate letter to get the correct commission structure?
gt; gt;
gt; gt;
gt; gt;

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

quot;Bernard Liengmequot; wrote:

gt; I cannot see how you compute $212.50
gt; Bur here is a start, assuming you are finding commission on only one amount.
gt; Use B1:F1 to enter the codes y or n (Excel will not care if you use upper or
gt; lower case)
gt; In A2 enter the order amount
gt; In B2 use something like =IF(B1=quot;Yquot;,A2*(100%-15%),A2) (looks like $850
gt; already!)
gt; In C1 =IF(C1=quot;Yquot;,B2*20%,B2*10%)
gt; etc
gt; I you clarify the method, I will refine the answer
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Oriana Gquot; lt;Oriana gt; wrote in message
gt; ...
gt; gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; gt; variables (5) in our commission structure based on each order. Here's how
gt; gt; I
gt; gt; set it up so far:
gt; gt;
gt; gt; (In Cloumns)
gt; gt; A= Order Amount
gt; gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; gt; G= SUM(A:F)
gt; gt;
gt; gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt; gt;
gt; gt; How do I create the formulas so I can just put in the order amount and the
gt; gt; appropriate letter to get the correct commission structure?
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

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=quot;Yquot;,A2*(100%-15%),A2)
In C1 =IF(C1=quot;Yquot;,B2*20%,B2*10%)
In D2 =IF(D1=quot;Yquot;,B2*2%,0)
In E2 =IF(E1=quot;Yquot;,B2*2%,0)
In F2 =IF(F1=quot;Yquot;,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)=quot;Yquot;,A5*(100%-15%),A5)
C5 =IF(MID($H5,2,1)=quot;Yquot;,$B5*20%,$B5*10%)
D5 =IF(MID($H5,3,1)=quot;Yquot;,$B5*2%,0)
E5 =IF(MID($H5,4,1)=quot;Yquot;,$B5*2%,0)
F5 =IF(MID($H5,3,1)=quot;Yquot;,$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

quot;Bernard Liengmequot; gt; wrote in message
...
gt;I cannot see how you compute $212.50
gt; Bur here is a start, assuming you are finding commission on only one
gt; amount.
gt; Use B1:F1 to enter the codes y or n (Excel will not care if you use upper
gt; or lower case)
gt; In A2 enter the order amount
gt; In B2 use something like =IF(B1=quot;Yquot;,A2*(100%-15%),A2) (looks like $850
gt; already!)
gt; In C1 =IF(C1=quot;Yquot;,B2*20%,B2*10%)
gt; etc
gt; I you clarify the method, I will refine the answer
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Oriana Gquot; lt;Oriana gt; wrote in message
gt; ...
gt;gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt;gt; commsiion structure, but can't figure out a way to attack it. We have
gt;gt; many
gt;gt; variables (5) in our commission structure based on each order. Here's how
gt;gt; I
gt;gt; set it up so far:
gt;gt;
gt;gt; (In Cloumns)
gt;gt; A= Order Amount
gt;gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt;gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt;gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt;gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt;gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt;gt; G= SUM(A:F)
gt;gt;
gt;gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt;gt;
gt;gt; How do I create the formulas so I can just put in the order amount and
gt;gt; the
gt;gt; appropriate letter to get the correct commission structure?
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Ok, then the below seems to do the job. However, I would very closely check
the quot;Elsequot; 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=quot;Yquot;,A123-(A123*0.15),A123)
IF(C123=quot;Yquot;,B124*0.2,B124*0.1)
IF(D123=quot;Yquot;,B124*0.02,B124)
IF(E123=quot;Yquot;,B124*0.02,B124)
IF(F123=quot;Yquot;,B124*0.01,B124)
SUM(C124:F124)

--
Kevin Vaughnquot;Oriana Gquot; wrote:

gt; Sorry, I wasn't very clear,
gt;
gt; If the order is $1000,
gt; and I have to subtract 15% of the gross= $850 (Net).
gt; multiply that by 20% =$170.
gt; add 2% of net 1st bonus=$17
gt; add 2% of net 2nd bonus=$17
gt; add 1% of net 3rd bonus=$8.5
gt;
gt; 170 17 17 8.5=212.5
gt;
gt; How do I create the formula to where I can insert the order amount, and
gt; answer quot;Yquot; or quot;Nquot; to the variables and get the final commission total?
gt;
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; Something seems to be wrong. Can you specify what the numbers are at each
gt; gt; step? Also, if A is 1000 and you sum a - f, how are you coming up with 212.5?
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Oriana Gquot; wrote:
gt; gt;
gt; gt; gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; gt; gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; gt; gt; variables (5) in our commission structure based on each order. Here's how I
gt; gt; gt; set it up so far:
gt; gt; gt;
gt; gt; gt; (In Cloumns)
gt; gt; gt; A= Order Amount
gt; gt; gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; gt; gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; gt; gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; gt; gt; G= SUM(A:F)
gt; gt; gt;
gt; gt; gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt; gt; gt;
gt; gt; gt; How do I create the formulas so I can just put in the order amount and the
gt; gt; gt; appropriate letter to get the correct commission structure?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

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=quot;Yquot;,A123-(A123*0.15),A123)
gt; IF(C123=quot;Yquot;,B124*0.2,B124*0.1)
gt; IF(D123=quot;Yquot;,B124*0.02,0)
gt; IF(E123=quot;Yquot;,B124*0.02,0)
gt; IF(F123=quot;Yquot;,B124*0.01,0)
gt; SUM(C124:F124)

--
Kevin Vaughnquot;Kevin Vaughnquot; wrote:

gt; Ok, then the below seems to do the job. However, I would very closely check
gt; the quot;Elsequot; values because although my formulas appear to reflect what you
gt; originally stated if the Ys were Ns, I doubt if what I used was really what
gt; you intended. For instance, if I change all Y to N then the formula comes up
gt; with 3100
gt;
gt; Here are the formulas:
gt;
gt; IF(B123=quot;Yquot;,A123-(A123*0.15),A123)
gt; IF(C123=quot;Yquot;,B124*0.2,B124*0.1)
gt; IF(D123=quot;Yquot;,B124*0.02,B124)
gt; IF(E123=quot;Yquot;,B124*0.02,B124)
gt; IF(F123=quot;Yquot;,B124*0.01,B124)
gt; SUM(C124:F124)
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Oriana Gquot; wrote:
gt;
gt; gt; Sorry, I wasn't very clear,
gt; gt;
gt; gt; If the order is $1000,
gt; gt; and I have to subtract 15% of the gross= $850 (Net).
gt; gt; multiply that by 20% =$170.
gt; gt; add 2% of net 1st bonus=$17
gt; gt; add 2% of net 2nd bonus=$17
gt; gt; add 1% of net 3rd bonus=$8.5
gt; gt;
gt; gt; 170 17 17 8.5=212.5
gt; gt;
gt; gt; How do I create the formula to where I can insert the order amount, and
gt; gt; answer quot;Yquot; or quot;Nquot; to the variables and get the final commission total?
gt; gt;
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; Something seems to be wrong. Can you specify what the numbers are at each
gt; gt; gt; step? Also, if A is 1000 and you sum a - f, how are you coming up with 212.5?
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Oriana Gquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt; gt; gt; gt; commsiion structure, but can't figure out a way to attack it. We have many
gt; gt; gt; gt; variables (5) in our commission structure based on each order. Here's how I
gt; gt; gt; gt; set it up so far:
gt; gt; gt; gt;
gt; gt; gt; gt; (In Cloumns)
gt; gt; gt; gt; A= Order Amount
gt; gt; gt; gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt; gt; gt; gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt; gt; gt; gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; gt; gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt; gt; gt; gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt; gt; gt; gt; G= SUM(A:F)
gt; gt; gt; gt;
gt; gt; gt; gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt; gt; gt; gt;
gt; gt; gt; gt; How do I create the formulas so I can just put in the order amount and the
gt; gt; gt; gt; appropriate letter to get the correct commission structure?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;

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

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

quot;Oriana Gquot; gt; wrote in message
...
gt; Ok, I'm dumb...how do I enter the codes for Y and N in b1:f1?
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt;gt; I cannot see how you compute $212.50
gt;gt; Bur here is a start, assuming you are finding commission on only one
gt;gt; amount.
gt;gt; Use B1:F1 to enter the codes y or n (Excel will not care if you use upper
gt;gt; or
gt;gt; lower case)
gt;gt; In A2 enter the order amount
gt;gt; In B2 use something like =IF(B1=quot;Yquot;,A2*(100%-15%),A2) (looks like
gt;gt; $850
gt;gt; already!)
gt;gt; In C1 =IF(C1=quot;Yquot;,B2*20%,B2*10%)
gt;gt; etc
gt;gt; I you clarify the method, I will refine the answer
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt; quot;Oriana Gquot; lt;Oriana gt; wrote in message
gt;gt; ...
gt;gt; gt; Here's my problem, I'm trying to create a worksheet to calculate our
gt;gt; gt; commsiion structure, but can't figure out a way to attack it. We have
gt;gt; gt; many
gt;gt; gt; variables (5) in our commission structure based on each order. Here's
gt;gt; gt; how
gt;gt; gt; I
gt;gt; gt; set it up so far:
gt;gt; gt;
gt;gt; gt; (In Cloumns)
gt;gt; gt; A= Order Amount
gt;gt; gt; B= quot;Yquot; is A-15%; quot;Nquot;=A
gt;gt; gt; C= quot;Yquot; is B*20%; quot;Nquot; is B*10%
gt;gt; gt; D= quot;Yquot; is B 2%; quot;Nquot; is B
gt;gt; gt; E= quot;Yquot; is B 2%; quot;Nquot; is B
gt;gt; gt; F=quot;Yquot; is B 1%; quot;Nquot; is B
gt;gt; gt; G= SUM(A:F)
gt;gt; gt;
gt;gt; gt; For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
gt;gt; gt;
gt;gt; gt; How do I create the formulas so I can just put in the order amount and
gt;gt; gt; the
gt;gt; gt; appropriate letter to get the correct commission structure?
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;