If statement with range and varying false results

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

Question

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.

Answer

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

©2021 eLuminary LLC. All rights reserved.