Calculating shipping costs

[LPJR]

Hello everyone. I'm a newbie here and have searched the New Users
threads with no luck.

I have a small sales business and have created a workbook to figure
pricing. The problem I'm having is calculating shipping costs. We use
UPS and they charge by the pound. I can total the weight of all items
on an order but I can't figure out how to make the worksheet find the
shipping costs based on the weight totals.

For example, the freight weights are:
Weight, lbs Price
Under 3 5.95
3 - 6.9 6.85
7 - 13.9 7.85
14 - 23.9 8.90
24 - 39.9 0.35 per lb
40 - 59.9 0.31 per lb
and so on down to
Over 1000 0.24 per lb.

I've tried some of the VLOOKUP formulas in this forum without luck. I
just don't know what I'm doing.

Can someone help?

Thank you very much,

Lynn

 

[Rowan Drummond]

Hi Lynn

The vlookup is your best option you just need to set your data up in the
right format.

Create a lookup table that looks like this:
0 5.95
3 6.85
7 7.85
14 8.9
etc

Assuming this table is in the range A2:B20 and your weight is in cell D1
then the vlookup formula would be:
=VLOOKUP(D1,$A$2:$B$20,2,1)

See Debra Dalgleish's notes at:
http://www.contextures.com/xlFunctions02.html#Range

Hope this helps
Rowan

 

[JE McGimpsey]

A straight VLOOKUP will work fine for weights < 24 lbs. After that, the
shipping cost is proportional to the weight.

One workaround could be

=VLOOKUP(D1,$A$2:$B$20, 2, TRUE) * IF(D1<24, 1,D1)

Note that if your package weighs 14 to 23.9 lbs, it's cheaper to add
enough gravel to make the package 24 pounds.

 

[LPJR]

Thanks, everyone. It works!!!

JE, I entered your formula and then modified it to fit my worksheet
parameters.

The actual formula I wound up with is:
=VLOOKUP(F305,$Q$315:$R$325, 2, TRUE) * IF(F305<24, 1,F305)

I had some trouble at first. Everything worked until the order weight
went over 24 lbs. Then all I got for a result was the multiplier
instead of the total shipping cost. I discovered that I entered >24
rather than <24 and changed > for < like you had in your formula and
we're in business.

Thank you both so much. I have been trying to do this on my own for so
long I'm almost ashamed to admit it and I wasn't even close.

This forum is awesome!

Lynn