Function to Calculate per ton/mile

[stacie]

Hey everyone, this is very specific to my industry, but I'm hoping to get some help on a function for calculating a per ton/mile charge on permits for the trucking company I work for. The regulation reads:

For loads greater than 254,000 lbs, $10.00 - plus $0.50 per ton-mile in excess of 80,000 lbs

On my sheet, you enter in the gross weight of the truck + load, and calculate from a code in a cost estimate cell that looks like this:

Code: Select all

IF(AND(F2>=200;G2>=232001;G2<=254000);1420)

F2 is miles driven, G2 is gross weight, and 1420 is the cost for this particular permit. The previous formulas work on flat rates and not per ton/mile, so it's getting more complicated. 80,000 lbs is the legal limit, but the function I need only applies to loads that are above 254,000 lbs.

Any suggestions on how to calculate this? If I need to give more details, let me know.

Thanks!

[Villeroy]

Please rephrase your question. Give some examples how you would calculate different loads in your head, on paper or with a calculator.