Shipping calculations for weight based sheet

[ianharper68]

Hello all,

I am building a spreadsheet for quoting my customer.

They order multiple items and we consolidate and then ship on once we have a full trailer.

My question I have been asked to give a collection rate based on weight of products rather than size of pallets.

My rates for instance would be as follows:

I have tried the following formula but it doesn't quite work and I can't work out what need to change to make it work correctly:

=IFS(G5<50,H5,G5<100,I5,G5<250,J5,G5<500,K5,G5<1000,L5,G5<2000,M5,G5<3000,N5,G5>3000,O5)

For instance if I put 49 in the KG column I get 35 as the price, then if I put 50 I get 35 however the formula says Less than <50 should only give that result.

My next issue is a want the total to be either 35 for less than 50 KG or the KG rate * total kgs to give a total cost?

I tried this and it works fine for anything over 51kg but I don't want it to calculate the Min charge of £35

=IFS(G5<50,H5,G5<100,I5,G5<250,J5,G5<500,K5,G5<1000,L5,G5<2000,M5,G5<3000,N5,G5>3000,O5)*G5

As you can see I have the issue of the first two lines not being correct values

Can somebody please help

 

[Fluff]

If you are happy to change the headers, how about

Fluff.xlsm
	G	H	I	J	K	L	M	N	O	P
4		0	50	100	250	500	1000	2000	3000
5	49	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35
6	50	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35
7	51	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35.7
8	99	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	69.3
9	1500	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	450
Sheet6

Cell Formulas
Range	Formula
P5:P9	P5	=INDEX(H5:O5,XMATCH(G5,$H$4:$O$4,-1))*(IF(G5<50,1,G5))

 

[ianharper68]

Perfect thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ianharper68]

If you are happy to change the headers, how about

Fluff.xlsm
	G	H	I	J	K	L	M	N	O	P
4		0	50	100	250	500	1000	2000	3000
5	49	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35
6	50	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35
7	51	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	35.7
8	99	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	69.3
9	1500	35	0.7	0.6	0.5	0.4	0.3	0.2	0.1	450
Sheet6

Cell Formulas
Range	Formula
P5:P9	P5	=INDEX(H5:O5,XMATCH(G5,$H$4:$O$4,-1))*(IF(G5<50,1,G5))

Sorry Fluff, just one more question if I have a 0 or blank in the KG column how do I return a - in the total column as will be adding a sum total eventually when I sort the rest of the sheet out

 

[Fluff]

Why do you need an - instead of 0? As a 0 will not affect the result of a sum.

 

[ianharper68]

Why do you need an - instead of 0? As a 0 will not affect the result of a sum.

- just looks neater but a 0 will be fine

 

[Fluff]

You can always use a custom cell format to display a - rather than 0

 

[ianharper68]

You can always use a custom cell format to display a - rather than 0

What I am saying is the formula you gave me doesn't give a 0 if kgs are 0 it shows 35 .....

 

[Fluff]

Ok, how about

=IF(G5=0,"-",INDEX(H5:O5,XMATCH(G5,$H$4:$O$4,-1))*(IF(G5<50,1,G5)))