Freight charges based on zip

Hi there,

Please see the attached file. An example how the shipping rate should be calculated:


300kg shipment to zip 999 = Basic charge (in this case 20)+(300 kg* $5.88)= $1781;

Please note that if the final amount is less than <<Min Charge>>, the customer will be charged with this minimum charge:

10kg shipment to 3455 = 3 + (10*0.83) = $11.3, 11.3 < 70 (min. charge, so the final price is $70)

Thank you for your help!

Try this workbook.
How are your zip areas broken down? Is it by the firstpart, or the whole code?

Hope this helps

This gets a bit more complex
Try the attached workbook

The formula in F7 is an array

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter NOT just Enter.

You will have to build better Validation Lists than I have in this demo.

Hope this helps

That become very big there are some 42,000 US zip-codes excluding Military Codes.

How many States are you realistically needing to handle, and what are these States?

If you post a separate worksheet with your "Zip Code" & "State" codes Columns only, taken from your real workbook, I'll have a look at it a bit further.

Are you really needing USA zip-codes or are you actually working with Bulgarian Codes?

This could be further inproved, but I'm afraid you might give up, having waited a while for a reply.

I have left some workings in this workbook so you can check the results yourself.
In Sheet "Get Zip Rate" you need only keep Rows 4 & 5
In Sheet "Lookups" the columns with White text in a Black cell are required.

1/. Sheet "Lookups" are the unique codes from your list of Australian Post Codes
Column A is the Numeric List and Column B is that list converted to text to allow the method I have used to work.

This is where the Dynamic Lists-within-Lists are calculated, see the names manager.

2/. Sheet "Get Zip Rate" extracts the Code Groups from "Australia shipping rates" using Dynamic Data Validation.
This creates 2683 sub-lists from one column of data containing 18907 rows!

This method might possibly be applied to Sheet "Lookups" with hindsight, but try it out as it stands

Sheet "Get Zip Rate" > "Zip Group" is the first digit (as text) in the codes, "0" is for codes below 1000.
This Calls the Drop-Downs in "Zip Code" and they in turn call the "Suburb" Drop-Downs.

The Array in E5 has been modified to use the Named Ranges.

Hope this helps.

Happy to have helped.

here is a non array formula

You could just add *1.5*1.0885 to the end of the formula, then confirm with Ctrl +Shift+Enter.

However I would be tempted to add the markup and tax to the table then sum the result.

Hope this helps