Creating a variable location and shipping/kg formula

Example postage.xlsx


Hi everyone,

Just getting into excel recently for work purposes and have been struggling to come up with a formula. Info is above in the link.

Basically trying to create a shipping chart that has prices that change depending on where in the world there are and how heavy the package is.

The main part is wondering what formulae I'd use for the prices above 2kg (or any formula), as from that weight range it's an extra 2,80 per 0.5kg.

I'm absolutely lost, not sure if it's been to use a lookup or create a table, ideally I'd want a cell that reflects the right price based on the total kg and location.

Cheers!
Michael

Thanks for the reply Hans! Here's the rest of the data for below 2kg (each line would be a different country location).

.5kg 1kg 1.5kg 2kg

12.03 16.15 20.28 24.41 NZ ($2.80/.5kg after 2kg)
24.99 35.07 45.14 55.22
21.12 30.81 40.53 50.26
26.61 37.33 48.06 58.78
27.41 38.46 49.51 60.56


Once it's over 2kg, for every 500g it's $2.80 (depending on location), so yes for every 1kg it's $5.60 extra. The postage is based on 'up to' so the per gram you said is right, but not factored in by their postage prices. Thanks!

Example:

Someone in NZ is going to buy 3.7kg of stuff

the 2kg caps at 24.41, then $2.80 for every 500 grams extra.

2kg + 4 x $2.80 (24.41 + 11.20) = 35.61 would be the end result.

Thanks!

Oh my gosh that's incredible, thank you so so so much, I really appreciate that! You're a wizard and excel is so useful haha