Whatever your purpose, this Instructable details a useful method to accomplish the best results for your data analysis.
Step 1: OCD Much?
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
Gas Station Type
From this information, combined with Microsoft Excel or some other data entry program, you can find:
Length of Experiment
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
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
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 "fx" box is the column name field. Enter the names of all your columns here.
Step 5: Creating Calcuated Columns and Autosums
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:
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
This section is based on
A B C D E
Date ODO Gallons Cost Company
You need the following calculations:
Miles/Day (2) =(B4-B2)/(A4-A2)
Miles/Day (10) =(B11-B2)/(A11-A2)
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
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
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
A B C D E
Date ODO Gallons Cost Company
In the new section, enter this:
L M N
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
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!