Excel Guru Needed

[Jayhawks]

I am no good with excel formulas at all, but was hoping someone could put together one to figure shipping cost on the bulk orders. I got a shipping quote from CT
Your goods total weight:17.5 kgs = 28.03 + 34 x 5.9 = 228.63 USD + 5% PayPal fee = 240.06 USD

All the items on CT have the weights on the page so if this could all be plugged in to a spread sheet this might help those who are trying to find a fair way to charge shipping on the Bulk orders

Just a thought

Link to the shipping chart
http://www.centraltrade.eu/catalog/...id=11&osCsid=67568825f07e9fa0d17ee2253eed9a31

 

[aasupplyny]

240/17.5=13.7117
Round that number up and you get $13.80 per kg

Excel formula for multiplication : a1*a2 (a1 or a2 has to be 13.8)

Its actually faster to do the multiplying with a calculator.
With excel, you'd have to plug all those formulas to each box.

 

[redrose]

Hey aasupplyny,
You don't have to paste the formula over and over, just do the formula once, select it's cell and highlight it and the cells you want it to repeat in. Then "fill down" is you're friend

 

[redrose]

KG's $ paypal ttl
0.50 $28.03 $1.40 $29.43
1.00 $33.93 $1.70 $35.63
1.50 $39.83 $1.99 $41.82
2.00 $45.73 $2.29 $48.02
2.50 $51.63 $2.58 $54.21
3.00 $57.53 $2.88 $60.41
3.50 $63.43 $3.17 $66.60
4.00 $69.33 $3.47 $72.80
4.50 $75.23 $3.76 $78.99
5.00 $81.13 $4.06 $85.19
5.50 $87.03 $4.35 $91.38
6.00 $92.93 $4.65 $97.58
6.50 $98.83 $4.94 $103.77
7.00 $104.73 $5.24 $109.97
7.50 $110.63 $5.53 $116.16
8.00 $116.53 $5.83 $122.36
8.50 $122.43 $6.12 $128.55
9.00 $128.33 $6.42 $134.75
9.50 $134.23 $6.71 $140.94
10.00 $140.13 $7.01 $147.14
10.50 $146.03 $7.30 $153.33
11.00 $151.93 $7.60 $159.53
11.50 $157.83 $7.89 $165.72
12.00 $163.73 $8.19 $171.92
12.50 $169.63 $8.48 $178.11
13.00 $175.53 $8.78 $184.31
13.50 $181.43 $9.07 $190.50
14.00 $187.33 $9.37 $196.70
14.50 $193.23 $9.66 $202.89
15.00 $199.13 $9.96 $209.09
15.50 $205.03 $10.25 $215.28
16.00 $210.93 $10.55 $221.48
16.50 $216.83 $10.84 $227.67
17.00 $222.73 $11.14 $233.87
17.50 $228.63 $11.43 $240.06
18.00 $234.53 $11.73 $246.26
18.50 $240.43 $12.02 $252.45
19.00 $246.33 $12.32 $258.65
19.50 $252.23 $12.61 $264.84
20.00 $258.13 $12.91 $271.04
20.50 $264.03 $13.20 $277.23
21.00 $269.93 $13.50 $283.43
21.50 $275.83 $13.79 $289.62
22.00 $281.73 $14.09 $295.82
22.50 $287.63 $14.38 $302.01
23.00 $293.53 $14.68 $308.21
23.50 $299.43 $14.97 $314.40
24.00 $305.33 $15.27 $320.60
24.50 $311.23 $15.56 $326.79
25.00 $317.13 $15.86 $332.99
25.50 $323.03 $16.15 $339.18
26.00 $328.93 $16.45 $345.38
26.50 $334.83 $16.74 $351.57
27.00 $340.73 $17.04 $357.77

 

[redrose]

Hi Jayhawks, that's the chart based on $5.90 per half kg after the initial $28.03. However, i'm not sure there's not a surcharge for oversized boxes.
I feel the fairest way to charge shipping on bulk is to take the weight of each person's order, determine what percentage that is of the total weight, and then charge that percent of the total shipping cost. ie:
total order weighs 100 lbs, total shipping is $200. My order weighs 25 lbs, which is 25% of the weight. I would pay 25% of $200 = $50.

 

[Spikey]

Hi Jayhawks, that's the chart based on $5.90 per half kg after the initial $28.03. However, i'm not sure there's not a surcharge for oversized boxes.
I feel the fairest way to charge shipping on bulk is to take the weight of each person's order, determine what percentage that is of the total weight, and then charge that percent of the total shipping cost. ie:
total order weighs 100 lbs, total shipping is $200. My order weighs 25 lbs, which is 25% of the weight. I would pay 25% of $200 = $50.

I would have done it by percentage of shipping calculated by percentage of total order for that person... but this seems more fair (harder to calculate, but more fair )

 

[Jayhawks]

great post redrose.

 

[redrose]

Well some places do charge shipping based on price of the total order, which I never understood till I realized they don't sell anyhing very heavy, lol. In these bulk orders, the cost/total of anyone's order means nothing in relation to shipping. If you order 1000 atomizers at $50, and I order 5 bricks at $10, my 5 bricks cost alot more to ship

 

[redrose]

Jayhawks, emailed you the chart.