Excel nested IF formula question

[hanesds@hotmail.com]

I have a question regarding Nested If statements in Excel 2003. I have
a commission structure below that needs to be nested
0-$50K = 10%
50-100K = 20%
100-150K = 25%
150-200K = 30%
200K+ - 35%

For example:
Fee commission rate commission
19,125 10% 1,912.50
8,000 10% 800.00
8,000 10% 800.00

8,000 10% 800.00
Total Billings: 43,125

Here is where I am running into a problem. At this point the person has
reached a total billing of $43,125 with the next commission it needs
to be split between the 10% rate and the 20% rate as follows:
Fee commission rate
commission
8,000 10% of the first 6,875 (to get to 50,000)
687.50
20% of the next 1,125 (the dollar over
50,000) 225.00

8,000 20%
1,600
This split will happen at each new level reached and I'm failing to
figure out an easy way to handle these events. Is there an easy way to
add this into the IF statement:
=IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
IF(H3>=50000,D3*20%,D3*10%))))

In the above H3 is the Total Billings and D3 is the fee that the
commission is paid on. In the above it gives the below:
Fee commission rate commission
19,125 10% 1,912.50
8,000 10% 800.00
8,000 10% 800.00

8,000 10% 800.00
8,000 20% 1600.00 this
should be the 6875 @10% and 1,125 @20% shown above for a total of
$912.50
8,000 20% 1600.00

I hope this makes sense. Any simple Excel trick I can add in here to
make this work smoothly would be greatly appreciate!!

Thanks in advance,
D

[Ron de Bruin]

Hi D

See J.E page
http://www.mcgimpsey.com/excel/variablerate.html


--
Regards Ron de Bruin
http://www.rondebruin.nl


<hanesds@hotmail.com> wrote in message news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
>I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125 with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee commission rate
> commission
> 8,000 10% of the first 6,875 (to get to 50,000)
> 687.50
> 20% of the next 1,125 (the dollar over
> 50,000) 225.00
>
> 8,000 20%
> 1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> 8,000 20% 1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000 20% 1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
>

[RagDyer]

I believe John McGimpsey has created a web page for just your situation:

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

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


<hanesds@hotmail.com> wrote in message
news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
> I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125 with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee commission rate
> commission
> 8,000 10% of the first 6,875 (to get to 50,000)
> 687.50
> 20% of the next 1,125 (the dollar over
> 50,000) 225.00
>
> 8,000 20%
> 1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> 8,000 20% 1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000 20% 1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
>

[Bob Phillips]

=D3*10%+MAX(0,D3-50000)*10%+MAX(0,D3-100000)*5%+MAX(0,D3-150000)*5%+MAX(0,D3
-200000)*5%

gives you the commission, but I don't see how this differs from your nested
IF. If you want to calculate the additional commission, just subtract the
commission to date.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<hanesds@hotmail.com> wrote in message
news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
> I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125 with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee commission rate
> commission
> 8,000 10% of the first 6,875 (to get to 50,000)
> 687.50
> 20% of the next 1,125 (the dollar over
> 50,000) 225.00
>
> 8,000 20%
> 1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee commission rate commission
> 19,125 10% 1,912.50
> 8,000 10% 800.00
> 8,000 10% 800.00
>
> 8,000 10% 800.00
> 8,000 20% 1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000 20% 1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
>

[hanesds@hotmail.com]

Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D
Ron de Bruin wrote:
> Hi D
>
> See J.E page
> http://www.mcgimpsey.com/excel/variablerate.html
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl

[hanesds@hotmail.com]

Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D

[RagDyeR]

Your L7 cell reference is in mixed form.

To copy it down rows without changing, just revise it to an absolute
reference:
$L$7

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

<hanesds@hotmail.com> wrote in message
news:1131578091.462638.317530@o13g2000cwo.googlegroups.com...
Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D