Converting Excel 2007 Nested "If" Statement to Excel 2003: Overcom

[shabutt]

=IF(E33<=250000,(180000*0%)+(E33-180000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=350000,(250000*0.5%)+(E33-250000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=400000,(350000*0.75%)+(E33-350000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=450000,(400000*1.5%)+(E33-400000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=550000,(450000*2.5%)+(E33-450000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=650000,(550000*3.5%)+(E33-550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=750000,(650000*4.5%)+(E33-650000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=900000,(750000*6%)+(E33-750000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1050000,(900000*7.5%)+(E33-900000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1200000,(1050000*9%)+(E33-1050000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1450000,(1200000*10%)+(E33-1200000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1700000,(1450000*11%)+(E33-1450000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1950000,(1700000*12.5%)+(E33-1700000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=2250000,(1950000*14%)+(E33-1950000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=2850000,(2250000*15%)+(E33-2250000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=3550000,(2850000*16%)+(E33-2850000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=4550000,(3550000*17.5%)+(E33-3550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=8650000,(4550000*18.5%)+(E33-4550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33>8650000,(8650000*19%)+(E33-8650000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3))))))))))))))))))))

I have created the above formula in excel 2007 to calculate tax. This
formula works perfectly as excel 2007 allows us to use more than 7 nested if
statements. But this formula won't work in excel 2003. What could be changed
in this formula so it would work in excel 2003? I am looking forward to
helpful suggestions.

 

[Bob Phillips]

What you could do is create another lookup table structured like so (I will
leave you to complete it)

0 0.00% 180000
250001 0.50% 250000
350001 0.75% 350000
450001 1.50% 450000
550001 2.50% 550000


call it say mult_Vals and use

=(VLOOKUP(E31,mult_vals,2)*VLOOKUP(E31,mult_vals,3))+((E31-VLOOKUP(E31,mult_vals,3))*(VLOOKUP(E31,Tax_Slabs_2009,3)*VLOOKUP(E31,Marginal_Tax_2009,3)))
and in future, develop in 2003 to avoid these problems.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

You can overcome the limit of 7 nested functions by constructing your
formula like this:

=IF(condition1,formula1,0)+IF(condition2,formula2,0)+IF(condition3,formula3,0) ...

and so on. Zeroes are added if the condition is not met, so only those
conditions which are TRUE contribute to the value. Perhaps you can
incorporate this approach in your formula (but be wary of the length
of the formula).

Hope this helps.

Pete

 

[Harlan Grove]

I have created the above formula in excel 2007 to calculate tax. This
formula works perfectly as excel 2007 allows us to use more than 7 nested if
statements. But this formula won't work in excel 2003. What could be changed
in this formula so it would work in excel 2003? I am looking forward to
helpful suggestions.

Helpful suggestion: if you need to support Excel 2003 or older
versions, don't develop spreadsheet models using Excel 2007. Workbooks
developed using Excel 2003 should cause fewer problems when running
under Excel 2007 than the reverse.

As for your formula, first there seems to be a bug: if E33 < 180000,
do you really want a negative result (assuming the two VLOOKUP calls
each return positive values)? Or do you want a zero result when E33 <
180000?

Next, the two VLOOKUP calls in each IF call's second argument are ALL
the same, so you'd get the same result by using two nested IF
constructs and multiplying the latter by the two VLOOKUP calls. You
could also pull the 'E33-' outside the second IF construct. I.e.,

=IF(E33<=250000,0,IF(E33<=350000,250000*0.5%,
IF(E33<=400000,350000*0.75%,IF(E33<=450000,400000*1.5%,
IF(E33<=550000,450000*2.5%,IF(E33<=650000,550000*3.5%,
IF(E33<=750000,650000*4.5%,IF(E33<=900000,750000*6%,
IF(E33<=1050000,900000*7.5%,IF(E33<=1200000,1050000*9%,
IF(E33<=1450000,1200000*10%,IF(E33<=1700000,1450000*11%,
IF(E33<=1950000,1700000*12.5%,IF(E33<=2250000,1950000*14%,
IF(E33<=2850000,2250000*15%,IF(E33<=3550000,2850000*16%,
IF(E33<=4550000,3550000*17.5%,IF(E33<=8650000,4550000*18.5%,
IF(E33>8650000,8650000*19%+(E33-8650000))))))))))))))))))))
+(E33-IF(E33<=250000,180000,IF(E33<=350000,250000,
IF(E33<=400000,350000,IF(E33<=450000,400000,
IF(E33<=550000,450000,IF(E33<=650000,550000,
IF(E33<=750000,650000,IF(E33<=900000,750000,
IF(E33<=1050000,900000,IF(E33<=1200000,1050000,
IF(E33<=1450000,1200000,IF(E33<=1700000,1450000,
IF(E33<=1950000,1700000,IF(E33<=2250000,1950000,
IF(E33<=2850000,2250000,IF(E33<=3550000,2850000,
IF(E33<=4550000,3550000,IF(E33<=8650000,4550000,
IF(E33>8650000,8650000))))))))))))))))))))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

