use if condition more than 7 times in a cell of office 2003

[Montu]

I have a tax rate slab like
Sl. Income Rate
1 1500 or Less Nil
2 1501 to 2000 18
3 2001 to 3000 25
4 3001 to 5000 30
5 5001 to 6000 40
7 6001 to 7000 45
8 7001 to 8000 50
9 8001 to 9000 90
10 9001 to 15000 110
11 15001 to 25000 130
12 25001 to 40000 150
13 40001 to above 200

Now I want to put a formula to calculate the tax of a person. So I have
write a formula lik
=if(a2=1500,"Nil",if(a2<2001,18,if(a2<3001,25,if(a2<5001,30,if(a2<6001,40,if(a2<7001,45,if(a2<8001,50,if(a2<9001,90,if(a2<15001,110,if(a2<25001,130,if(a2<40001,150,200))))))))))))
but problem is that ms excel 2003 not allowed input more than 7 times if
condition at a time in a cell.
So how could I calculate tax as above of a person,
Thanks in advance

 

[Ron Coderre]

Try this:

Put this table in F1:G13
Income Rate
0 Nil
1501 18
2001 25
3001 30
5001 40
6001 45
7001 50
8001 90
9001 110
15001 130
25001 150
40001 200

Then, this formula returns the rate associated with the value in A2

=VLOOKUP(A2,F2:G13,2,1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[arno]

Hi,

I'd guess that you need an extra column where you calculate the tax for each
row if you have to split the income according to rows. otherwise make a
better table and use vlookup on it. make sure you set true/false right! (You
will need "TRUE" this is why it is important to have a perfect table.)

See Excel help on vlookup.

arno

 

[Ron Rosenfeld]

I have a tax rate slab like
Sl. Income Rate
1 1500 or Less Nil
2 1501 to 2000 18
3 2001 to 3000 25
4 3001 to 5000 30
5 5001 to 6000 40
7 6001 to 7000 45
8 7001 to 8000 50
9 8001 to 9000 90
10 9001 to 15000 110
11 15001 to 25000 130
12 25001 to 40000 150
13 40001 to above 200

Now I want to put a formula to calculate the tax of a person. So I have
write a formula like
=if(a2=1500,"Nil",if(a2<2001,18,if(a2<3001,25,if(a2<5001,30,if(a2<6001,40,if(a2<7001,45,if(a2<8001,50,if(a2<9001,90,if(a2<15001,110,if(a2<25001,130,if(a2<40001,150,200))))))))))))
but problem is that ms excel 2003 not allowed input more than 7 times if
condition at a time in a cell.
So how could I calculate tax as above of a person,
Thanks in advance

Set up your table like this and NAME it tbl.

Income Rate
0 0
1500 18
2000 25
3000 30
5000 40
6000 45
7000 50
8000 90
9000 110
15000 130
25000 150
40000 200

Then use this formula:

=VLOOKUP(A1,tbl,2)
--ron

 

[Roger Govier]

Hi

Set up your table as follows
0 Nil
1500 18
2000 25
5000 30
6000 45
7000 50
8000 90
9000 110
15000 130
25000 150
40000 200

With Income in C1, enter in D1
=VLOOKUP(C1,$A$1:$B$11,2,1)

 

[Montu]

Thanks for your suggession, but I don't want to use vlookuup formula, because
this formula only find the exact value i.e. 1500 or 2000 or 3000 etc. but I
want to calculate in between value i.e. 1501 to 2000 or 2001 to 3000 etc. So
I thaught that if function can solve this problem. So what should I do ?
Please help me.

 

[Montu]

Thanks for your suggession, but I don't want to use vlookuup formula, because
this formula only find the exact value i.e. 1500 or 2000 or 3000 etc. but I
want to calculate in between value i.e. 1501 to 2000 or 2001 to 3000 etc. So
I thaught that if function can solve this problem. So what should I do ?
Please help me.

 

[Montu]

Thanks for your suggession, but I don't want to use vlookuup formula, because
this formula only find the exact value i.e. 1500 or 2000 or 3000 etc. but I
want to calculate in between value i.e. 1501 to 2000 or 2001 to 3000 etc. So
I thaught that if function can solve this problem. So what should I do ?
Please help me.

 

[Montu]

Thanks for your suggession, but I don't want to use vlookuup formula, because
this formula only find the exact value i.e. 1500 or 2000 or 3000 etc. but I
want to calculate in between value i.e. 1501 to 2000 or 2001 to 3000 etc. So
I thaught that if function can solve this problem. So what should I do ?
Please help me.

 

[arno]

Pls. read carefully what so many people wrote. You need vlookup with FALSE
and a corresponding table.

 

[Ron Rosenfeld]

Thanks for your suggession, but I don't want to use vlookuup formula, because
this formula only find the exact value i.e. 1500 or 2000 or 3000 etc. but I
want to calculate in between value i.e. 1501 to 2000 or 2001 to 3000 etc. So
I thaught that if function can solve this problem. So what should I do ?
Please help me.

From what you wrote to me and others, I can only assume that you did not even
bother to try my suggestion. And you clearly don't understand how to use
VLOOKUP.

You have concluded that the IF function would solve your problem and are
unwilling to even try out other suggestions.

Since you won't even do us the courtesy of trying our suggestions, go spend
your money on Excel 2007 which will allow more nested IF's than seven.
--ron