Introduction: A Case Study in Gasoline Use

Picture of A Case Study in Gasoline Use

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?

Picture of 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

Picture of 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

Picture of 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

Picture of 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 "fx" box is the column name field. Enter the names of all your columns here.

Step 5: Creating Calcuated Columns and Autosums

Picture of 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

Picture of 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

Picture of 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

Picture of 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

Picture of 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

Picture of 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!

Comments

killroytech (author)2016-03-30

I've been doing this for the last 30 years, with 8 different cars, recently I added the outside temp reading, so worse fuel economy in winter can be traced to cold weather.

I use it also as a personal journal, the gear head kind. (trips, who is in the car, special events, maintenance.storms!)

It will be funny to read it back 10 years from now.

acheide (author)2015-03-19

Nice work. We log all expense entries due to business use. This will be handy to determine end of life for a vehicle as well.

Thanks

milothefultz (author)2011-08-04

This is the geekiest and I am so enthralled. I usually just try to find out my MPG, but I may just start doing this from now on. Thanks for the detail and the multiple calculations possible. Was there a way to find a correlation between MPG and gas brand?

DarkStarPDX (author)2011-01-19

I was spending about $0.14 per mile for gasoline in my previous car... When the car croaked I decided my next one would be electric as it will only cost $0.014 per mile.

I've been waiting almost 2 years, but my Nissan LEAF finally arrives in April!

buteomont (author)2011-01-19

Great idea! The only problem I see is that the amount of gas that the pump says you bought isn't always the amount that you put in your tank.

Gas stations are supposed to have their pumps calibrated and tested by the state highway department every 6 months, but it seems that (at least in Tennessee) they don't have the manpower to do it that often. The way I understand it, they do good to check them once every 6 years!

From my own experience, very few of them seem to actually give you what you bought, in some cases by quite a lot. I drive a little Smart car which has an 8.5 gallon tank, and one pump that I used showed where I put almost 10 gallons in it!

Of course, there's no incentive for the gas station owner to fix his pumps if they're cheating people. I'd like to see if your data shows any indications of this problem.

CrLz (author)2011-01-18

Very nice, my dad and I track the gas mileage similarly!

Depressing how much $ goes into fuel over the life of a car...

About This Instructable

3,216views

8favorites

License:

Bio: I'm a graduate student in the Materials Science department of the University of California at Santa Barbara. I made these Instructables while I was ... More »
More by laserjocky:Digital Frequency CounterLearn Counter ICs Using an ArduinoAnalog Function Generator
Add instructable to: