Freight calculator based on zipcode, weight and type

Hello,

I found this sheet on this forum and I have tried to modify it into my needs. However, Im missing 'something'.

I need it to calculate a price based on:

zipcode within a prespecified range (works)
type of delivery KANT or IND (works)
find the price based on weight within a range(not working. It doesnt take the weight into account at the moment???)

I then need to hide all the calculations so its easy to use and look at.

Freight calculator zipcode weight and type.xlsx

I hope one of you superexcelgeeks can help me out as I have no idea what Im doing....

Hi,

Try this formula instead in Q4

Formula:

Please Login or Register  to view this content.

abousetta

Have a look at the attachment, the formula seems to work fine on the example.

Are you getting an error message or the wrong results? Are you modifying the example to your own needs or just testing on the uploaded copy?

abousetta

Try this workbook

Hi Verox

Have a look at the attached. i am using the INDIRECT. If that is a problem the formula can be adapted. It seems to work!

So what would your expected price be for the following!

Type: ind
zipcode: 3702
weight: 325

Try this amended workook, note the layout of columns B:C.

Kevin!

ahhhhhhhhh....... I made a mistake :P..... you are correct!

however!

one thing: if I enter the weight at the exact MAX for a column. it takes the price for the next tier instead of keeping the lower price. ie.(its not supposed to change to the higher price unless you enter 251 instead of 250)

I hope you understand?
(this also means that I cant enter a weight under 25 kg.... then it gives an error.... instead of the lowest price..


we are almost there! very nice work

Hi Verox

Try the following, I will have another look later on as the rugby has started.

I'm having a bad hair day ... ...
Try this workbook

[EDIT]
The Postcodes failed, one more go before I totally loose all credibilty!!!

Are you using Excel 2003, or a later version?

Originally Posted by Verox

Thanks all of you!

...
Marcol: look above :P Kevin nailed it! but thanks a bunch!

Try with weights <= to 25 and greater than 350, you still need some error handling.

Hi Verox

See the attached file, I have changed the formula to a straight INDEX & MATCH. I have used a helper row (Row 9) for the match. It could be adapted for the Weight column headers if required, but I think this is a simpler solution. have a look and see how you get on!

Hi Verox

No problem, we got there in the end! Thank you for the feed back.