Calculating shipping costs

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

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

LPJR wrote:
> 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
>
>

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.



In article <[email protected]>,
Rowan Drummond <[email protected]> wrote:

> 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
>
> LPJR wrote:
> > 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
> >
> >

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

Hello,
I have a similar problem with a slightly different twist. In my case there is a base weight and then additional weight. Upto 39 Kgs its considered a base weight and it has a few ranges e.g. from 0 - 2 Kgs, from 2.01 - 4 Kgs etc.

I have actually worked around this by breaking the weight into base+additional weight in seperate columns and then calculating the
1. price for base weight through VLookup
2. Multiplying the additional weight by USD 0.7 - which is the standard rate for anything above 39 Kgs.

but i was just wondering is there any way to do this more efficiently?

Google drive link to file :

https://drive.google.com/file/d/0B5Y...ew?usp=sharing