Tracking MPG online using Google Docs

This post was published 3 years 11 months 13 days ago. Therefore, it may well be out of date. Do not reply on the contents of this post being accurate.

Are you a trucker, cabbie, sales rep or, anyone who wants to easily track their MPG? Read on; this tip is useful if you want to use your web-enabled mobile device (smart phone, iPhone, PDA, etc.) to do it.

Up to now I’ve tracked my MPG in an Excel spreadsheet. Because I seldom have my laptop booted-up when I fill up I always get a receipt when I pay for fuel and, I write on it the mileage showing on the odometer. Then, when back at my computer I enter the details into the spready.

I’ve thought of several ways to semi-automate and/or streamline this process, however, now I don’t have to. Why? Because macosxhints.com has the answer and it’s obvious (now that I’ve had it pointed out to me) …

Using Google Docs create an online spreadsheet and, here’s the really clever part, use the Share function and share it with yourself. You can then generate an online form which has a corresponding URL which you can have emailed to you to store in your inbox or, of course, store as a bookmark. Then, using any web-enabled mobile device navigate to the URL (or find the email), enter the mileage and quantity of fuel purchased in the form you previously generated then, click submit and a new row will be entered into your Google Spreadsheet.

Very neat!

The only data you need to capture when setting up the form are the odometer reading and the litres of fuel purchased (as I live in the UK, buying fuel in litres and measuring fuel efficiency in miles per gallon are two of many idiosyncrasies we posses). The date is captured automatically and the other cells are calculated (miles traveled, litres to gallons and, finally, MPG per fill-up and overall MPG).

One tip; it would seem that Google Spreadsheet selects the next empty row (i.e. one without formulae or other entries). This is no good if you want to use calculations. So, I import (upload) the source data into one worksheet then manipulate it in another (i.e. copy over the uploaded data then you can do all the MPG calculations).

Of course, other solutions exist but I like this because it’s so simple to set up and, it works.

Here’s a summary of my spreadsheet headings and formulae:

• cell C2 is the odometer reading when I picked up my car with a full tank
• cell B3 contains the formula =C2 and is copied down the column
• from here on entries in column C and E are entered via the Google form
• cell D3 could contain the formula =C3-B3 and copied down the column, however, by using =IF(C3=0, ,C3-B3) will ensure zeros are not displayed further down the sheet if a cell is empty
• similarly the formula to calculate the U.K. gallons per litre is =IF(E3=0, ,E3*0.22)
• the formula to calculate MPG is =IF(D3=0, ,D3/F3)
• finally, you could also add a cell to the worksheet that calculates your average MPG as each row is added; in this case the formula would be =AVERAGE(G:G)

So, each time you fill up you can enter the quantity and cost of fuel you purchased in to the online form and hey-presto your Google Spreadsheet will track your MPG.

For me, being conscious of my MPG has helped reduce my fuel costs.

Here’s a copy of my spreadsheet to date:

NB. This method only works (I think) if you fill up completely each time you visit the filling station because you need to know how many gallons of fuel it took to complete the number of miles since the last fill-up. I’m sure someone better versed in maths could put that more eloquently.

Can you think of other examples where you might want to collect spreadsheet data on the move in this way? Let me know via a comment.