NESTED IF STATEMENTS

[Mark McDonough]

I am doing nested if statements but need to have more than 7 nested if
statements. What does one do to get more?
Formula is attached and it won't work and I think this is due to too many if
statements

=IF(C1<=12000,0.01*C1,
IF(C1<=30000,120+0.02*(C1-12000),
IF(C1<=50000,480+0.03*(C1-30000),
IF(C1<=100000,1080+0.035*(C1-50000),
IF(C1<=200000,2830+0.04*(C1-100000),
IF(C1<=250000,6830+0.0425*(C1-200000),
IF(C1<=300000,8955+0.475*(C1-250000),
IF(C1<=500000,11330+0.05*(C1-300000),
IF(C1>500000,21330+0.055*(C1-500000))))))))))

Any help is appreciated.

 

[Niek Otten]

Look here:

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Keep discussions in the newsgroups- no private emails please
|I am doing nested if statements but need to have more than 7 nested if
| statements. What does one do to get more?
| Formula is attached and it won't work and I think this is due to too many if
| statements
|
| =IF(C1<=12000,0.01*C1,
| IF(C1<=30000,120+0.02*(C1-12000),
| IF(C1<=50000,480+0.03*(C1-30000),
| IF(C1<=100000,1080+0.035*(C1-50000),
| IF(C1<=200000,2830+0.04*(C1-100000),
| IF(C1<=250000,6830+0.0425*(C1-200000),
| IF(C1<=300000,8955+0.475*(C1-250000),
| IF(C1<=500000,11330+0.05*(C1-300000),
| IF(C1>500000,21330+0.055*(C1-500000))))))))))
|
| Any help is appreciated.
|
|
|
|
|
|

 

[Guest]

I would switch from the IF function to VLOOKUP

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

My response to your previous post

=IF(C1<=1300,20,0)+
IF(C1<=10000,C1*1.5%,IF(C1<=30000,150+(C1-10000)*2%,
IF(C1<=75000,550+(C1-30000*2.5%),IF(C1<=150000,1675+(C1-75000)*3%,
IF(C1<=225000,3925+(C1-150000)*3.5%,IF(C1>225000,6550+(C1-225000)*4%,0))))))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Mark McDonough]

I've studied the site you refer to but don't think it will solve my problem
as I have individual amounts in addition to the rates. See my formula below

 

[Ron Rosenfeld]

I am doing nested if statements but need to have more than 7 nested if
statements. What does one do to get more?
Formula is attached and it won't work and I think this is due to too many if
statements

=IF(C1<=12000,0.01*C1,
IF(C1<=30000,120+0.02*(C1-12000),
IF(C1<=50000,480+0.03*(C1-30000),
IF(C1<=100000,1080+0.035*(C1-50000),
IF(C1<=200000,2830+0.04*(C1-100000),
IF(C1<=250000,6830+0.0425*(C1-200000),
IF(C1<=300000,8955+0.475*(C1-250000),
IF(C1<=500000,11330+0.05*(C1-300000),
IF(C1>500000,21330+0.055*(C1-500000))))))))))

Any help is appreciated.

I assume there is a typo in your line:

IF(C1<=300000,8955+0.475*(C1-250000)

and the 0.475 should be 0.0475


Use a different approach.

This one makes maintenance much simpler. And can be extended as needed.

Set up a table and name it, for example, TaxTable:

0 0 0.01
12000 120 0.02
30000 480 0.03
50000 1080 0.035
100000 2830 0.04
200000 6830 0.0425
250000 8955 0.0475
300000 11330 0.05
500000 21330 0.055

(Note that you can use a formula for column 2:

With your table in K1:M9

L2: =L1+(K2-K1)*M1

and copy/drag down to L9)


Now use the following formula:

=VLOOKUP(C1,TaxTable,2)+(C1-VLOOKUP(C1,TaxTable,1))*VLOOKUP(C1,TaxTable,3)



--ron