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!!)
Tariff assistance please (2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Tariff assistance please (2003 SP3)
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Tariff assistance please (2003 SP3)
Your screenshot is missing the 11-50 kilo rate.
Could you attach a stripped down sample workbook?
Could you attach a stripped down sample workbook?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Tariff assistance please (2003 SP3)
Hi HansHansV wrote:Your screenshot is missing the 11-50 kilo rate.
Could you attach a stripped down sample workbook?
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,
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Tariff assistance please (2003 SP3)
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Tariff assistance please (2003 SP3)
Hi HansHansV wrote: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.
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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Tariff assistance please (2003 SP3)
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)
=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)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Tariff assistance please (2003 SP3) - Closed
Hi HansHansV wrote: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)
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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin