Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA FOR if(GREATER THAN, BUT LESS THAN,THIS,OR THAT
I am trying write a formula to include variable tax percents applied to
specifc ranges as in this example: If greater than 500 but less than 1000, then X% if greater than 1000, but less than 1500, then Y% if greater than 1500 but less than 2000 then Z% and so on for 6 ranges |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA FOR if(GREATER THAN, BUT LESS THAN,THIS,OR THAT
Create a table of value pairs
500 x% 1000 Y% etc and use =VLOOKUP(val,A1:B10,2,TRUE) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MINI MART" wrote in message ... I am trying write a formula to include variable tax percents applied to specifc ranges as in this example: If greater than 500 but less than 1000, then X% if greater than 1000, but less than 1500, then Y% if greater than 1500 but less than 2000 then Z% and so on for 6 ranges |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA FOR if(GREATER THAN, BUT LESS THAN,THIS,OR THAT
Hi
One way Create a lookup table and give it a defined name Rates. The table would look something like 0 0 500 5% 1000 6% 1500 7% 2000 8% 2500 9% 3000 10% substituting your values for the top of each range and the appropriate % rate alongside. It is important to have the 0 value for the first row and whatever you want the outcome to be for values below 500 Then with the value to be used fro the calculation in cell A1, the result would be =A1*VLOOKUP(A1,Rates,2) -- Regards Roger Govier "MINI MART" wrote in message ... I am trying write a formula to include variable tax percents applied to specifc ranges as in this example: If greater than 500 but less than 1000, then X% if greater than 1000, but less than 1500, then Y% if greater than 1500 but less than 2000 then Z% and so on for 6 ranges |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |