IF formula based on weights and dimensions

[Arigato123]

Hello,

I have the length, width, height, and weights of products. The parameters are based on the Royal Mail pricing structure, which is as follows, the first price is 1st Class and the second is 2nd class.

Letter 100g: 72p, 47p
Large Letter 100g: £1.14, 87p (MAX DIMENSION DEPTH 25MM)
250g: £1.60, £1.21(MAX DIMENSION DEPTH 25MM)
500g: £2.05, £1.59(MAX DIMENSION DEPTH 25MM)
750g: £2.87, £2.19 (MAX DIMENSION DEPTH 25MM)
Small Parcel 1kg: £3.68, £3.00 (MAX DIMENSIONS 160 X 450 X 350)

Please note anything above 1.5KG I intend to send with a courier.

I would like to create a formula that would enter into the cell the price of the postage based on the rates above. My sheet is below:

Dropship Master List 23022022 (Mr Excel).xlsx
	A	B	C	D	E
1	Height (cm)	Length (cm)	Width (cm)	Weight (kg)	Column1
2	2.60	24.00	9.00	0.24
3	2.60	24.00	9.00	0.27
4	5.00	31.50	11.00	1.54
5	6.00	35.50	12.00	1.02
6	5.00	21.50	11.00	0.54
7	4.30	28.00	9.50	0.42
8	3.00	16.20	2.50	0.41
9	5.00	13.00	26.50	1.08
10	3.00	16.50	2.50	0.40
11	5.00	13.00	26.50	1.30
12	3.00	16.50	2.50	0.41
13	4.50	21.50	11.00	0.56
14	3.00	11.00	21.00	0.62
15	4.50	21.50	11.00	0.60
Dropship Master List

 

[DRSteele]

There is nothing in the raw data to tell us whether something is a Letter or a Large Letter. Also the raw data doesn't have a field for 'Depth'.

 

[Arigato123]

There is nothing in the raw data to tell us whether something is a Letter or a Large Letter. Also the raw data doesn't have a field for 'Depth'.

If you take the “depth” (this is taken directly from the Royal Mail rate card hence the difference in diction) to be height from my data this should clear things up.

In the data I have added there wouldn’t be anything that is classed as letter as nothing is less than 100g.

Ideally the formula would look at the data and calculate that if it’s depth/height is greater than 25mm then it’s not a large letter and then if it’s between 500g and 750g the cost is 2.05 or 1.59 depending on class.

The aim is to get the cost, the method doesn’t matter as long as we know how much it’s going to cost.

Thanks.