Excel Mileage Tracking
August 6, 2008 1:47 PM Subscribe
Help me set up an Excel spreadsheet to track my gas mileage on my car.
I have about a year and a half of mileage gas mileage data for my car (whenever I filled up, I noted the amount of gas and the odometer reading). I would like to finally put it in Excel and take a look at it. My only concern is that there were a handful of times where I filled up my car and I forgot to fill out my sheet, so it may look something like this:
6/8/08 --- 54789 miles --- 8 gallons
??????
6/18/08 --- 55000 miles --- 3 gallons
I know that if I just plug that data into Excel, it will tell me that one week in June I had a Super Fillup of Extreme Efficiency Gas, and I know that's just not true. I remember from 8th grade math that if I were plotting this by hand, I would just estimate the missing point, and I imagine that there must be a way for Excel to do this for me, but I don't know how.
Any other resources or information on tracking gas mileage or using Excel for similar "life tracking" applications would be very welcome.
I have about a year and a half of mileage gas mileage data for my car (whenever I filled up, I noted the amount of gas and the odometer reading). I would like to finally put it in Excel and take a look at it. My only concern is that there were a handful of times where I filled up my car and I forgot to fill out my sheet, so it may look something like this:
6/8/08 --- 54789 miles --- 8 gallons
??????
6/18/08 --- 55000 miles --- 3 gallons
I know that if I just plug that data into Excel, it will tell me that one week in June I had a Super Fillup of Extreme Efficiency Gas, and I know that's just not true. I remember from 8th grade math that if I were plotting this by hand, I would just estimate the missing point, and I imagine that there must be a way for Excel to do this for me, but I don't know how.
Any other resources or information on tracking gas mileage or using Excel for similar "life tracking" applications would be very welcome.
I did one of those for along time, until the odometer broke.
Columns for Date, Miles, Gallons,MPG,Octane, Odometer, $/gallon
The formulas were simple. I was using lotus123 but I'd bet there are templates for this on the MS Excel site.
posted by Agamenticus at 3:03 PM on August 6, 2008
Columns for Date, Miles, Gallons,MPG,Octane, Odometer, $/gallon
The formulas were simple. I was using lotus123 but I'd bet there are templates for this on the MS Excel site.
posted by Agamenticus at 3:03 PM on August 6, 2008
One approach to excluding the bogus data would be to set a threshold that would indicate bogus data and use it in a column with an if statement to output either the row's legitimate value or a blank.
Imagine if A is date, B is Odometer reading, C is # of gallons:
D3 (mileage) would be =(B3-B2)/C3 [miles traveled since last fill up divided by # gallons added]
E3 could be =if(D3>40,D2,D3) [if mileage is greater than 40 (or some other number of your choosing), then report previous mileage, otherwise report mileage].
Alternatively, to make the data look smoother, you could replace the D2 in the last formula with something like: average(D2,D4)
Ideally, I would have replaced the D2 with a "" (two quote marks) which would leave the cell blank, however excel seems to regard that as a zero instead of a blank.
You would then do a drag-fill of your formulas all the way down.
If you were to plot column E it will leave the blank cells as gaps in your plot.
posted by i love cheese at 3:57 PM on August 6, 2008
Imagine if A is date, B is Odometer reading, C is # of gallons:
D3 (mileage) would be =(B3-B2)/C3 [miles traveled since last fill up divided by # gallons added]
E3 could be =if(D3>40,D2,D3) [if mileage is greater than 40 (or some other number of your choosing), then report previous mileage, otherwise report mileage].
Alternatively, to make the data look smoother, you could replace the D2 in the last formula with something like: average(D2,D4)
Ideally, I would have replaced the D2 with a "" (two quote marks) which would leave the cell blank, however excel seems to regard that as a zero instead of a blank.
You would then do a drag-fill of your formulas all the way down.
If you were to plot column E it will leave the blank cells as gaps in your plot.
posted by i love cheese at 3:57 PM on August 6, 2008
This isn't exactly what you are asking for, but I have been using the site My Mile Marker for about a year. It lets you enter the data at the web site, or send a text message with the data to the web site. When you enter the data on the web site, there is a box to check that says 'my last fillup wasn't (on the last date data was entered)'. When you do this, it skips the gas mileage calculation using that data. It's also got pretty charts and graphs that track your miles per hour and your total miles driven.
posted by LightMayo at 5:19 PM on August 6, 2008
posted by LightMayo at 5:19 PM on August 6, 2008
Take row 2 sub tract from row 1 didve the difference
posted by BoldStepDesign at 10:27 PM on August 6, 2008
posted by BoldStepDesign at 10:27 PM on August 6, 2008
This thread is closed to new comments.
As far as I know, there's no way for Excel to guess what value should have been there except for making a formula for the cell with the missing data that would take into account the average of the values on both sides of the missing value.
As for the second part of the question, I use Excel to keep track of my grades/GPA (check over in the Projects if you're interested). You could use it to track inventory, money, really almost anything.
posted by theichibun at 2:29 PM on August 6, 2008