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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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
39
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,030
Messages
5,569,757
Members
412,289
Latest member
Kingchaos64
Top