Calculate Courier Costs

Please could someone help provide a formula for me to calculate courier costs based on a consignments weight?

Courier charges are a flat fee for then first 1/2 kg and then a cost per kg, or part kg thereafter.

I have attached a basic spreadsheet as an example.

The spreadsheet lists part codes and their total weight in kg.
There is a £3 for the first 1/2 kg and £1 per kg or part kg there after.
For example cell B4 has a weight of 3.45kg, so the formula should return the result of £6.

The formulas I have tried do not seem to take into account the part kg's or the first 1/2 kg correctly.

Thanks

James

Thanks very much for the response.

Yes, it does need to round up to the nearest.
So for example the formula you have provided will return a cost of £3.27 in cell C2, but all part kg's should be charged as a whole kg, so the total cost should be £4.00. (£3 for the first 1/2 kg and then £1 for the remaining 0.27kg)

It's finding a formula that will multiply the part kg's as whole kg's that I've been struggling with.

Thanks,

James

Cell C2 should have this formula:
=IF(B2<0.5,3,3+ROUNDUP(B2-0.5,0))
then copy down the column.

Thanks very much for the response, it's returning the correct results, but it's not quite what I am after.

For example, I can see how I can link this formula to the cost for the first 1/2 kg to automate the formula should I change the £3 cost to another value.
But if I was to change the cost per kg thereafter, from £1 to another value, the formula doesn't take that into account, and I cannot see how I can amend the formula to account for that.

If you could help amend the formula slightly, so that if I was to change either of the costs in cells B8 and B9, it would update automatically, that would be fantastic and would genuinely make my day.

Great thought - planning ahead!
I think of those figures as CONSTANTS and usually put them in a block of cells to be referenced later.
You have them in cells B8 and B9, so just change hard-coded values (1 and 3) to the reference cells.
Cell C2 should have this formula:
=IF(B2<0.5,$B$8,$B$8+$B$9*ROUNDUP(B2-0.5,0))

That's brilliant. Thanks very much.
It seems reasonably simply now you have explained it, but I just couldn't piece it together.

So can you now, yourself, edit the formula to account for the 0.5 kg by referencing the CONSTANT cell - or would you like help with that?

I ok with that bit thanks candybg, though thanks for the offer of help.

Thanks for the response newdoverman

Hi
Wow what a great thread, i am looking to do something similar and have used the original excel spreadsheet and followed the previous advice and amended to work with what I need, I am now stuck as my courier has a three tier pricing structure and I am unsure how to add the final part for the combined weight of parcels, when the combined weight is over 100Kg then every Kg over 100Kg is charged at £0.35 per Kg