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:
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.