Spreadsheets, for just about anything.

Discussion in 'Gen 3 Prius Fuel Economy' started by thbjr, Dec 16, 2009.

Page 1 of 2
  1. thbjr

    thbjr Member

    Joined:
    Nov 15, 2009
    326
    63
    0
    Location:
    Phoenix
    Vehicle:
    2010 Prius
    Model:
    III
    I've spent several hours searching the forums for spreadsheets to track mileage and maintenance. My guess is I'm not alone in that I don't know squat about Excel cell formulas to get the spreadsheet to do the calculations.
    I want to give a big THANKS to those that have attached a copy of their tracking spreadsheets in threads they posted in. I have collected several of them, all I could find anyway, and an attaching then to this thread. There are a wide range of them, from the very complex to the very simple.
    The spreadsheets were copied just the way I found them, so you may need to delete data already in the spreadsheet and then put in your data.
    I also saw some very nice looking spreadsheets from members, a_priori, bwilson4web, and TonyPSchaefer to name a few, but the actual spreadsheet was not attached.
    My hope is that other members will attach their spreadsheets to this thread so that new members, or even senior members, can look through them and use which ever one best serves their individual needs.
    Thanks again to those who have shared their spreadsheets with the community!!!!!

    You may need to copy and paste a copy of the spreadsheet from the zip file to your desktop to change it from a 'read only' to a 'read and write' file.

    For those who don't have Microsoft Excel, you can use these spreadseets with Open Office. To download a free copy of Open Office, go to OpenOffice.org

    (Mods, if there is a better forum to place this thread, please feel free to move it.)
     

    Attached Files:

    • Fuel.zip
      File size:
      692.4 KB
      Views:
      405
    #1 thbjr, Dec 16, 2009
  2. wvgasguy

    wvgasguy New Member

    Joined:
    May 6, 2009
    1,255
    185
    0
    Location:
    a
    Vehicle:
    2010 Prius
    Model:
    V
    comment deleted
     
    #2 wvgasguy, Dec 16, 2009
  3. TonyPSchaefer

    TonyPSchaefer Your Friendly Moderator
    Staff Member

    Joined:
    May 11, 2004
    14,816
    2,499
    66
    Location:
    Far-North Chicagoland
    Vehicle:
    2017 Prius Prime
    Model:
    Prime Advanced
    The only reason I don't my spreadsheet is that it's a big mess. I've distributed it to a few people will explanations on how to populate it. I'll try to find the "blank" version and post it.
     
    #3 TonyPSchaefer, Dec 16, 2009
  4. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    Google docs works well for me.

    Read only version of mine: http://spreadsheets.google.com/ccc?key=0AjOnKoSMKSnMckdFSzNxNVJGUF9IZ0Nxd1lOem1STWc&hl=en

    Feel free to copy and use for your own purpose. The fields that are editable are:

    Date, ODO, Gallons, Price Per Gallon, Tank Price (entered from receipt - eliminates any rounding), Display MPG, and Comments.
    ​
    From there, the following is calculated:

    Tank Miles, Calculated MPG, Gas Cost Per Mile, Display vs. Calculated Δ (delta), Lifetime MPG (calculated), Lifetime MPG (display), Disp vs. Calc Lifetime Δ, Average Tank Cost, Lifetime Fuel Cost, and Average CPM (cost per mile).
    ​
    On the next tab I have a few charts with "copied" data that auto-populates. The charts aren't smart enough to auto-increment, and I don't like having lots of zeros or error data, so I edit the ranges for them every time I update.
     
    #4 HTMLSpinnr, Dec 16, 2009
  5. mgb4tim

    mgb4tim Noob

    Joined:
    Jul 8, 2009
    1,153
    111
    9
    Location:
    Pittsburgh, PA
    Vehicle:
    2010 Prius
    Model:
    III
    looks a lot like mine, but I didn't start off including the displayed mileage, but think I will start just to note the difference.
     
    #5 mgb4tim, Dec 17, 2009
  6. thbjr

    thbjr Member

    Joined:
    Nov 15, 2009
    326
    63
    0
    Location:
    Phoenix
    Vehicle:
    2010 Prius
    Model:
    III
    Thanks HTMLSpinnr for the link. I was able to download your spreadsheet and then, with the help of our Excel master here at work, Ben, tweek it. I hope you approve of the changes.
    Ben, my Excel teacher, also helped me tweek another of my personal favorites from the zip file in the OP. Sorry, I don't know who to give credit to for the original.
    I'm attaching both 'tweeked/revised' spreadsheets to this post. I hope you enjoy them. (Be sure to check out the graph charts after data is input. :D)

    If you see your spreadsheet amoung the ones I collected, PLEASE, do take credit for it. If you want to add your spreadsheet to the thread, I would encourage you to do so.
     

    Attached Files:

    #6 thbjr, Dec 17, 2009
  7. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    I'll look into the changes - I don't use it offline, so I am "limited" to the capabilities of Google Docs. However, because it is a Google Doc, I can update it from home, work, etc. (and even someday, my BlackBerry).
     
    #7 HTMLSpinnr, Dec 17, 2009
    1 person likes this.
  8. SageBrush

    SageBrush Senior Member

    Joined:
    Jun 4, 2008
    11,627
    2,531
    8
    Location:
    Southwest Colorado
    Vehicle:
    2012 Prius v wagon
    Model:
    Two
    Pretty good iPhone apps exist for the MPG obsessives amongst us. Gas Cubby lite is free, and even purports to satisfy the 'hypermilers'. No doubt this is the screenshot to seal the deal: :D
    [​IMG]
     
    #8 SageBrush, Dec 17, 2009
  9. jdcollins5

    jdcollins5 Senior Member

    Joined:
    Aug 30, 2009
    5,131
    1,341
    0
    Location:
    Wilmington, NC
    Vehicle:
    2010 Prius
    Model:
    III
    The second spreadsheet, Prius Mileage.xls, is mine. I will take it home tonight and re-enter the data and let you know what I think.

    Thanks,

    Dwight
     
    #9 jdcollins5, Dec 17, 2009
    1 person likes this.
  10. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    Took a peek at the second sheet (a variation of the online version).

    Because this is a Google Doc, the export won't be 100% the same within Excel. It would seem that when downloading, you didn't get both tabs - the second one had about 5 charts ;) I've added most of the formula suggestions (mostly IF clauses) - they're not a bad idea - I just didn't know/care enough to make them happen initially. As for formula in the tank price, sometimes I deviate and mix 2 partial tanks (i.e. adding $10 worth, then a more complete fill when prices are lower across state lines (CA to AZ)), then count it as one tank for calculation purposes - hence why it wasn't made a specific formula ;-) Other times, the 9/10th in the price (which is accurately typed in, but rounded up), combined w/ the 100ths of the gallons (also rounded up) make for a total price that deviates ±1-2¢ either way. I prefer to type in what I actually paid.

    Unfortunately, the IF calculations don't really help w/ the graphs. In the online version, I have 5 separate graphs in a second tab. They do reference copied data (out of order from the first sheet) so that I can group the ranges correctly. They track per tank and lifetime MPG (both display and calculated). I also track cost per mile, average tank cost, gas prices, difference between display and calculated MPG, etc.
     
    #10 HTMLSpinnr, Dec 17, 2009
  11. thbjr

    thbjr Member

    Joined:
    Nov 15, 2009
    326
    63
    0
    Location:
    Phoenix
    Vehicle:
    2010 Prius
    Model:
    III
    Dwight,
    On your original spreadsheet I found there were only 14 lines to enter data on. We tweeked some of the cell formulas with logic and added several extra rows for data. I wanted it to be able to track closer to a full years worth of data.
    Thanks for your input.
    Tom
     
    #11 thbjr, Dec 17, 2009
  12. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    I'm not an iPhone user, so I'm fine w/ the Google Doc method.
     
    #12 HTMLSpinnr, Dec 17, 2009
  13. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    ** Thinking more on it, I'm moving to the Fuel Economy sub-forum. While not 2010 specific, it's a better fit there. **
     
    #13 HTMLSpinnr, Dec 17, 2009
  14. thbjr

    thbjr Member

    Joined:
    Nov 15, 2009
    326
    63
    0
    Location:
    Phoenix
    Vehicle:
    2010 Prius
    Model:
    III
    Actually, the second tab did download. Now that I understand why you wanted them, I'll add a copy of the original download. When I first looked at the 2nd tab, I didn't see it as seperate groupings, just copied data from the 1st tap. My mistake.
    In fact, I'll zip both originals with my changed versions.
     

    Attached Files:

    #14 thbjr, Dec 17, 2009
  15. SageBrush

    SageBrush Senior Member

    Joined:
    Jun 4, 2008
    11,627
    2,531
    8
    Location:
    Southwest Colorado
    Vehicle:
    2012 Prius v wagon
    Model:
    Two
    High tech compared to me. I am happy with the Total_miles/Total_fuel method aided by a hand calculator.
     
    #15 SageBrush, Dec 17, 2009
  16. HTMLSpinnr

    HTMLSpinnr Super Moderator
    Staff Member

    Joined:
    Dec 8, 2003
    5,341
    920
    251
    Location:
    Surprise, AZ (Phoenix)
    Vehicle:
    2018 Tesla Model 3
    Model:
    N/A
    I've added one more tab to the Google Doc sheet:

    Gives me the stats below on another sheet:

    Column 1 Column 2 Column 3
    0 Stats Value
    1 As of Date 12/19/2009
    2 As of Odo 7 808
    3 Best Tank (Calculated) MPG 51.19
    4 Best Tank (Display) MPG 55.50
    5 Worst Tank (Calculated) MPG 40.50
    6 Worst Tank (Display) MPG 42.90
    7 Highest Miles per tank 453
    8 Biggest Calc vs. Display MPG 4.31
    9 Smallest Calc vs. Display MPG 0.88
    10 Highest Gas Price (Tank) $2.83
    11 Lowest Gas Price (Tank) $2.25
    12 Total spent on Gas (Car Lifetime) $403.84
     
    #16 HTMLSpinnr, Dec 20, 2009
  17. thbjr

    thbjr Member

    Joined:
    Nov 15, 2009
    326
    63
    0
    Location:
    Phoenix
    Vehicle:
    2010 Prius
    Model:
    III
    Thanks Rick, Here it is as an Excel spreadsheet.
    Tom
     

    Attached Files:

    #17 thbjr, Dec 20, 2009
  18. jdcollins5

    jdcollins5 Senior Member

    Joined:
    Aug 30, 2009
    5,131
    1,341
    0
    Location:
    Wilmington, NC
    Vehicle:
    2010 Prius
    Model:
    III
    Tom,

    Yes, I insert a new line each time I enter new data. This way I keep the trend at the bottom of the data so I can view while entering new data.

    Thanks for the tweeking. I plan to keep this spreadsheet going for as long as I own the car.

    Thanks,

    Dwight
     
    #18 jdcollins5, Dec 21, 2009
  19. Indyking

    Indyking Happy Hyundai owner...

    Joined:
    May 28, 2009
    1,280
    90
    0
    Location:
    I don't know... Indy, Chicago, Madison (WI)... it
    Vehicle:
    Other Non-Hybrid
    Model:
    N/A
    I see the Honda Fit gets pretty close to your Prius MPG... and the graph shows spring only I think. Not sure in what part of the country you live, but I would not be surprised at all if the Honda fit actually beat the Prius MPG in very cold climates considering that my previous CR-V gets close to it...
     
    #19 Indyking, Dec 22, 2009
  20. DaveinOlyWA

    DaveinOlyWA 3rd Time was Solariffic!!

    Joined:
    Apr 13, 2004
    15,140
    611
    0
    Location:
    South Puget Sound, WA
    Vehicle:
    2013 Nissan LEAF
    Model:
    Persona
    i can post mine but mine is a mess too. i did it the way it is now, because my SO and other family members who drive Pri's could not understand how the cells were formulated, so i ended creating another one that breaks down each calculation separately. this created a very WIDE spreadsheet as i had separate calculations for summer and winter gas along with computer data and pump data on each. along with LT figures on all 4 causing essentially 4 separate tables on combined into one.

    to simplify the flow, totals were recorded in separate columns (in prev ones, i was just using sum functions for each individual tank data columns, but kinda got tired of explaining where the numbers were coming from, etc.)

    to effectively manage the gas formula switch over for LT summer/winter mileage, i did a manual formula adjustment with summer and winter numbers in different columns to reduce confusion.
     
    #20 DaveinOlyWA, Dec 22, 2009
(You must log in or sign up to post here.)
Page 1 of 2