How do I write an IF statement that falls within a range? I am trying to create a formula that calculates additional charge in a range per set dollar increments. Example: If our subtotal is less than $500, charge $0, $501-1000 charge $100, $1001-1500 charge $200, $1501-2000 charge $300, over $2000 charge $0.
You would set up a table like this:
0 0 501 100 1001 200 1501 300 2001 0
assume that table is in Sheet2 starting in A2
assume you enter 600 in cell B9 of sheet1, then in C9 you would have
that should produce the results you describe.
the 4th argument of True tells it to match the highest value less than or equal to the value being looked up.