But both nested IFs could be replaced by LOOKUP calls.

=LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{0;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}
*{0;0.005;0.0075;0.015;0.025;0.035;0.045;0.06;0.075;0.09;
0.1;0.11;0.125;0.14;0.15;0.16;0.175;0.185;0.19})
+(E33-LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{180000;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

And you could also replace the 3rd argument of the first LOOKUP call
with its array product.

=LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{0;1250;2625;6000;11250;19250;29250;45000;67500;94500;
120000;159500;212500;273000;337500;456000;621250;841750;1643500})
+(E33-LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{180000;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

Or you could put these values into a table, e.g.,

-1 180000 0
250000 250000 0.005
350000 350000 0.0075
400000 400000 0.015
450000 450000 0.025
550000 550000 0.035
650000 650000 0.045
750000 750000 0.06
900000 900000 0.075
1050000 1050000 0.09
1200000 1200000 0.1
1450000 1450000 0.11
1700000 1700000 0.125
1950000 1950000 0.14
2250000 2250000 0.15
2850000 2850000 0.16
3550000 3550000 0.175
4550000 4550000 0.185
8650000 8650000 0.19

name the first column Tbl_1, the second column Tbl_2 and the third
column Tbl_3, then use formulas like

=LOOKUP(E33-1/128,Tbl_1,Tbl_2*Tbl_3)+(E33-
LOOKUP(E33-1/128,Tbl_1,Tbl_2))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

 

[shabutt]

Dear Mr. Harlan Grove,

Your these two formulas work perfectly well in Excel 2003. Thank you very
much for helping me out.

=LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{0;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}
*{0;0.005;0.0075;0.015;0.025;0.035;0.045;0.06;0.075;0.09;
0.1;0.11;0.125;0.14;0.15;0.16;0.175;0.185;0.19})
+(E33-LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{180000;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

=LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{0;1250;2625;6000;11250;19250;29250;45000;67500;94500;
120000;159500;212500;273000;337500;456000;621250;841750;1643500})
+(E33-LOOKUP(E33-1/128,
{-1;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000},
{180000;250000;350000;400000;450000;550000;650000;750000;900000;1050000;
1200000;1450000;1700000;1950000;2250000;2850000;3550000;4550000;8650000}))
*VLOOKUP(E33,Tax_Slabs_2009,3)*VLOOKUP(E33,Marginal_Tax_2009,3)

There is another way to overcome the 7 nested if statement limitation in
excel 2003 but it takes more than one cell. For example, my excel 2007
formula has 19 if statements and if in cell H35 I write first 7 nested if
statements and refer to cell H36 in it (which has 7 more nested if
statemenets) and then cell H37 (which has 5 more nested if statements) is
refered in cell H36, the limitation could be overcome.

The first cell will always have the desired result.

The cell H35 will have this:
=IF(E33<=250000,(180000*0%)+(E33-180000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=350000,(250000*0.5%)+(E33-250000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=400000,(350000*0.75%)+(E33-350000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=450000,(400000*1.5%)+(E33-400000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=550000,(450000*2.5%)+(E33-450000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=650000,(550000*3.5%)+(E33-550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=750000,(650000*4.5%)+(E33-650000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),H36)))))))

The cell H36 will have this:
=IF(E33<=900000,(750000*6%)+(E33-750000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1050000,(900000*7.5%)+(E33-900000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1200000,(1050000*9%)+(E33-1050000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1450000,(1200000*10%)+(E33-1200000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1700000,(1450000*11%)+(E33-1450000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=1950000,(1700000*12.5%)+(E33-1700000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=2250000,(1950000*14%)+(E33-1950000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),H37)))))))

The cell H37 will have this:
=IF(E33<=2850000,(2250000*15%)+(E33-2250000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=3550000,(2850000*16%)+(E33-2850000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=4550000,(3550000*17.5%)+(E33-3550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33<=8650000,(4550000*18.5%)+(E33-4550000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3),IF(E33>8650000,(8650000*19%)+(E33-8650000)*(VLOOKUP(E33,Tax_Slabs_2009,3))*VLOOKUP(E33,Marginal_Tax_2009,3))))))

These were the source tables:

Tax_Slabs_2009
180,000 0%
180,001 250,000 0.50%
250,001 350,000 0.75%
350,001 400,000 1.50%
400,001 450,000 2.50%
450,001 550,000 3.50%
550,001 650,000 4.50%
650,001 750,000 6.00%
750,001 900,000 7.50%
900,001 1,050,000 9.00%
1,050,001 1,200,000 10.00%
1,200,001 1,450,000 11.00%
1,450,001 1,700,000 12.50%
1,700,001 1,950,000 14.00%
1,950,001 2,250,000 15.00%
2,250,001 2,850,000 16.00%
2,850,001 3,550,000 17.50%
3,550,001 4,550,000 18.50%
4,550,001 8,650,000 19.00%
8,650,001 20.00%

Marginal_Tax_2009
- 500,000 20%
500,001 1,050,000 30%
1,050,001 2,000,000 40%
2,000,001 4,450,000 50%
4,450,001 60%