I am trying to nest 3 conditions for a commission pay structu $0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns $50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%). But if
they earn $10,000, the commission is ($10K * 25%) and all the other levels
need to have a zero (instead of FALSE as is currently occurring). Any help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%). But
if
they earn $10,000, the commission is ($10K * 25%) and all the other levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%). But
if
they earn $10,000, the commission is ($10K * 25%) and all the other levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.


On Tue, 19 Apr 2005 21:05:02 -0700, "Dixie"
wrote:

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%). But
if
they earn $10,000, the commission is ($10K * 25%) and all the other
levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

Disregard the formula below. It isn't correct.

On Wed, 20 Apr 2005 17:02:31 -0500, Myrna Larson
wrote:

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.


On Tue, 19 Apr 2005 21:05:02 -0700, "Dixie"
wrote:

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K
at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%).
But
if
they earn $10,000, the commission is ($10K * 25%) and all the other
levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

Try this formula instead:

=IF($A3<B$1,0,MIN(C$1-B$1,$A3-B$1)*B$2)

You need to modify the layout I described earlier: in $G1 put some impossibly
high number, say $100,000,000 or greater.


On Wed, 20 Apr 2005 17:31:58 -0500, Myrna Larson
wrote:

Disregard the formula below. It isn't correct.

On Wed, 20 Apr 2005 17:02:31 -0500, Myrna Larson
wrote:

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put
the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.


On Tue, 19 Apr 2005 21:05:02 -0700, "Dixie"

wrote:

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K
at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%).
But
if
they earn $10,000, the commission is ($10K * 25%) and all the other
levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

EXCELLENT solution, Myrna. It worked great. Not sure why $G1 needs such a
high number, but the formula works so I am VERY happy. Thanks for the help.

"Myrna Larson" wrote:

Try this formula instead:

=IF($A3<B$1,0,MIN(C$1-B$1,$A3-B$1)*B$2)

You need to modify the layout I described earlier: in $G1 put some impossibly
high number, say $100,000,000 or greater.


On Wed, 20 Apr 2005 17:31:58 -0500, Myrna Larson
wrote:

Disregard the formula below. It isn't correct.

On Wed, 20 Apr 2005 17:02:31 -0500, Myrna Larson
wrote:

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put
the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.


On Tue, 19 Apr 2005 21:05:02 -0700, "Dixie"

wrote:

Excellent solution to find the commission with the various levels grouped
together. However, I need to see each commission broken out at each level.
For example, if the sales is $60K, I need the breakdown for commission for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over $80K
at
45%. Are you able to "ungroup or un-nest" the solution so that they apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%).
But
if
they earn $10,000, the commission is ($10K * 25%) and all the other
levels
need to have a zero (instead of FALSE as is currently occurring). Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie

G$1 just has to be higher than any possible value in column A. You could in
fact put a formula in G$1: =MAX(A:A)

The last formula, in F3, is

=IF($A3<F$1,0,MIN(G$1-F$1,$A3-F$1)*F$2)

You want to ensure that the number in G1 high enough that $A3 will always be
smaller than G$1 so the commission for the last bracket is calculated on the
difference between the actual sales (in $A3) and $80,000.

If you were to put, say, $100,000 in G$1, and the sales were $125,000, you
would calculate 45% of G$1-F$1 ($20,000), instead 45% of of $A3-F$1 ($45,000).

On Wed, 20 Apr 2005 20:52:02 -0700, "Dixie"
wrote:

EXCELLENT solution, Myrna. It worked great. Not sure why $G1 needs such a
high number, but the formula works so I am VERY happy. Thanks for the help.

"Myrna Larson" wrote:

Try this formula instead:

=IF($A3<B$1,0,MIN(C$1-B$1,$A3-B$1)*B$2)

You need to modify the layout I described earlier: in $G1 put some
impossibly
high number, say $100,000,000 or greater.


On Wed, 20 Apr 2005 17:31:58 -0500, Myrna Larson
wrote:

Disregard the formula below. It isn't correct.

On Wed, 20 Apr 2005 17:02:31 -0500, Myrna Larson
wrote:

Do you mean that you want 5 columns with the commission for each of the 5
brackets?

If so, you could do something like this. With the amount in column A, put
the
numbers 0, 15000, 30000, 50000, and 80000 in B1:F1. In B2:F2 put the
corresponding percentages, 25%, 30%, 35%, 40%, and 45%

Then put the sales in A3. In B3 put this formula:

=MAX(($A3-B$1)*B$2,0)

and copy it to the right through F3. Then B3:F3 down as far as you need.
Adjust the formulas to suit your layout.


On Tue, 19 Apr 2005 21:05:02 -0700, "Dixie"

wrote:

Excellent solution to find the commission with the various levels
grouped
together. However, I need to see each commission broken out at each
level.
For example, if the sales is $60K, I need the breakdown for commission
for
the first $15K at 25%, then commission for $15k-$30K at 30%, commission
for
$30K-$50K at 35%, commission for $50K-$80K at 40%, then portion over
$80K
at
45%. Are you able to "ungroup or un-nest" the solution so that they
apply
only per commission band?

"Peo Sjoblom" wrote:

Take a look here


http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom


"Dixie" wrote in message
...
I am trying to nest 3 conditions for a commission pay structu
$0-$15,000
= 25%; $15,000-$30,000 = 30%; $30,000-$50,000 = 35%. D6 is the
total
earnings upon which the commission is based. So, for the $15k-$30K
possibility I have:
=if(D6<15000,"0",if(D630000,(15000*.30)),(d6-15000)*.30)
Excel's error says there are too many arguments. If an employee
earns
$50K,
they would receive (15,000 * 25%) + (15,000 * 30%) + (20,000 * 35%).
But
if
they earn $10,000, the commission is ($10K * 25%) and all the other
levels
need to have a zero (instead of FALSE as is currently occurring).
Any
help
on nesting IF functions would be greatly appreciated.
thanks, Dixie