Freight calculator

[someko]

Hi guys,

It would great if you help me with the freight calculator I am trying to prepare.
The table in the attached file will be hidden. My client should choose country and zip code (note that I will operate with around 10 countries) and based on the purchased item's weight [50 or 100 kg - I need kind of VLOOKUP formula here, because the weight can vary from 50 to 10 000 (more columns are coming)], I have to calculate what would the charges be.

Thank you so much!

[Chemistification]

Hi Smokeo,

Its probably not the cleanest way of doing it but as you are going to hiding your table anyway i figured it should be ok. Let me know what you think. The cell with the formula in is F16. Customer enters details in b16 to e16

Chemist

[NBVC]

See attached for a suggestion...

The inputs and results can be placed on another sheet, if desired, just make sure to reference the table sheet.

I added a helper column to determine which item falls into the Country/Zip Code chosen by the user....

=IF(A3="","",AND(A3=$J$2,$K$2>=B3,$K$2<=C3)) copied down as far as you want...

This column can be hidden if desired.

Then the formula in J4 determines cost based on next highest weight if not equal....

=INDEX($D:$E,MATCH(TRUE,F:F,0),MATCH(TRUE,$D$2:$E$2>=L2))

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER in order to work

[Chemistification]

See attached for a suggestion...

The inputs and results can be placed on another sheet, if desired, just make sure to reference the table sheet.

I added a helper column to determine which item falls into the Country/Zip Code chosen by the user....

=IF(A3="","",AND(A3=$J$2,$K$2>=B3,$K$2<=C3)) copied down as far as you want...

This column can be hidden if desired.

Then the formula in J4 determines cost based on next highest weight if not equal....

=INDEX($D:$E,MATCH(TRUE,F:F,0),MATCH(TRUE,$D$2:$E$2>=L2))

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER in order to work

Nice. Reading your post think i got mine all wrong..... next time tho!

[NBVC]

I guess you could also loose the helper column and just use 1 array formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.. but with array formulas it is better to define a range rather than use whole column references because of efficiency issues.... and that should be true for my previous example too..

[someko]

Wow, NBVC, man, thaks a lot!

[someko]

Dear NBVC,

Would you give me one last hint?

Would you please check the attached file and tell me where I go wrong? "IF" formula is ok, but I have difficulties with =INDEX($D:$E,MATCH(TRUE,F:F,0),MATCH(TRUE,$D$2:$E$2>=L2))?

Thank you

[NBVC]

Your rows 1 and 2 are merged.. so you should be referencing D1:AM1 and also a tweek in the formuala so try this instead:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

[someko]

Yes, it works. Thank you for your great help!