Calculate Rate based on Country Zones

[ali_plus]

Hi,

I am trying to create a simple chart for my team mates to calculate import rates based on country zone and weight increment. I have two tables like this:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Zone[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]UK[/TD]
[TD="align: center"]UK[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Canada[/TD]
[TD="align: center"]CN[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]France[/TD]
[TD="align: center"]FR[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]

Increment​

[/TD]
[TD]

From​

[/TD]
[TD]

To​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.0[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]1.0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]1.1[/TD]
[TD="align: center"]2.0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]2.1[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]2.6[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[/TR]
</tbody>[/TABLE]

Now I have a calculation sheet which is very simple. My team will select the country from drop down list and enter the weight and all is calculated automatically.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Zone[/TD]
[TD="align: center"]Chargeable Weight[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]charges[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]France[/TD]
[TD="align: center"]SUMIF( I already made it)
SUMIF(A2:A5,J2,C2:C5)[/TD]
[TD="align: center"]The Team will enter the Weight here[/TD]
[TD="align: center"]NEED THIS RATE BASED ON WEIGHT AND ZONE[/TD]
[TD="align: center"]=D2 * C2[/TD]
[/TR]
</tbody>[/TABLE]


Please help me create the formula in cell M2 as my mind is currently our of ideas. I know it must be something simple but workload and mind not working creatively.

Thanks.

 

[ali_plus]

I can also use Vlookup in K2. Instead I will change it to vlookup cause it will be more accurate. Plus I am thinking in M2 there will be a combination of nested IF's and OR e.g. =IF(OR(xxx something....

 

[James006]

Hello,

How are the rates ... linked to the Zones ?

Where are the Zones in your second table ?

 

[ali_plus]

Hi James, it is written as 1, 2 & 3 in cells G1, H1 & I1

 

[James006]

Hi James, it is written as 1, 2 & 3 in cells G1, H1 & I1

... OK ... But your first table shows 4 Zones (1,2,3,4) ... !

 

[ali_plus]

Yes I am taking tables as examples. You can consider a new column J1:J6 for Zone 4 sorry I am on phone otherwise I would've edited it.

 

[James006]

With your revised table ...

In cell K2 =MATCH(K2,$A$2:$A$5,0)

In cell N2 you will have =Index($G$1:$J$6,findRow,MATCH(L2,$A$1:$J$1,0))

Regarding how to replace FindRow ... I do not know how to read/use the rows of your Table 2

Hope this will help

 

[James006]

If my understanding is correct ...

With the weight located in cell M2, you should replace findRow by following formula:

MATCH(M2,$E$1:$E$6,1)

or your final formula in cell N2

=INDEX($E$1:$J$6,MATCH(M2,$E$1:$E$6,1),MATCH(L2,$E$1:$J$1,0))

HTH

 

[ali_plus]

If my understanding is correct ...

With the weight located in cell M2, you should replace findRow by following formula:

MATCH(M2,$E$1:$E$6,1)

or your final formula in cell N2

=INDEX($E$1:$J$6,MATCH(M2,$E$1:$E$6,1),MATCH(L2,$E$1:$J$1,0))

HTH

James Thank you. the formula is working but there is one problem. if the value is greater than E6 than the formula returns error #REF. I think i should calculate as well if the value are greater than E6 than it should automatically take values from last row. Anyone with a fix? if you want I can attach the source file as well

 

[ali_plus]

Bump for anyone who can help