I'm calculating the electric consumption for an PHEV car based on each trip data.
I'm calculating each trip kWh/100km and after that I'm averaging the consumption to get the data for an entire month.
But I'm getting some differences between this result and when I'm actually calculating the sum of each trip and after that I apply the formula.
This is how some of my data looks like:
What is most important is Total Electric km & Trip Energy kWh.
For each trip I use the following formular to calculate kWh/100km: =IF(P100<>0,100*R100/P100,0) (100*"Trip Energy kWh"/"Total Electric km").
So, for example my trip for 29th of May is of 37.8 Electric km and it used 8.06 kWh for this trip. To calculate the electric consumption in "kWh/100km" I use the formula 100 * 8.06 / 37.8 = 21.32 kWh/100km.
The final formula I use for the entire month is actually with an exclusion of values bigger than 100 as I have some inputs that do not really make sense (I suspect some erroneous data as I have 800kWh/100km values). The formula I used is: =AVERAGEIF(V3:V118, "<100")
But what is really curious to me is to why my average is 23.50 and if I apply the same formula to the total, I get 12.39 instead (100 * 127 / 1025.3 = 12.39 kWh/100km)
Do I have mistakes in my formulas? Did I approach this from a wrong angle? How can I make sure which data is actually correct?