I’ve been keeping a spreadsheet of my fuel consumption for the past three years. I’d like to put it in a graph. I used the insert chart function in Excel to create a chart of Sheet 2, but I don’t see how to change the x-axis. I have my calculated MPG on the y-axis. The x-axis is just the line numbers. I’d like to put the dates (Column A) on the x-axis, and I’d like to choose intervals and stuff like that so I can smooth the line.
It should be a scatter chart.
It doesn’t have to be a scatterchart. A regular line graph would work fine.
Did you select the two columns and only the two columns? It worked like it was supposed to when I did this sample.
You can email me the spreadsheet if you want me to take a look (click my username).
That looks like what I’m after.
Here’s the chart wizard. It only gives me the option for the data, rather than the dates.
Thanks for the offer. Email sent.
Well, that was a neat learning experience.
First, to directly answer your charting question, you simply had to select the date column in addition to the MPG column (by holding down CTRL). You can also do that before you make the chart if you don’t want to use the Wizard.
That was the easy part. The hard part: Because your MPG was collected on irregular dates, to make a useful comparison you first have to summarize them by month. I had to Google around a bit to figure out how to do that, but it seems the easiest way is to make a PivotTable out of them:
- Select your whole data table on Sheet1 and insert a PivotTable.
- Drag the “Date” field into the “Row Labels” box. Drag the “Calc MPG” field into the “Values” box.
- In the newly-created PivotTable, right-click on any of the specific dates and choose “Group”. Select ONLY “Month”.
- In the same PivotTable, right-click on any of the specific VALUES (Calc MPG) and choose “Summarize by…” → “Average” instead of the default Sum.
- Make a line graph from the result.
That’s the first chart you see in the worksheet called “Reply’s Charts”. That tells you your highest MPG is in September, but still doesn’t break it down by season.
To do that, you have to further average the months out by season (Mar, May, Sep, Dec) and do another chart (the second chart). That tells you, on average, that you get 4 MPG less in the summer than in the winter. Your hunch seemed to be correct 
ETA: And just FYI, the third chart shows by month AND year, but it wasn’t very visually useful.
Outstanding! I’ll save these instructions and do some more playing.
You can see the dips in the third chart. I have a theory about that. Winter is cold and dark. I suspect that the use of the heater, lights, and windshield wipers account for some loss. It can also be blustery, and I’ve noticed head- or side-winds adversely affect mileage. Fuel is oxygenated in the Winter, and I think the addition of alcohol lowers mileage as well.
Thanks for doing that for me. I didn’t think it would be so involved. I’m glad you found it fun! With your instructions, I can start my learning experience.
Heh, yeah, if I had a Prius I’d be doing that all day long. I’d probably be tinkering with the onboard computer to make it automatically turn down the AC or roll up the windows if mileage dips below a certain point, even. This makes me want to get a computer for my bike – I always wonder if my riding speed differs depending on hourly temperature, calorie consumption, current body weight, etc.
In your case, I made the third chart a bit more readable here. Those are interesting theories 
What’s the difference between Ind MPG, Calc MPG, and Average MPG anyway? And what caused the highly-irregular days for the data (instead of every day or every month, etc.)?
There are a lot of variables. First: Refueling days. I generally commute to the office (210 to 220 or so miles r/t, depending upon whether I drive downtown or use the park & ride) three days a week. Sometimes two. Usually that translates to refueling on Mondays, Wednesdays, and Fridays. But I might take a road trip, so that will disrupt the intervals.
I try to refuel at the same pump at the same time each time, but that’s not always possible. Different pumps will ‘click off’ at different times. So will the same pump if it’s just been serviced, if it’s near the end of its service interval if it’s delivering fuel more slowly or quickly, etc.
I suspect that the ‘outliers’ happen when, for whatever reason, the pump shuts off early. Thus, I seem to go farther on less fuel. I think it comes out in the wash, though. For example, in one case I show 57 mpg, and the next fueling I show 39 mpg. That averages to 48 mpg, which is about where it should be.
Ind MPG is the miles per gallon indicated on the nav unit. Calculated MPG is calculated using the trip meter and the amount of gas delivered as stated on the receipt. Average MPG is the average MPG since I started the spreadsheet. Average error is the discrepancy between the indicated MPG and the calculated MPG.
Wow, that’s a long commute. And so the data gets downloaded every time you refuel?
Or use a scatter chart.
I update the spreadsheet after each fueling. Since gas has gotten so expensive, I’ve started going to Arco instead of my local station. (Toad’s was never as cheap as Arco, but it was the cheapest place around until it came under new ownership.) I used to clip my receipts, with mileage and indicated mpg written on them, clipped to my office key card and enter the data when I got to it. Since I have to use a debit card at Arco, I have to record the transaction in my check register so I do the spreadsheet at the same time. Last night I was curious about graphs, so I logged into the office computer and emailed the file to me. I only do that sporadically, though.
It’s a long commute, but it gives me the chance to listen to NPR for a couple of hours, and to get into a city. Unlike L.A., Seattle is pedestrian-friendly so I can get out and about and not have to have a car there. When I was working in Orange, one of the managers there lived in Big Bear. That was a 100-mile commute for him. But then, he had an old Piper Cherokee to commute in. 
How do you do it with a scatterchart?
Format the date cells as dates and put them on the x-axis. You can use line smoothing to connect the points and/or a trendline if desired.
I like the following site’s charts.
I’m not sure it has all the whiz-bangs it could possibly have, but it is pretty nice.
I used to religiously chart my mileage there, prior to losing my old job and becoming self-employed.
No job to drive to… my weekly mileage is quite low, and MPG doesn’t really affect my finances any more.
ETA:
http://www.cleanmpg.com/index.php?page=garage&displayunits=MPG(US)&viewcar=3492
Here are the graphs Reply made for me.
I might be doing this wrong, but all I got was this, which is pretty much what a line graph gets you. Like line graphs, I can add an overall trendline for the entire dataset, but that’s not very useful. Is there a way to do separate trendlines by month or to otherwise make it easier to comprehend?
Hmm, interesting how all the arrows became misplaced.
Do it over the whole series, and format the trendline as “moving average”. Adjust the averaging period (number of points) to whatever suits you.
I’d also start the y-axis at 20, and make the x-axis major division a month and minor a week.