Excel Excel formula for price banded shipping rates

Joined
Apr 26, 2015
Messages
1
Reaction score
0
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

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Welcome to the forum Jim! :wave:

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top