PIP & MPG Spreadsheet

ukr2
As I said in post #19, the drinnovation formula is not working correctly and the reason is that someone moved it from the place dr put it to the top of the sheet!

If you want, I can fix it back to where dr put it originally, so it will calculate correctly.

 

I've never used it either.. so I looked it up.. very interesting.. I'm no spreadsheet jockey.. but always good to know

MS Excel: DSum Function

 

I added my data from an iPad and it did not perform the calculations. If someone can correct that, I would appreciate it (Work computer blocks google docs).

 

ukr2,

1. I see that you (or somebody else) fixed the formula for fleet average and it is working properly now.

2. I see that someone (maybe you) added a simple formula (currently in row 59) to do same calculation, however, unlike the dr formula this simple formula will not work correctly if the data will be sorted in a different way it is sorted now and also with a new entry that is missing data. So, I think this simple formula should be deleted from sheet.

 

Seems people figured out Dsum


Since someone alsed for other useful things, I went ahead and added a formulae for fleet average of EV Miles/kWh and EV %
With 3 examples people can probably see how to add whatever else they want). Enjoy

 

Hmm looking at it some use 36 something for MPGe, but the EPA formula is based on 33.7kwh per gallon.

Also the formula for overall MPG Equiv is incorrect. needs to be a harmonic mean or computed directly from the formula
33.7*Total_Miles/(33.7*gas_used+kwh_used), so ely105 should be 57.29

Would you like me to correct these?

 

Agree that if we want to compare results to EPA ratings (e.g. if ely105 wants to know how he is doing compared to EPA sticker) we should use EPA figure for energy content of 1 gallon of gasoline (33.7 kWh).
Furthermore, since kWh report by the car is 'consumed', the charging losses (15%?) should be factored in to stay compatible with EPA, and column Q header should then say: WtW EV MPGe.

Agree that the formula for overall MPG(e) needs to be corrected.

Having said that, we should bear in mind that these MPG(e)s are at the consumer end only (Pump to Wheels and Wall to Wheels) and are good for costs calculations and comparisons.
More detailed analysis of efficiencies and GHG emissions should take into account total efficiency and total GHG emissions (Well to Wheels).

 

Yes since primary point of MPGe is for cross-vehicle comparison it needs to be in the proper units. Since MPGe is, as EPA uses it, a mixture of walls-to-wheels and tank-to-wheels, calling it wtw makes no sense.

Good points on the charging loss..

I just went ahead and corrected the formulas to use 33.7 and wherever it used kwh to use (1.15*kwh)
Charger inefficiency may be closer to 20% but at least this is reasonably close. Anyone know the actual efficiency for a PiP? So far I've seen kill-a-watt estimates but none comparing the kill-a-watt to the internal display..

 

drinnovation,

You have to correct formula at T1 (Fleet average composite MPGe) as it is wrong as well.

 

I did not do the formula for T1, but I just fixed it as well as q1

 

Thanks, its now working correctly.
And I did not want to imply you did them, just asked you to fix them.

 

I'd probably forget and while I trust google's revisions, if you want a download copy you should be able to use google scripts that downloads them for you every day at a regular time.

Checkout
ExampleScripts - googlecl - Example commands and tasks GoogleCL can do. - Command line tools for the Google Data APIs - Google Project Hosting

If you want to keep doing it long term, maybe better if you set that up for yourself both for the trip and for the long term.