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

Made an Excel Chart You Guys Might Be Interested In

Discussion in 'Gen 1 Prius Plug-in 2012-2015' started by Roadburner440, Dec 15, 2011.

  1. Roadburner440

    Roadburner440 Member

    Joined:
    Feb 19, 2011
    316
    59
    0
    Location:
    Hampton, VA
    Vehicle:
    2011 Prius
    Model:
    Two
    So I have been contemplating making one of these for awhile, and since I am about to get my power bill I figure now is the time to start so I can see exactly WHAT portion of my bill I can attribute to my Volt. When I initially made the sheet I did not include a gas column, but I got to thinking about you guys so I added one. Now I will put the forumlas here, AND the sheet. I know viruses can be spread so excel so I do not blame people if they don't want to download it. Either way it autocalculates out to 1000 (so basically 3 years of everyday usage), and automatically figures out you overall cost for both electric and gas. Obviously you guys can feel free to rename it to PiP tracker.. I am just going to upload my excel sheet for you guys to mod as you please.

    All you need to do is create a spread sheet, and copy/paste the forumulas into the column letter to get the formula's activated. Just label the columns as I did, or change your start column (in my case it is 3 in all the forumulas) and the end column (1000 on mine).. Apparently the numbers seperated by colons apply the entire operation on every cell in the range in that column.. Pretty neat I think. Is what has taken me so long to make it. Cause till tonight I did not know how.

    Volt Chart.jpg

    View attachment Volt Power Consumption and Cost Tracker.xls
    It will open as is, but I had to change it from a .xlsx to a .xls so it would upload to here. Formulas are below.

    Column D: =(C3:C1000*0.202)+C3:C1000 I factor charging line losses at 20% (high side) since it takes 12.9kwh to recharge the Volt's 10.3kwh of usage.. You guys can make it what you like, but I overestimate rather than under.

    Column E: =D3:D1000*0.145 My electric cost is $0.145. Change to your liking.

    Column H: =F3:F1000*G3:G1000 Just multiplies the gas columns together. I thought gas prices deserved their own changeable column since prices change ALL the time. I will just keep retyping/coping $3.35 till I do wind up filling the Volt.

    Column I: =E3:E1000+H3:H1000 Takes the electric total and adds it to the gas total. Voila! Total drive cost. And you guys get to see my driving cost for the day as the example.

    EDIT: In column E's formula you will see a smiley face. NOT part of the equation, lol.. It is a : and a D together. So that will need to be corrected, but there is no way around it on the forum. :doh:
     
    #1 Roadburner440, Dec 15, 2011
  2. ItsNotAboutTheMoney

    ItsNotAboutTheMoney EditProfOptInfoCustomUser Title

    Joined:
    Jul 18, 2009
    2,287
    460
    0
    Location:
    Maine
    Vehicle:
    2010 Prius
    Model:
    II
    A spreadsheet tip when using tables with ranges:

    If you have a table with summaries that's going to grow you can make make it easy to expand by having a blank row at the bottom of the table that's included in the summary ranges.

    Then, when you want to add a new row to the end of the table you Insert a row before the blank row.

    E.g.

    Say you have:
    Column 1 Column 2 Column 3
    0 A B
    1 1 Date Value
    2 2 10/1/2011 1
    3 3 11/1/2011 2
    4 4 Insert Here <blank>
    5 5 Total =SUM(B2:B4)
    Then when you want to add a new row you highlight row 4 and insert row (Ctrl-+ is the usual keyboard shortcut) and you get:

    Column 1 Column 2 Column 3
    0 A B
    1 1 Date Value
    2 2 10/1/2011 1
    3 3 11/1/2011 2
    4 4 <blank> <blank>
    5 5 Insert Here <blank>
    6 5 Total =SUM(B2:B5)
    It works because the rule is that when you insert within a referenced range the references will be expanded to include the new cells.

    I couldn't help but see your 1000 and thought it was overkill. I prefer copy, insert/paste edit myself. ;)

    BTW what you used are called array formulae. They are an efficient way to repeat a formula across different rows/columns.
     
    #2 ItsNotAboutTheMoney, Dec 15, 2011
    1 person likes this.
  3. Roadburner440

    Roadburner440 Member

    Joined:
    Feb 19, 2011
    316
    59
    0
    Location:
    Hampton, VA
    Vehicle:
    2011 Prius
    Model:
    Two
    Interesting. I suck at making spread sheets. So I pretty much google what I am trying to do, and then tinker with it. Is something to do since I no longer have to work on the truck, lol... I have added a MPG column now as well, and had to get rid of the #DIV/0 error. That was fun. Will have to try your tip though.
     
    #3 Roadburner440, Dec 15, 2011
  4. hill

    hill High Fiber Member

    Joined:
    Jun 23, 2005
    20,173
    8,353
    54
    Location:
    Montana & Nashville, TN
    Vehicle:
    2018 Chevy Volt
    Model:
    Premium
    Good Try. Shouldn't such a spreadsheet be able to calculate the cost of a kWh based on its variability? On tier 1 for example, we will spend appx 10¢/kWh ... but if were at a top tier, we'd be charged about 3x that much. Since the rate to charge will fluctuate turning on whether it's the beginning of the monthly cycle versus the end of the monthly cycle, it'd seem necessary to factor for that. Most months, we have a surplus via PV solar. On the 3 shortest daylight months we sometimes end up giving back maybe a couple hundred kWh's per month, turning on rainy weather variables. For us, it'd be hard to allocate kWh usage on the short daylight months, because it's a toss up as to what part of the usage is via electric heat, charger usage, or Christmas lights - unless you're willing to make a career out of it.

    .
     
    #4 hill, Dec 17, 2011
  5. Roadburner440

    Roadburner440 Member

    Joined:
    Feb 19, 2011
    316
    59
    0
    Location:
    Hampton, VA
    Vehicle:
    2011 Prius
    Model:
    Two
    I could probably make it variable.. I always estimate on the highside, and I just like to have a general idea. I have added a lot of columns to the chart since you guys have last seen it. I added a kwh/mi, and a mi/kwh column just to see how that works out. Also added a $ per mile column as well. To the best of my knowledge my electric rate does not vary at all (I am not on TOU or anything like that).. So until the power bill posts sometime after Christmas I am in the dark as I have only gotten one power bill since I've been here.

    The only problem I could see in adding the electric cost section is that you would have to enter your electric cost everytime you put it in the chart. I will work on this when I get back later though. Going to buy a Kerosene heater today. Got the Natural Gas Bill, and I did not even crank it at all, have felt cold the last month in the house, and so on. Get the bill and it is almost $100 (that is actual bill, no fee's or anything like that. Just straight based on the usage). So I am not giving those people anymore of my money. At least with the 1-K heater like I had in NY I know I will be warm for that amount of money. Wasn't even warm using the central heat really.
     
    #5 Roadburner440, Dec 18, 2011
  6. Roadburner440

    Roadburner440 Member

    Joined:
    Feb 19, 2011
    316
    59
    0
    Location:
    Hampton, VA
    Vehicle:
    2011 Prius
    Model:
    Two
    As a side note Hill.. I see you are talking about making a career out of tracking the PiP's usage... IF it is anything like the Volt it will tell you this information when you shut it down, or have the menu. That is all I am using. When I press the power button it gives me the usage screen, and I note down the miles driven on the charge/kwh usage (and gas miles/gallons used if I need it). I hope the PiP has a screen like that for you guys. Otherwise you are right, and it will be useless to you. The only reason I created the chart is so I could get an idea of the cost portion of the Volt on my power bill (I figure it to be $25-$30, but I just decided I want to know 100%). This is how the Volt displays the info though:
    [​IMG]
    Then my electrical charging loses, and all that stuff are calculated as worst case scenario in my chart. You are right though that some people have variable electric rates. After seeing what has happened to some other Volt owners I refuse to disclose my car to my electric company, and it is actually cheaper to remain on the flat rate anyway in the long run since 95% of my electricity usage is during the day anyway. In the end going to a TOU would force me to only charging the car at night, and after working all day I basically could only use the car for driving to work + maybe picking up a pizza or something.
     
    #6 Roadburner440, Dec 18, 2011
(You must log in or sign up to post here.)