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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Bagsy Baker

New Member
Joined
Feb 17, 2002
Messages
41
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?
 

Jegacats

Board Regular
Joined
Jul 30, 2002
Messages
136

ADVERTISEMENT

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
:)
 

Jegacats

Board Regular
Joined
Jul 30, 2002
Messages
136
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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

On 2002-09-11 17:15, Jegacats wrote:
What is the data column for?

the data column has examples of the values you want to work out the shipping rates for.
 

snoopyhr

Active Member
Joined
Aug 12, 2002
Messages
395
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 :)
 

Jegacats

Board Regular
Joined
Jul 30, 2002
Messages
136
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.
 

Forum statistics

Threads
1,148,270
Messages
5,745,787
Members
423,973
Latest member
man_this_is_hard

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
Top