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 |
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 |
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 |
All times are GMT +1. The time now is 08:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com