# Freight Cost Calc. based on Transport and Toll Cost with some criteria

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Hello all,

I am trying to calc. the Total Freight from two different sheet with different data.

1. Transport data sheet - contains the item type, zip-codes, weights and prices. Extra condition here is, if the item type is 'Palette', then the minimum weight applicable should be 150kg. And the price is calculated based on the zip-codes and the weights. here is the code I used for this:

Code
``=IFERROR(IF(B3="Not Palette",INDEX('Transport Data'!C3:CS10,MATCH(B4,'Transport Data'!C2:CS2,FALSE),MATCH(B6,'Transport Data'!B3:B10,FALSE)),INDEX('Transport Data'!C12:CS54,MATCH(B4,'Transport Data'!C2:CS2,FALSE),MATCH(B6,'Transport Data'!B12:B54,FALSE))),"Palette Weight should be ≥150kg")``

2. Toll data sheet: contains distances, weights and prices. Here the toll cost is determined from the weight and distance. there are two extra condition to this:

a) if the shipment type is 'Express' and weight >100kg, add 15% to total toll cost. If it is <100kg, just add 15€ to toll cost.

b) if the goods type is 'Hazardous' add extra 19€ to toll charge.

Total freight cost now should be transport cost + toll cost.

See the file attached.

Kind regards,

Foday.

## Files

• Hello and Welcome to the Forum

For a start ... you could test the following formula in cell D7 to get the basic reference price .... before all extras ...

Code
``=INDEX('Toll Data'!\$A\$1:\$N\$44,MATCH(D6,'Toll Data'!\$C:\$C,0),MATCH(D5,'Toll Data'!\$4:\$4,0))``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Just to make sure my understanding is correct.... regarding Extras ...

Targospeed is the same as Not Express Shipment

and it is either Adding 15% ...or Adding €15 ... Is that right ...????

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Dear Carim,

Targospeed is the same as Express Shipment. It is adding 15% if 'Express Shipment' is selected and 'weight' >100kg; or just adding 15 € f it is 'Express Shipment' and 'weight' <100kg.

thank you.

• Hello again,

Could you test the formula for the " Toll Cost " ... in cell D7 ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Dear Carim,

many thanks for the effort an help.

the formula seems to work perfectly when Express Shipment is selected in all situations. But when Express Shipment is not selected, it does not bring up any value. And when Express Shipment is not selected but Hazardous is selected, it only brings up the charges for Hazardous an the basic cost is not included.

Many thanks for the effort again.

• Thanks a lot for your tests ....

You are right !!!

I completely forgot to deal with the " Not Express Shipment " .... Sorry

Will review the formula ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• OK ... Attached is the revised formula ...

Thanks again for testing as much as possible to validate ... or to spot mistakes ...

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Once you have tested the Formula ... feel free to share your comments

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Thank you Carim. It is fine and works perfectly now.

Thanks a lot.

Foday.

• Glad to hear the formula is now fine

Thanks a lot for your Thanks ..AND for the Like

Question : have you corrected your Formula in cell B7 for the Transport Cost ... or not yet ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Just in case ... you would also needed the Transport Cost formula ...

By the way ... please NEVER use MERGED CELLS ... ... since they only created problems ...( see examples in cells A2 and C2 )

Attached is your revised test file ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Once you have tested the Formula ... feel free to share your comments

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• You are welcome

Thanks for your Thanks ...AND for the Like

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!