Figure Shipping costs

Jegacats

Board Regular
Joined
Jul 30, 2002
Messages
136
I'm making a price list and would like
to calculate the Shipping as follows

up to 14.99...5.99
from 15.00 to 24.99...6.25
from 25.00 to 29.00...6.55 and so on
What would the formula be ?

Thanks for all the help.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You need a lookup table that relates your cost bands to the cost for each band. Check out vlookup in the helpfile or search this board for loads of examples. Post back if you need more...

Paddy
 
Upvote 0
A couple of questions.

How many price breaks (levels) do you have?

Are the increments for levels and returned value constant or variable?

Are you looking for a formula or VB solution?
 
Upvote 0
What is the data column for? I've always
had problems with the vlookup and hlookup
I never did get those questions right in
my MOUS 97 Now I'm studying for MOUS2000
and still don't understand these darn lookups
:)
 
Upvote 0
Cost S & H 0 to14.99=5.99
$18.95 15.00 to 24.99=6.99
$21.95 25.00 to 34.99=7.99
$21.95
$159.95
I'm trying to enter a formula in the S & H column. I have 7 price increments up 50 and over.
 
Upvote 0
Book1
FGHIJKL
25Ordersheet
26lookuptableItemeQtyU-PriceTotal
27$-$5.99Item10101$1.00$1.00
28$15.00$6.25Item10203$5.50$16.50
29$25.00$6.55Item50102$6.59$13.18
30$30.00$7.50Item30201$20.50$20.50
31$40.00$8.00
32$50.00$10.00Subtotal$51.18
33$60.00$12.00ShippingandHandeling$6.25
34$70.00$14.00
35$80.00$16.00TotalOrdercost$57.43
36Tax-fromtableLoc1$3.45
37
38lookuptablefortaxTotalorder$60.88
39Loc16%
40Loc27%
41Loc34%
42Loc49%
43Loc54%
Sheet1


hope this help :)
 
Upvote 0
Thank you but I still can't get it right.
I've download the file to insert my worksheet
Maybe this will give you an better idea of what I want. thanks for all the help
S_H_and_tax_ calcul.xls
ABCDEF
1lookuptablefor S&H Suggested USRETAILClient's S&HProv SalesTax8%
2$-$5.99$18.95#N/A
3$15.00$6.25$21.95
4$25.00$6.55$21.95
5$30.00$7.50$159.95
6$40.00$8.00$16.95
7$50.00$10.00$9.95
8$60.00$12.00$10.95
9$70.00$14.00$7.95
10$80.00$16.00$10.95
11$9.95
12$1.00
13lookuptablefortax$19.95
14Prov8%$19.95
15Fed7%$19.95
16Loc34%$19.95
17Loc49%$99.95
18Loc54%$7.95
Sheet1


Oh dear I don't see an excel sheet.
 
Upvote 0

Forum statistics

Threads
1,219,034
Messages
6,145,893
Members
450,654
Latest member
lorento

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top