Tariff assistance please (2003 SP3)

[steveh]

Good morning

I have a tariff with a base rate that includes 10 kilos, I then have the following weight breaks 10-50, 51-100, 101-200, 201+ the way that the boss wants it to work is for example the client had 250 kilos the rate would be the base rate +40 kilos of the 10-50 rate, +49 kilos of the 51-100 rate, +100 of the 101 - 200 rate and then anything over that at the +201 kilo rate.

I have constructed an example workbook with a combo that selects a country and then the structure is as per the jpg below. I have tried my best but I am getting tied in knots when it gets to the part of taking into account all of the different weight breaks, this is where I have got to so far

=IF(AND(C5<=10,C3=A16),B16,IF(AND(C3=A16,C5>10,C5<=50,B16+(C5-10)*D16,IF(AND(C3=A16,C5>10,C5<=100),B16+(C5-10

But I can't think then how I can say that the next weight break needs to be calculated etc. (I don't even know to be honest if the part I have down above works yet!!)

[HansV]

Your screenshot is missing the 11-50 kilo rate.
Could you attach a stripped down sample workbook?

[steveh]

Your screenshot is missing the 11-50 kilo rate.
Could you attach a stripped down sample workbook?

Hi Hans

Sorry, I tried to hide a column because it would have been a red herring but in doing so I hid 2 columns without realising, the eventual aim is to be able to select the shipment type Non regulated or UN and the return the rate but I thought I would try and get one part working first!!

Please find attached an example workbook,

[HansV]

See the attached version. I have added a range with cumulative tariffs, and used a validation dropdown to indicate whether the shipment is UN regulated, instead of using a separate column.

TariffExample.xls

[steveh]

See the attached version. I have added a range with cumulative tariffs, and used a validation dropdown to indicate whether the shipment is UN regulated, instead of using a separate column.

TariffExample.xls

Hi Hans

That is awesome, than you very much for the additional bits. I have just been advised that there are actually 3 different UN regulated charges £55, £60 or £65 depending on region so I have entered a new column L16:L82 which reflects the charge by Country with the intention of having the value calculated in J1 my using match or vLookup for exaple =MATCH(C3,L16:L82) but all I get is a #N/A, the same with VLOOKUP and I have tried a combination of MATCH and INDEX -=MATCH(C3,A16:A82,INDEX(L16:L82)), I have tried to Google but can't seem to phrase the question right to get an answer. Can you help please?

Also I have been asked to do some tariffs where there may be 3 different shipment types, I will try and adapt what I have here when it is working but I may need to call for more help if you don't mind.

[HansV]

The formula becomes

=IF(C5<$B$15,INDEX($B$16:$B$82,MATCH(C3,$A$16:$A$82,0)),INDEX($H$16:$K$82,MATCH(C3,$A$16:$A$82,0),MATCH(C5,$B$15:$E$15))+(C5-INDEX($B$15:$E$15,MATCH(C5,$B$15:$E$15)))*INDEX($C$16:$F$82,MATCH(C3,$A$16:$A$82,0),MATCH(C5,$B$15:$E$15)))+IF(C7="Yes",INDEX($L$16:$L$82,MATCH(C3,$A$16:$A$82,0)),0)

[steveh]

The formula becomes

=IF(C5<$B$15,INDEX($B$16:$B$82,MATCH(C3,$A$16:$A$82,0)),INDEX($H$16:$K$82,MATCH(C3,$A$16:$A$82,0),MATCH(C5,$B$15:$E$15))+(C5-INDEX($B$15:$E$15,MATCH(C5,$B$15:$E$15)))*INDEX($C$16:$F$82,MATCH(C3,$A$16:$A$82,0),MATCH(C5,$B$15:$E$15)))+IF(C7="Yes",INDEX($L$16:$L$82,MATCH(C3,$A$16:$A$82,0)),0)

Hi Hans

Thank you very much for that, I was suprised how close I go with =MATCH(C3,A16:A82,INDEX(L16:L82)), especially with my pea brain I will have a read up on what functions take precedence in a formula, i before e etc.