 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008use if condition more than 7 times in a cell of office 2003
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 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) >I have a tax rate slab like > Sl. Income Rate [quoted text clipped - 18 lines] > So how could I calculate tax as above of a person, > Thanks in advance 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. > Try this: > [quoted text clipped - 48 lines] > > So how could I calculate tax as above of a person, > > Thanks in advance 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 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. > Hi, > [quoted text clipped - 6 lines] > > arno Pls. read carefully what so many people wrote. You need vlookup with FALSE and a corresponding table. >I have a tax rate slab like >Sl. Income Rate [quoted text clipped - 18 lines] >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 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. > >I have a tax rate slab like > >Sl. Income Rate [quoted text clipped - 39 lines] > =VLOOKUP(A1,tbl,2) > --ron >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 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)  SignatureRegards Roger Govier > I have a tax rate slab like > Sl. Income Rate [quoted text clipped - 18 lines] > So how could I calculate tax as above of a person, > Thanks in advance 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. > Hi > [quoted text clipped - 36 lines] > > So how could I calculate tax as above of a person, > > Thanks in advance |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.