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

 

[ianharper68]

Ok, how about

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

Brilliant ... THANK YOU SO MUCH

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ianharper68]

Hi everyone ... I am still working on this one but have come across a problem for some reason this forula doesn't return a result? Any help would be appreciated

=IF(B14<>0,"-",INDEX(C14:E14,XMATCH(B14,$C$13:$E$13,-1))*(IF(B14<2,1,B14)))

 

[Fluff]

Shouldn't that be B14=0 rather than B14<>0