Forum Discussion
Freight Cost Calc. based on Transport and Toll Cost with some criteria
Excel 2016, OS Windows
Hello all,
I am trying to calc. the Total Freight from two different sheet with different data.
- 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.
- 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.
You switched the row_number and column_number arguments of all INDEX functions.
And most of your numeric data were actually text values, making it difficult to work with them.
See the attached version; I have corrected all the errors I could find, but please check carefully,
4 Replies
You switched the row_number and column_number arguments of all INDEX functions.
And most of your numeric data were actually text values, making it difficult to work with them.
See the attached version; I have corrected all the errors I could find, but please check carefully,
HansVogelaarthank you once more for the help. Everything is working fine except for two things:
1. If Palette is selected, I want to enforce that minimum weight applicable is 150 KG. If Palette is not selected, normal operation applies.
2. When the weight of 600 KG and above is selected, it brings up the '#REF!' message; below 600 KG works fine.
See the file again.
Kind regards,
Foday.
I had missed one of the switched argument pairs.
I have changed the data validation for the weight for the transport costs - you can now only select the weights appropriate for Not Palette/Palette.
- Many thanks Hans, this helps a lot.
