Excel Excel formula for price banded shipping rates

[Jim Ludwig]

Hi. I am trying to create a formula that would output different numbers for different price bands. For example, if the value in cell A1 is (between $0 and $15, then 1)(between $15 and $25, then 2)(between $25 and $35, then 3)(between $35 and $45, then 4) and so forth. Any help would be appreciated.

 

[Becky]

Welcome to the forum Jim!

I'll start off by saying I'm no expert in excel, and there may well be a better way to do it that what I could suggest... But in my view it looks like you're going to need a chunky IF formula.

An IF formula checks whether a condition is met, and gives you a set value if the conditon is met and another set value if not. So the formula will be set out like this:

=IF(logical_test,value_if_true,value_if_false)

From the data you have given in your post, I presume you are working with a linear data set. So you could set it up something like this:

=IF(A1<15,1,IF(A1<25,2,IF(A1<35,3,IF(A1<45,3,etc))))

Where red is your first IF formula and the next IF formula (green) is the 'false', and so on. The etc should be replaced with further IF formulae if you want to go beyond 45.

The help function in Excel is quite useful if you get stuck - just type in 'IF formula' and it should bring up the right article.

Hope this helps you! Let me know how you get on.