Calculating Cost of Cargo from Pricing Criteria

[RoRoQuest]

Hi there!

I work in logistics and we have tiered pricing dependent on a number of factors. For example, if a car is being imported and is equal to or smaller than 2 metres tall we charge $50/Tonne, however if the car is greater than 2 metres tall we charge $100/Tonne. We also change the pricing quarterly.

I am trying to make the spreadsheet I use less formula heavy so other users can update the spreadsheet too. I am having difficulty finding a way to get excel to recognise the bucket a piece of cargo fits into, and assign a cost to it though. I am guessing some combo of IF and INDEX functions are required, then users would be able to just update the tables on the right of the worksheet to yield new pricing data. I have attached like what I envision the spreadsheet looking like.