Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or if statement
hey i really need help with a formula because i don't know which one to use.
i have this information 0-10000 = O% tax 10000-90000 = 20% tax 90001-100000= 30% tax and i have a lot of values for it but i dont know how to make it like simple to work out as one formula like the IF statement can be used if it only had to variables but it has three and i dont know what formula to use. please help its really important thank you heaps. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or if statement
a) Using IF: =IF(A190000,30%,IF(A110000,20%,0%))
but you are limited to 7 level of nesting with Excel version prior to XL2007 b) With LOOKUP: =LOOKUP(A1,{0,10001,90001},{0,0.2,0.3}) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "andy" wrote in message ... hey i really need help with a formula because i don't know which one to use. i have this information 0-10000 = O% tax 10000-90000 = 20% tax 90001-100000= 30% tax and i have a lot of values for it but i dont know how to make it like simple to work out as one formula like the IF statement can be used if it only had to variables but it has three and i dont know what formula to use. please help its really important thank you heaps. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or if statement
Hi
=(0.2*(A210000)+0.1*(A290000))*A2 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "andy" wrote in message ... hey i really need help with a formula because i don't know which one to use. i have this information 0-10000 = O% tax 10000-90000 = 20% tax 90001-100000= 30% tax and i have a lot of values for it but i dont know how to make it like simple to work out as one formula like the IF statement can be used if it only had to variables but it has three and i dont know what formula to use. please help its really important thank you heaps. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or if statement
You may need to clarify what you want. Are you looking for the tax rate, or
the tax payable? If the latter, presumably the rate is applicable on the amount within that band? If when you say "i have a lot of values for it" you mean that there are a large number of additional steps beyond the 30% band, then you may be better with a lookup. Tax rate: =IF(A1<=10000,0,IF(A1<=90000,20%,IF(A1<100000,30%, "undefined"))) Tax payable: =IF(A1100000,"undefined",30%*(MIN(10000,MAX(A1-90000,0)))+20%*(MIN(80000,MAX(A1-10000,0)))) -- David Biddulph "andy" wrote in message ... hey i really need help with a formula because i don't know which one to use. i have this information 0-10000 = O% tax 10000-90000 = 20% tax 90001-100000= 30% tax and i have a lot of values for it but i dont know how to make it like simple to work out as one formula like the IF statement can be used if it only had to variables but it has three and i dont know what formula to use. please help its really important thank you heaps. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or if statement
See:
http://www.mcgimpsey.com/excel/variablerate.html "David Biddulph" wrote: You may need to clarify what you want. Are you looking for the tax rate, or the tax payable? If the latter, presumably the rate is applicable on the amount within that band? If when you say "i have a lot of values for it" you mean that there are a large number of additional steps beyond the 30% band, then you may be better with a lookup. Tax rate: =IF(A1<=10000,0,IF(A1<=90000,20%,IF(A1<100000,30%, "undefined"))) Tax payable: =IF(A1100000,"undefined",30%*(MIN(10000,MAX(A1-90000,0)))+20%*(MIN(80000,MAX(A1-10000,0)))) -- David Biddulph "andy" wrote in message ... hey i really need help with a formula because i don't know which one to use. i have this information 0-10000 = O% tax 10000-90000 = 20% tax 90001-100000= 30% tax and i have a lot of values for it but i dont know how to make it like simple to work out as one formula like the IF statement can be used if it only had to variables but it has three and i dont know what formula to use. please help its really important thank you heaps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup or If Statement? | Excel Discussion (Misc queries) | |||
using vlookup within an if statement | Excel Worksheet Functions | |||
Vlookup with if statement | Excel Discussion (Misc queries) | |||
Vlookup with If Statement | Excel Worksheet Functions | |||
If statement with Vlookup | Excel Worksheet Functions |