Personal Fuel Calculator

[Kcissem]

Attached are two spreadsheets for my Personal Fuel Calculator, one is for Excel 97-2003 and the other is for Excel 2007. Layout is optimized for screen resolutions 1400x900 but will work on other resolutions.

Features Include

- Tracks Individual Statistics such as Season, Brand, Fuel Date, $cost, Gallons, $Cost per gallon, Miles per tank, Total Miles, MFD MPG, Calculated MPG, $Cost per MFD mile, $Cost per calculated mile.

- Tracks lifetime averages for all individual statistcis with the exception of brand type

- Tracks Seasonal averages for all individual statistic with the exception of brand type

- Tracks record high and low averages for for MFD MPG, Calculated MPG, and Miles per tank.

- 2 graphs to compare MFD MPG to Calculated MPG, and $Cost per MFD mile to $Cost per Calculated Mile

- Around 130 prefilled rows with Formulas, Cells shaded in light blue contain formulas DO NOT input into these fields as it will delete the formula.

- some example entries are provided. Some field require specific inputs for example the season column requires an W = Winter, SP = Spring, S= Summer, F=Fall

Future Enhancements

- I plan on programming an application to replace this spreadsheet in a scripting language called Runtime Revolution.

Please give any feedback

[a priori]

I like the look. Thank you!

You have a much slimmer spreadsheet than I use (I add all sorts of other calculations that likely have interest to me alone!), and it is appealing to look at. I like the graphs, and I am hoping they are time/date scaled.

I hope to enter some of my data to see what it would look like!

[Kcissem]

Originally Posted by a priori

I like the look. Thank you!

You have a much slimmer spreadsheet than I use (I add all sorts of other calculations that likely have interest to me alone!), and it is appealing to look at. I like the graphs, and I am hoping they are time/date scaled.

I hope to enter some of my data to see what it would look like!

yes the dates will scale as more entries are put in, All graph data is set to auto except for the minimum MPG which is fixed at 40 for that graph.

[fishinmyeye]

Thanks so much for this! It looks and works great!
I am a spreadsheet rookie so this is just what I was looking for.
I have a question though, I have a relatively new Prius but I only started saving my fill-up/mpg info starting at 1629 miles on the odometer. For that reason when I fill in the first row of the chart my calculated mpg is way off (high) b/c it is taking all those miles as being driven on 1 tank instead of just the previous 458 like it should. Is there a place to correct for this by entering the "started to chart" odometer reading?
Thanks again Kcissm!

[fishinmyeye]

Originally Posted by fishinmyeye

Is there a place to correct for this by entering the "started to chart" odometer reading?
Thanks again Kcissm!

Never mind, I see it... at the head of the column. Thanks again for the great work!

[beach247]

Thanks!

[Fraser]

Kcissem, I like your calculator. I'm barely beyond novice stage on Excel (took a college Lotus course once a hundred years ago), and this works for me.

I did just stumble across a potential problem tonight. As I was entering tonight's fill, I had previously used a hand calculator in my car to figure my manual mileage -- MFD read 52.5 on 532 miles, and my calculation showed 55.78 with a 9.537 gallons fill. Fairly close, not out of line with what I was seeing before. But when I put my figures into the spreadsheet, I got a manual calculation of 68.5 mpg in column J. So I looked at the formula in cell J24 and saw that is was dividing H24 by J23. I checked the other J cells and noted that you are dividing the current miles by the previous number of gallons. I'm far from a math whiz, but it seems to me I should be dividing the current miles (532, in this case) by the most recent fill (9.537) to get the miles per gallon based on the most current numbers. That's the way I've always figured mileage. Could you please explain the reasoning behind using the previous fill gallons in the J cell formulas? If that's the way it should be, I'm happy to accept your reasoning, but it seems odd to me. I'm using the Excel 2003 program. Please be gentle.

[Prius 07]

Nice work. It appears that for lifetime MFD average you have a simple average of all the individual results. A more accurate (and true average) would be a weighted average based on the mileage driven for each individual tank. This is what I use in my spreadsheet. The formula for it: =SUMPRODUCT($K$9:K30,$B$9:B30)/SUM($B$9:B30)

coloumn K contains the range of MFD results and coloumn B the miles driven for each tank.

Again nice look and functionality.

[Kcissem]

Originally Posted by Fraser

Kcissem, I like your calculator. I'm barely beyond novice stage on Excel (took a college Lotus course once a hundred years ago), and this works for me.

I did just stumble across a potential problem tonight. As I was entering tonight's fill, I had previously used a hand calculator in my car to figure my manual mileage -- MFD read 52.5 on 532 miles, and my calculation showed 55.78 with a 9.537 gallons fill. Fairly close, not out of line with what I was seeing before. But when I put my figures into the spreadsheet, I got a manual calculation of 68.5 mpg in column J. So I looked at the formula in cell J24 and saw that is was dividing H24 by J23. I checked the other J cells and noted that you are dividing the current miles by the previous number of gallons. I'm far from a math whiz, but it seems to me I should be dividing the current miles (532, in this case) by the most recent fill (9.537) to get the miles per gallon based on the most current numbers. That's the way I've always figured mileage. Could you please explain the reasoning behind using the previous fill gallons in the J cell formulas? If that's the way it should be, I'm happy to accept your reasoning, but it seems odd to me. I'm using the Excel 2003 program. Please be gentle.

My reasoning for using the previous fill up is due to the variable bladder size, On normal tanks that fill up the same amount everytime it's ok in my opinion to use the current fill up is it is usually always the same no matter the conditions. Yet on a PRI you have the variable bladder size, you may go 500 miles and put in 9 gallons one time, and then you may go 500 again but only put in 7 gallons on the next. So the way i calculate it on the pri is based upon the current miles for the tank divided by the previous fill up for gallons. This to me gives a more accurate calculated mpg reading for the current tank, and it more closely matches the MFD MPG. My current tank i just finished MFD was 60.5 while calculated was 60.7 using my current method, but using the old method that is used for a normal gas tank, it would be 58.2 calculated MPG.

to make a long story short i calculate the MPG for the prius in that way due to the variable bladder size of the prius. This may or may not be the correct way but it works for the prius IMO.

[Kcissem]

Originally Posted by Prius 07

Nice work. It appears that for lifetime MFD average you have a simple average of all the individual results. A more accurate (and true average) would be a weighted average based on the mileage driven for each individual tank. This is what I use in my spreadsheet. The formula for it: =SUMPRODUCT($9:K30,$9:B30)/SUM($9:B30)

coloumn K contains the range of MFD results and coloumn B the miles driven for each tank.

Again nice look and functionality.

I will play around with your suggestion once i get time, Been a busy time lately.