Have you ever wanted to know the exact fuel economy of your car, or needed to keep track of the behavior of your wallet as the seasons change? Perhaps you are curious what time of year you drive the most, or least, and the price of gas at those times. Fuel price trends may fascinate or frustrate you and you want to coordinate your long trip with the low price season, or you want to keep an eye on how your car is performing so you can take it to the shop before it completely breaks down. Or possibly you're like me and simply want to collect a bunch of data and analyze it to make some awesome graphs!

Whatever your purpose, this Instructable details a useful method to accomplish the best results for your data analysis.

Step 1: OCD Much?

I bought my first car in March of 2006, and despite it's boat-like handling and appearance, my 2000 Chevy Impala has been my baby ever since. I wanted to know every single detail about it, and my insurance company had already suggested I keep a log of service dates and whatnot so I'd have a better idea of what my car needed. So I took their advice and put a little notebook in my car, and took the service logging a little further. Actually I took it much, much further.

Since the day I drove my car off the lot, I have recorded every single time I purchased gasoline.

Whenever I'm driving my friends around and I stop for gas, I get some pretty odd looks. "Hey man, can you hand me the black notebook in the glove compartment?" I then scribble down another line of seemingly useless information into the little book and tell my friend to put it back. Invariably, I have to explain what the book is for, and no I'm not obsessive compulsive. I've diligently kept my log for over 4 years, and I just got the motivation to enter it into my computer like I had originally planned. It suffices to say this is the longest experiment I have ever performed.

Logging your data should be something automatic. Keep the notebook in a visible place for a while, then once you're in the habit, you can keep it in the glove box. Dedicate a nice pen to the cause and keep it in the spiral of the notebook.

Step 2: Relevant Information to Log

It's amazing how much information can be derived from just a few bits of data. All you'll ever need to record is:
Date
Odometer Reading
Gallons Purchased
Total Price
Gas Station Type

From this information, combined with Microsoft Excel or some other data entry program, you can find:
Length of Experiment
Total Mileage
Total Cost of Gas
Total Amount of Gas
Total CO2 released
Average Fuel Economy
MPG over time
Cost of fuel per month
Mileage between purchases (Trip)
Cost per gallon
Miles per day
Gas station preference
And probably more

Sure, some of this may seem redundant and worthless, but it's kind of a trip to find out your car has emitted 20 metric tonnes of CO2.

Step 3: Using Microsoft Office Excel

Microsoft Excel is a pretty amazing program. I hadn't learned how to use it before I started entering the data into it earlier this week. It's kind of confusing at first, but once you get the hang of it it's actually very straightforward and almost infinitely useful.

If you have some other kind of data management program, hopefully you know how to create calculated columns and whatnot, because these instructions are for Excel. However, the basic ideas should apply to whatever you're working with.

Step 4: Data Entry

So, you've collected your data, eh? You want to make some awesome graphs? Well, there is an important step you're getting ahead of yourself about.

Data Entry.

Ah the delightful tedium. Oh the wonderful, splendid several hours you may spend entering all the data you've spent forever collecting. You'd better get comfortable with your numpad. I suggest this format:

     A                      B             C               D            E
1 Date                  ODO       Gallons    Cost      Company
2 01/15/2011      53202     6.3            20.00    Chevron

Notice that the label resides in row 1. This won't affect your data. You may want to formally label your columns so you can use their names later for calculations. Click on a letter so that the entire column is selected. Just above column A and to the left of the "f_x" box is the column name field. Enter the names of all your columns here.

Step 5: Creating Calcuated Columns and Autosums

To make sense of your data you need to perform calculations on it. For this project, you will only need two types of calculations. Calculated columns and Autosums. Calculated columns use one or more data sets to calculate the contents of a new column. The new column can be summed or used to make a graph.

Calculated columns are created in Excel by iterating formulas that are written in one cell, then copied to other cells with a simple but difficult to discover mouse maneuver. Say you have a data set in column A and want column B to show your data multiplied by two. To do this, Excel requires a formula in each and every cell in B. Such a set would look like this:

      A    B
1    2   =(A1*2)
2 5 =(A2*2)
3 7   =(A3*2)

Obviously, it would be rather tedious to write the formula for every cell if you had a data set several hundred numbers long. So Excel allows you to iterate a formula like "=(A1*2)" to the cells below it, automatically increasing the number after the A each time. This is done by writing the formula for the first cell by hand, then clicking on the cell so there is a dark, bold line around it with a little black square in the lower right corner. Grab the little black square and drag it downwards, and the formula will automatically iterate. This takes some practice, but becomes very simple.

Autosums are created in a single cell and automatically add together all the contents of a selected column. Select the cell you want to hold your autosum and click on the "Σ" (Greek uppercase letter sigma) in the upper right corner and select "Sum". You can also just type "=SUM" into the cell. Inside the parentheses type the name of the column you want to sum. For example, to find the total amount of gas you've used, type "=SUM(Gallons)".

