1. Attachments are working again! Check out this thread for more details and to report any other bugs.

Personal Fuel Calculator

Discussion in 'Gen 2 Prius Fuel Economy' started by Kcissem, Aug 13, 2008.

  1. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    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
     

    Attached Files:

  2. a priori

    a priori Canonus Curiosus

    Joined:
    Aug 14, 2007
    3,083
    407
    23
    Location:
    Chicagoland (West)
    Vehicle:
    2010 Prius
    Model:
    V
    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!
     
  3. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    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.
     
  4. fishinmyeye

    fishinmyeye Junior Member

    Joined:
    May 17, 2008
    8
    0
    0
    Vehicle:
    2008 Prius
    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!
     
  5. fishinmyeye

    fishinmyeye Junior Member

    Joined:
    May 17, 2008
    8
    0
    0
    Vehicle:
    2008 Prius
    Never mind, I see it... at the head of the column. Thanks again for the great work!
     
  6. beach247

    beach247 New Member

    Joined:
    Apr 3, 2008
    77
    0
    0
    Location:
    GTA
    Vehicle:
    2008 Prius
    Model:
    N/A
  7. Fraser

    Fraser New Member

    Joined:
    May 9, 2008
    667
    9
    1
    Location:
    Navarre, Florida
    Vehicle:
    2008 Prius
    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.
     
  8. Prius 07

    Prius 07 Member

    Joined:
    Jun 6, 2007
    715
    21
    0
    Location:
    Toronto, Canada
    Vehicle:
    2007 Prius
    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.
     
  9. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    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.
     
  10. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    I will play around with your suggestion once i get time, Been a busy time lately.
     
  11. Fraser

    Fraser New Member

    Joined:
    May 9, 2008
    667
    9
    1
    Location:
    Navarre, Florida
    Vehicle:
    2008 Prius
    I'm wondering if we (PCers) are changing our method of calculating mileage less because of the variable bladder size than because we can see the real-time MPG. Non-MFD cars don't give us that experience. My previous cars have been pretty consistent on mileage in given situations (so much for city driving, so much for highway driving), within a range. Prius can vary greatly even over the same route based on times stopped, pressure on the accelerator, ideal speed, etc. So a 500-mile tank this month may not have all the same elements as a 500-mile tank last month. We would have to check the displayed MPG to see if we actually were getting the same MPG for the same distance travelled. So, while theoretically the car's calculation should be the same as a manual calculation, we see a displayed figure and assume it is the standard and make changes in our methods of manual calculation based on that premise.

    Additionally, I'm not sure why the previous fillup would be any more accurate than the second-previous fillup or an even older fill. It seems to me that the previous fill has no relation, physically, to the current tank volume. I would think that the current fill relates to the current tank volume, but I understand why you have a different view.

    At any rate, you've given me something else to think about, however poorly I express those thoughts, and I appreciate that.
     
  12. krmcg

    krmcg Lowered Blizzard Pearl Beauty

    Joined:
    May 15, 2008
    2,592
    2,390
    0
    Location:
    Whittier, CA
    Vehicle:
    2016 Prius
    Model:
    Four
    I found this excel table a great starting point. As with most applications, some personal adjustment makes them more useful.
    I had already changed the formula to reflect the current tank fill and have now incorporated the weighted value from the MFD.
    I fooled around a bit with some of the other formulas as well and now have something that suits me to a tee.
    Thanks for doing the hard part, and leaving the fun adjustment only.
     
  13. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    the relationship between the gallons filled previously for the current tank is relevant due to the fact on the current tank you using the gas that you filled up at last stop, so to me this would be a more accurate way to calculate due to the variable bladder size. For instance on the dealer tank i went about 490 miles with a mfd average of 48.3, but put in 12.1 gallons on fillup, calculated that would have been 41.5mpg, on the next tank went 667 miles but only put in about 9.6 gallons with the MFD showing 55.6 but calculated would have been 68mpg for that tank, but calculating based off the previous fill up 667/12.1 = 54.8 which is much closer to 55.6 than 68. Either way it should still average out in the end over multiple fill ups. The spreadsheets were just a started point for anyone willing to use, modify the formulas as you see fit.
     
  14. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    no problem, the spreadsheets were always meant to be a starting point, everyone has different views on how things should be calculated and the formulas are easy enough to adjust to personal tastes.
     
  15. Fraser

    Fraser New Member

    Joined:
    May 9, 2008
    667
    9
    1
    Location:
    Navarre, Florida
    Vehicle:
    2008 Prius
    I see what you're trying to do with using previous fillups. But I'm still having trouble agreeing with that. Using simple figures that my soft brain can handle, let's say you have driven for X miles (the number doesn't seem to be relevant here) and stop to put in one gallon, and that fills it up again. You drive 100 miles, and then you put in 10 gallons. By your spreadsheet, that most recent MPG would be 100 mpg. But regardless of the size of the tank, you have used 10 gallons of gas in driving that 100 miles, so it seems to me the effect would be 10 mpg.

    I agree that a better overall average is determined with a number of fills, and that should be a better indication of the efficiency of the engine.

    Again, thank you for your hard work on what could well be the definitive mileage calculator.
     
  16. Kcissem

    Kcissem New Member

    Joined:
    Jul 29, 2008
    50
    0
    0
    Location:
    Maryland
    Vehicle:
    2008 Prius
    I understand what you are saying also, i thought about this for awhile while making the spreadsheet, i went back and forth on this fact, and finally decided on the formula i used. I have yet to hear a definitive arguement against calculating it this way on the prius so i have not changed my mind yet. If things start to get out of wack in the calculations then i may change it around but as of now it seems to work for me. One reason it may work for me is i always fill up at a certain point on the guess gauge so i know more or less my gallons filled should be similiar as the previous stop. For instance i always fill up at the first Shell station once my last pip starts to blink

    I would actually have a better lifetime calculated MPG right now if i calculated your way, better by two mpg actually, my right now calculated lifetime mpg is 54.9, your way would be 56.7 while the MFD is 55.9 lifetime. So in the end i think both ways work while both ways are still off by about 1% or so of the MFD average. I should probably think more about it again.
     
  17. GreenJuice

    GreenJuice Active Member

    Joined:
    Sep 3, 2008
    195
    127
    3
    Location:
    London, UK
    Vehicle:
    2016 Prius
    Model:
    N/A
    Many thanks Kcissem, very useful calculator for those of us new to Prius and this Forum.
    I've modified it for the UK - we have a wierd mix of buying petrol in litres but measuring everything else in imperial units, miles, mpg, etc!
    Cheers!
     
  18. freshspeedo

    freshspeedo Junior Member

    Joined:
    Oct 3, 2008
    22
    0
    0
    Location:
    Canada
    Vehicle:
    2007 Prius
    Great calculator for excel.

    I'm no expert in excel and wanted to change the results to display in Litres and km's

    How do I go about changing the formulas in excel?

    Does somebody have this calculator but for the Metric system?
     
  19. vincent1449p

    vincent1449p Active Member

    Joined:
    May 24, 2004
    894
    331
    0
    Location:
    Singapore
    Vehicle:
    2012 Prius c
  20. wangjiahua

    wangjiahua New Member

    Joined:
    Oct 21, 2008
    7
    0
    0
    Location:
    china
    Vehicle:
    2008 Prius
    I found that connector you were looking for

    Cindy, is this the thing you were lookin
    It's on the list of computer accessories and parts. They have the DVI video thing to convert that jap monitor to work with your other computer. Just about any other kind of wire adaptor, usb connectors, monitor extension wires, ps2 extention wires, and all kinds of female and male swap connectors and things that I think would help your shop. If that above linclick on computer accessories. Let me know if that is what you need and give me your email address again.
    :cheer2: