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.
Kristyna,
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
=if(B9<>"",vlookup(B9,Sheet2!$A$2:$B$6,2,True),"")
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.
Advertisement