I am new to this forum and only a part-time MS Excel user, so apologise for any terminology errors.

I have the following spreadsheet (that I did not create, nor did I write all the current formulae in it) that I am struggling to create working formulae for, I am fairly certain it should work, but I can’t make it work and so was hoping somebody might be able to help:

Order DateOrdered By ItemsParcelsWeight KGDeliveryPick CostRework CostRM Carriage APC CarriageTotal Cost12313/08/24A Customer21 RM£0.80£0.26£3.95 £5.0112413/08/24B Customer34515APC£8.80£6.25£0.00£7.95£23.0012513/08/24C Customer34130APC£8.80£1.25£0.00£15.90£25.9512613/08/24D Customer34145APC£8.80£1.25£0.00£23.45£33.5012713/08/24E Customer34160APC£8.80£1.25£0.00£31.80£41.85

Question 1

The Pick Cost column currently has a formula in it (that works) and it looks like this:

=VLOOKUP(D2,VL!$C$23:VL!$D$522,2)

Unfortunately, the Pick charges have increased, so I need to amend the formula, but I have no idea how to do this. I need the new formula to work like this:

The Pick Cost for the first 2 Items is £0.85, the Pick Cost for every additional item after that is an additional £0.27

eg: the Pick Cost for 1 Item would be £0.85

the Pick Cost for 2 Items would be £0.85

the Pick Cost for 3 Items would be £1.12 (£0.85 + £0.27)

the Pick Cost for 10 Items would be £3.01 (£0.85 + (8x£0.27)) etc…

Question 2

The APC Carriage charge has always been worked out manually in the past. I would like to incorporate it into the spreadsheet, but the charge varies, depending on the total weight sent.

The RM Carriage column formula is written (and it works) so any APC Delivery entries have a value of £0.00:

=(IF(G3 = “RM”, 3.95, “”) & IF(G3=”TSRM”, 4.95, “”) & IF(G3=”APC”, 0, “”))*1

So I need a formula for the APC Carriage column that works like this:

If Weight KG has no value, the APC Carriage is £0.00;

If Weight KG is less than or equal to 15, the APC Carriage is £7.95;

but if Weight KG is greater than 15 and less than or equal to 30, the APC Carriage is £15.90;

but if Weight KG is greater than 30 and less than or equal to 45, the APC Carriage is £23.45;

but if Weight KG is greater than 45 and less than or equal to 60, the APC Carriage is £31.80

As an aside, the Weight KG column is NEVER filled in for RM Delivery entries.

Thanks in advance,

Sarah

​I am new to this forum and only a part-time MS Excel user, so apologise for any terminology errors.I have the following spreadsheet (that I did not create, nor did I write all the current formulae in it) that I am struggling to create working formulae for, I am fairly certain it should work, but I can’t make it work and so was hoping somebody might be able to help: Order DateOrdered By ItemsParcelsWeight KGDeliveryPick CostRework CostRM Carriage APC CarriageTotal Cost12313/08/24A Customer21 RM£0.80£0.26£3.95 £5.0112413/08/24B Customer34515APC£8.80£6.25£0.00£7.95£23.0012513/08/24C Customer34130APC£8.80£1.25£0.00£15.90£25.9512613/08/24D Customer34145APC£8.80£1.25£0.00£23.45£33.5012713/08/24E Customer34160APC£8.80£1.25£0.00£31.80£41.85 Question 1The Pick Cost column currently has a formula in it (that works) and it looks like this: =VLOOKUP(D2,VL!$C$23:VL!$D$522,2) Unfortunately, the Pick charges have increased, so I need to amend the formula, but I have no idea how to do this. I need the new formula to work like this: The Pick Cost for the first 2 Items is £0.85, the Pick Cost for every additional item after that is an additional £0.27 eg: the Pick Cost for 1 Item would be £0.85the Pick Cost for 2 Items would be £0.85the Pick Cost for 3 Items would be £1.12 (£0.85 + £0.27)the Pick Cost for 10 Items would be £3.01 (£0.85 + (8x£0.27)) etc… Question 2The APC Carriage charge has always been worked out manually in the past. I would like to incorporate it into the spreadsheet, but the charge varies, depending on the total weight sent.The RM Carriage column formula is written (and it works) so any APC Delivery entries have a value of £0.00:=(IF(G3 = “RM”, 3.95, “”) & IF(G3=”TSRM”, 4.95, “”) & IF(G3=”APC”, 0, “”))*1 So I need a formula for the APC Carriage column that works like this:If Weight KG has no value, the APC Carriage is £0.00;If Weight KG is less than or equal to 15, the APC Carriage is £7.95;but if Weight KG is greater than 15 and less than or equal to 30, the APC Carriage is £15.90;but if Weight KG is greater than 30 and less than or equal to 45, the APC Carriage is £23.45;but if Weight KG is greater than 45 and less than or equal to 60, the APC Carriage is £31.80 As an aside, the Weight KG column is NEVER filled in for RM Delivery entries. Thanks in advance,Sarah  Read More

​