Calculating Courier Costs

Try this:

=(MAX(0,D6-E14)*D14)+(MAX(0,D6-E13)*D13)+D12

Try this:

=MAX(6.25,SUMPRODUCT(--(D6>{0,20,100}),--(D6-{0,20,100}),{0.3125,-0.0325,0.07}))

see cells (in sheet) shaded purple for derivation of the bits in red (above)

See Post 3.

No, it returns what you asked for:
AliGW on MS365 Insider (Windows) 64 bit

	C	D	E	F
2	Courier Charges
3	Next Day 16:00 Service
4
5		Weight (kg)	Courier Cost
6	Combined weight of all parcels	21.00	£6.53	=(MAX(0,D6-E14)*D14)+(MAX(0,D6-E13)*D13)+D12

Sheet: Sheet1

Ali, you haven't told anyone what is in E13 and E14...

See below:

AliGW on MS365 Insider (Windows) 64 bit

	C	D	E	F
2	Courier Charges
3	Next Day 16:00 Service
4
5		Weight (kg)	Courier Cost
6	Combined weight of all parcels	21.00	£6.53	=(MAX(0,D6-E14)*D14)+(MAX(0,D6-E13)*D13)+D12
7
8
9
10
11
12	Cost for the first 20 Kg	£ 6.25	0
13	Cost of each Kg from 20Kg to 100Kg	£ 0.28	20
14	Cost of each Kg above 100Kg	£ 0.35	100

Sheet: Sheet1

Or this:

=(MAX(0,D6-100)*D14)+(MAX(0,D6-20)*D13)+D12

No need to do anything. The formula in F6 works perfectly for all values >100 Kg or otherwise. The values in the purple cells are not used. i just put them there for your information. they can be deleted, if you wish.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

If you want to do it that way, then see file...