gas mileage spreadsheet

[john112deere]

I'm working on putting together a new spreadsheet for tracking my fuel consumption (and probably maintenance, as a separate sheet within the same file).

Being an engineer with too much time on my hands...I'd like the sheet set up to generate charts plotting miles driven vs. time, gas mileage vs. total miles, etc. as I input my fill-ups.

Making Excel do stuff like that is a big part of my job, so the spreadsheet aspect of what I want to do is no big deal.

What I'm wondering, though, is this- when I plot "Gas Mileage vs. Odometer Reading" (to track changes in fuel mileage over time), should I use some sort of weighted average for the gas mileage? Tank-by-tank mileage fluctuates due to more factors than I care to list; if I'm trying to track longer-term trends, I'm thinking I should maybe try to normalize the data points a little to smooth the line. Maybe something like [(previous tank MPG)+2*(tank MPG)+(next tank MPG)]/4 ... though I'm wondering if maybe an exponential formula might work better?

Anybody understand what I'm trying to do? Know what the best way to do it is?

 

[polarbear]

How about generating some data points first to see what the variation looks like. Then you can figure out how to smooth out the fluctuations.

 

[john112deere]

I've got a similar spreadsheet and chart (though not especially pretty) for the '98 Escort I traded on this truck. Covers maybe the first 6000 miles I had the thing, and since I don't have the car anymore, I don't really feel like updating it with the remaining miles.

I've played with it some, but I'm just not sure how much I want to smooth it out before it starts to lose meaning.

 

[john112deere]

Here's the plot of the Escort numbers.

The blue line is just straight MPG, tank by tank.

The red line is [(previous tank)+(tank)+(next tank)]/3.

The green line is [(previous tank)+2(tank)+(next tank)]/4.


Notice that green line fluctuates more than the red, but not very well in sync with the blue one...I'm really not sure which way to go with this. (It is, of course, completely insane to spend much time at it, anyway...but like I said, I'm an engineer with too much time on my hands.)