Excel fuel cost formula?
 

I’m looking for the excel formula to calculate the cost of fuel for a trip.

I know it exists but I can’t find it. It needs to calculate the cost of fuel used for a particular leg or flight when the trip used a combination of fuel from multiple uploads at different prices.

Example: We just completed a trip that used 500 gallons of fuel. Prior to the trip we uploaded 300 gallons at $5/gal. on top of 400 gallons that were still in the plane from the fueling before that one at $6/gal. Therefore, the 500 gallon trip we just made used 300 gallons of fuel at $5/gal. and 200 gallons that were still in the plane prior to the most recent fueling at $6/gal. The formula will also have to account for the cost of any fuel that may have still been in the plane from maybe up to 3 previous fueling. An average isn’t good enough, it needs to calculate the exact cost of fuel used.

Anyone know where to find the formula?

I don't know of an Excel formula but you could build one if you want. What you are seeking is a weighted average of fuel cost since the fuel molecules purchased at different costs don't stay separated from each other and they don't burn in order that they were put in the tank.

If you had $1,500 of fuel (300 gal x $5/gal) and then added it to $2,400 of fuel (400 gal x $6/gal), then the average cost of the fuel in your tank is $5.57/gal. ($3,900/700).

The cost of your 500 gal trip is $2,786 (500 gal x $5.57/gal).

Hey thanks a lot...it only took me an hour to get the spread sheet to work but it worked....thank a bunch!!!

You said an average isn't good enough. KME9418 pointed out that the fuel molecules mix but that doesn't mean your formula and method for expense tracking can't assume that they stay separate.

You could treat the situation like tracking investment shares. First in/First out. Since you don't want to use an average, I suggest you consider the fuel loads will burn in the order they are bought.

Tracking it the opposite way by assuming any fuel added to what's there burns first or using an average is going to make the situation much more difficult to track over time. Both of those options leave the potential for fuel purchased months ago to still be in the formula, so I wouldn't recommend those methods.

Using your example:
400 gal @ 6$/gal were in the aircraft = $2400
300 gal @ 5$/gal were added = $1500

On your 500 gal trip, you used all of the fuel purchased at $2400 and $500 worth of the most recently purchase load (100 gal).
Total cost of the trip = $2900

You now have only the 200 gal valued at $1000 remaining to track on any future trip along with any additional fuel added.

I wrote an excel spreadsheet that does this but I can't seem to post a screen shot to use as a reference. PM me if you want me to email it.