FORMULA FOR if(GREATER THAN, BUT LESS THAN,THIS,OR THAT

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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)