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

  • 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
    1. =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.

  • 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
    1. =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 "Thumbs Up" 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 "Thumbs Up" 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 ...


    Please let me have your comments ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" 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 "Thumbs Up" 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 "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad to hear the formula is now fine :)


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


    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 "Thumbs Up" 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 ...:evil::evil::evil: ... 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 "Thumbs Up" icon, below, in the bottom right corner:)