Calculations using Postcode

I

ianharper68

New Member
Joined
May 16, 2024
Messages
26
Office Version
  1. 2021
Platform
  1. MacOS
Following on from my thread Shipping calculations for weight based sheet

My customer has now asked me to calculate rates based on weight of goods from specific postcodes.

Please see below, if somebody could help I would highly appreciate it :)

Sample Calcs.xlsx
ABCDEFGHIJKLMN
1
2
3
4DescriptionCollection PostcodeCollieLengthWidthHeightLDMCBMNWGWADRCollection FeeCollection Costs
5Tools7844 NZ NL11201001051.260.43125.00156.00NoNote 1Note 2
6
7
8Note 1 - if ADR = NO= Collection Fee (C10-C31)
9
10Note 1 - if ADR = YES= Collection Fee (C40-C61)
11
12Note 2 - if ADR = NO This must equal - Collection postcode then GW (K3) * Cost per weight (c10 - h34)
13as the above example would 156kg from 7844 NZ NL would €127
14
15Note 2 - if ADR = YES This must equal - Collection postcode then GW (K3) * Cost per weight (c40 - h61)
16as the above example would 156kg from 7844 NZ NL would €184.15
17
18
19Cost per collection by weight (NON ADR)
20Collection PostcodeCollection Fee90017503000350045005000
212380 AT € 50.00€ 543.00€ 608.00€ 627.00€ 713.00€ 833.00€ 1,305.00
222610 BE€ 50.00€ 182.00€ 244.00€ 258.00€ 304.00€ 355.00€ 490.00
231032 CH€ 50.00€ 398.00€ 465.00€ 480.00€ 529.00€ 575.00€ 837.00
2422047 DE€ 50.00€ 329.00€ 370.00€ 384.00€ 413.00€ 440.00€ 579.00
2522934 DE€ 50.00€ 329.00€ 370.00€ 384.00€ 413.00€ 440.00€ 579.00
262650 DK€ 50.00€ 338.00€ 428.00€ 488.00€ 603.00€ 693.00€ 898.00
279401 DK€ 50.00€ 347.00€ 422.00€ 498.00€ 629.00€ 712.00€ 939.00
2834410 FR € 50.00€ 349.00€ 473.00€ 514.00€ 573.00€ 632.00€ 822.00
2944482 FR€ 50.00€ 302.00€ 324.00€ 332.00€ 377.00€ 420.00€ 595.00
3045801 FR€ 50.00€ 255.00€ 270.00€ 275.00€ 304.00€ 333.00€ 424.00
316728 HU€ 50.00€ 600.00€ 703.00€ 815.00€ 930.00€ 1,024.00€ 1,582.00
3220094 IT€ 50.00€ 385.00€ 502.00€ 578.00€ 742.00€ 810.00€ 1,210.00
3345030 IT€ 50.00€ 438.00€ 580.00€ 668.00€ 857.00€ 982.00€ 1,429.00
342215 TC NL€ 50.00€ 117.00€ 163.00€ 180.00€ 218.00€ 237.00€ 314.00
354758 NL€ 50.00€ 117.00€ 163.00€ 180.00€ 218.00€ 237.00€ 314.00
366716 AE NL€ 50.00€ 122.00€ 173.00€ 183.00€ 219.00€ 238.00€ 359.00
377844 NZ NL€ 50.00€ 127.00€ 174.00€ 200.00€ 233.00€ 265.00€ 383.00
388262 CG NL€ 50.00€ 124.00€ 173.00€ 194.00€ 229.00€ 255.00€ 372.00
3981-574 PL€ 50.00€ 397.00€ 499.00€ 548.00€ 669.00€ 732.00€ 1,107.00
4083-000 PL€ 50.00€ 397.00€ 499.00€ 548.00€ 669.00€ 732.00€ 1,107.00
4185-862 PL€ 50.00€ 369.00€ 478.00€ 514.00€ 634.00€ 702.00€ 1,017.00
4208970 SP€ 50.00€ 342.00€ 525.00€ 602.00€ 669.00€ 834.00€ 1,355.00
43
44Cost per collection by weight (ADR)
45Collection PostcodeCollection Fee90017503000350045005000
462380 AT € 75.00€ 787.35€ 881.60€ 909.15€ 1,033.85€ 1,207.85€ 1,892.25
472610 BE€ 75.00€ 263.90€ 353.80€ 374.10€ 440.80€ 514.75€ 710.50
481032 CH€ 75.00€ 577.10€ 674.25€ 696.00€ 767.05€ 833.75€ 1,213.65
4922047 DE€ 75.00€ 477.05€ 536.50€ 556.80€ 598.85€ 638.00€ 839.55
5022934 DE€ 75.00€ 477.05€ 536.50€ 556.80€ 598.85€ 638.00€ 839.55
512650 DK€ 75.00€ 490.10€ 620.60€ 707.60€ 874.35€ 1,004.85€ 1,302.10
529401 DK€ 75.00€ 503.15€ 611.90€ 722.10€ 912.05€ 1,032.40€ 1,361.55
5334410 FR € 75.00€ 506.05€ 685.85€ 745.30€ 830.85€ 916.40€ 1,191.90
5444482 FR€ 75.00€ 437.90€ 469.80€ 481.40€ 546.65€ 609.00€ 862.75
5545801 FR€ 75.00€ 369.75€ 391.50€ 398.75€ 440.80€ 482.85€ 614.80
566728 HU€ 75.00€ 870.00€ 1,019.35€ 1,181.75€ 1,348.50€ 1,484.80€ 2,293.90
5720094 IT€ 75.00€ 558.25€ 727.90€ 838.10€ 1,075.90€ 1,174.50€ 1,754.50
5845030 IT€ 75.00€ 635.10€ 841.00€ 968.60€ 1,242.65€ 1,423.90€ 2,072.05
592215 TC NL€ 75.00€ 169.65€ 236.35€ 261.00€ 316.10€ 343.65€ 455.30
604758 NL€ 75.00€ 169.65€ 236.35€ 261.00€ 316.10€ 343.65€ 455.30
616716 AE NL€ 75.00€ 176.90€ 250.85€ 265.35€ 317.55€ 345.10€ 520.55
627844 NZ NL€ 75.00€ 184.15€ 252.30€ 290.00€ 337.85€ 384.25€ 555.35
638262 CG NL€ 75.00€ 179.80€ 250.85€ 281.30€ 332.05€ 369.75€ 539.40
6481-574 PL€ 75.00€ 575.65€ 723.55€ 794.60€ 970.05€ 1,061.40€ 1,605.15
6583-000 PL€ 75.00€ 575.65€ 723.55€ 794.60€ 970.05€ 1,061.40€ 1,605.15
6685-862 PL€ 75.00€ 535.05€ 693.10€ 745.30€ 919.30€ 1,017.90€ 1,474.65
6708970 SP€ 75.00€ 495.90€ 761.25€ 872.90€ 970.05€ 1,209.30€ 1,964.75
Sheet1
Cell Formulas
RangeFormula
H5H5=E5*F5*G5/1000000
I5I5=(F5*G5/2.45)/10000
D46:I67D46=D21*0.45+D21
Cells with Data Validation
CellAllowCriteria
C5List=$B$21:$B$42
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
N5:
Excel Formula: 
=IF(L5="No",INDEX(D21:I42,MATCH(C5,B21:B42,0),XMATCH(K5,D20:I20,1)),INDEX(D46:I67,MATCH(C5,B46:B67,0),XMATCH(K5,D45:I45,1)))
M5:
Excel Formula: 
=VLOOKUP(C5,IF(L5="No",B21:C42,B46:C67),2,0)
 
Upvote 0
Solution
Kaper said:
N5:
Excel Formula: 
=IF(L5="No",INDEX(D21:I42,MATCH(C5,B21:B42,0),XMATCH(K5,D20:I20,1)),INDEX(D46:I67,MATCH(C5,B46:B67,0),XMATCH(K5,D45:I45,1)))
M5:
Excel Formula: 
=VLOOKUP(C5,IF(L5="No",B21:C42,B46:C67),2,0)
Click to expand...
Amazing Thank you :) works perfect :)
 
Upvote 0
Glad to hear that. And thanks for marking answer as solution.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top