Step 6: Calculations You Will Need

For those of you who have taken calculus, I have news for you. Tables of data suck. You already knew this of course, since those problems in your text that told you to estimate derivatives based on tabular data were some of the most confusing, right? Nice functions in terms of x and y are much nicer. I know. I know. Also, throw out your fancy derivative rules. They're useless here.

This section is based on

A          B          C               D           E
Date    ODO    Gallons   Cost     Company

You need the following calculations:
Trip                            =(B3-B2)
Miles/Day (2)         =(B4-B2)/(A4-A2)
Miles/Day (10)        =(B11-B2)/(A11-A2)
Cost/Gallon             =(D2/C2)
MPG                          =(B11-B2)/(C2+C3+C4+C5+C6+C7+C8+C9+C10+C11)

The reason for the two different Miles/Day columns is a resolution issue. Since the data is in a table, the formulas need to determine the slope of the Mileage over Time function, rather than the actual derivative. Sometimes I go on long trips and have to get gas more than once a day, recording two sets of data for the same date. For you math people, remember what happens when there are two values of Y for one value of X? Yep, vertical lines with infinite slope. Bad news. The (2) represents the slope over two cells. The (10) represents the slope over 10 cells. This is kind of like getting different resolutions on your data. The MPG formula is the way it is for much the same issue, since MPG is calculated from incomplete data. To calculate true MPG you'd have to run your gas tank completely empty each time and figure out exactly how much gas you burned. With your data set there's a little overlapping going on, so it needs to be smoothed out to get useful data. That's why the MPG formula uses 10 data points.

Step 7: Creating Graphs

Now that you've processed your data, you need to create a visual representation of it so you can interpret the information more easily. Most of your graphs will make the most sense when you plot them over time. As in "What is my ODO reading as time progresses?" or "How does my MPG change between summer and winter?"

To plot your mileage, select column A, then hold control and select column B. Now both columns are selected. It' important to select the Date column first, as this tells Excel you want to use it as your X axis. Now click the "Insert" tab and click "Scatter". I found that the smooth line plot with no point protectors works the best.

Now you can make graphs of all the data combinations you want! Go crazy! Do anything you want! Try everything you can imagine. Plot Mileage against MPG, plot your Trip against Gallons, plot things against things that don't make sense and try to find patterns, then make sense of them! However, the most useful plots to you will be:
Mileage against Date
Miles/Day against Date
Cost against Gallons
MPG against Date

Step 8: Company Pie

You can make a pie chart of the companies you visit, but this takes a few steps. Figure out how many different companies you've visited and find that many free cells somewhere off to the side. You entered the company names in the E column right? In the cells you want, type =COUNTIF(E:E,"Chevron")

The E:E tells COUNTIF to look for "Chevron" in all the cells in column E. You'll need to copy this formula to all the necessary cells and change the name of the company each time.

Now select the cells with the totals you've just created and go to Insert -> Pie -> 2D Pie.

This makes a pie chart of your favorite companies.

Step 9: Other Sums and Calculations

Off to the side of my graphs and tables, I created a section dedicated to totals. Remember:
A    B C D E
Date ODO Gallons Cost Company

In the new section, enter this:

     L                                           M                      N
1
2 Miles Driven:             =MAX(Milage)-MIN(Milage)          Miles
3 Time:                                  =MAX(Date)-MIN(Date)                 Days
4                                               =M3/365                                          Years
5 Avg Miles/Day:                =M2/M3                                Miles
6 Avg Miles/Year                  =M2/M4                                           Miles
7 Fuel Consumed               =SUM(Gallons)       Gallons
8 Avg Fuel Economy           =M2/M7       MPG
9 Total Fuel Cost                 =SUM(Cost)                    Dollars
10    Avg Cost/Month                =M9/(M4*12)                    Dollars
11 CO2 Produced                 =8.877*M7/1000                           Tonnes
12 CO2/Year:                          =M11/M4    Tonnes

The CO2 Mass is calculated in metric tonnes, which is equivalent to 2021 lbs for the metrically impaired.

Step 10: Results

Interpreting results is pretty straightforward once you have your data and graphs. I found that I drive much more during the summer, sometimes up to five times more. I get better gas mileage on long trips. I get on average 21MPG. I would get more if I didn't drive in town so much. I've spent waaaay too much money on gas, more than my car is worth. And I'm a horrible person for generating 21 metric tonnes of CO2. There is other fun data you can glean from the graphs, for example in my cost per gallon graph there was a large singular spike in 2007 that correlated with a trip I made to California. I was about to run out of gas an had to stop in a little tourist trap gas station. Bad news. Stay away from those.

Also, If you ever needed motivation to stop driving your car and ride the bus or other public transport, this project is definitely for you!

I have attached my Excel spreadsheet. You have my permission to use and modify it under the same terms as the license under which this Instructable is posted.

I hope you found this Instructable useful, or at least not completely boring, and good luck on your own data logging!

Have Fun!