If statement with range and varying false results

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:42 PM GMT


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.


©2024 eLuminary LLC. All rights reserved.