Forum Discussion
Formula creation
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 | Date | Ordered By | Items | Parcels | Weight KG | Delivery | Pick Cost | Rework Cost | RM Carriage | APC Carriage | Total Cost |
| 123 | 13/08/24 | A Customer | 2 | 1 | RM | £0.80 | £0.26 | £3.95 | £5.01 | ||
| 124 | 13/08/24 | B Customer | 34 | 5 | 15 | APC | £8.80 | £6.25 | £0.00 | £7.95 | £23.00 |
| 125 | 13/08/24 | C Customer | 34 | 1 | 30 | APC | £8.80 | £1.25 | £0.00 | £15.90 | £25.95 |
| 126 | 13/08/24 | D Customer | 34 | 1 | 45 | APC | £8.80 | £1.25 | £0.00 | £23.45 | £33.50 |
| 127 | 13/08/24 | E Customer | 34 | 1 | 60 | APC | £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 used XLOOKUP, which is only available in Excel in Microsoft 365 and Office 2021.
The version attached to this reply uses VLOOKUP.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Hi, thanks for your reply, unfortunately (and a little surprisingly?), I am not able to attach a .xls or .xlsx document to this reply. So I have uploaded the file to DropBox, please use this link:
Thank you!
Thanks.
Question 1: enter the following formula in H2, then fill down.
=IF(D2=0, 0, 0.85+MAX(D2-2, 0)*0.27)
Question 2:
You can simplify the formula in J2 to
=IF(G2="RM", 3.95, IF(G2="TSRM", 4.95, 0))
In K2:
=IF(G3="APC", XLOOKUP(F3, {0,15,30,45,60}, {0,7.95,15.9,23.45,31.8}, "", 1), 0)
It would be better to create a lookup list in the hidden sheet VL - see the attached version. I unhid the VL sheet to show how it works.
