Google-sheets – Compute average over month when measurement is irregular

google sheets

I want to measure my power consumption. So once in a month I note my meter reading.

So I got the following data:

2012-12-01| 893.8|  |    |31
2013-01-05| 977.2|34|2.45|31
2013-02-01|1052.2|26|2.88|28
2013-03-06|1165.3|35|3.23|31
2013-04-07|1263.6|31|3.17|30
2013-05-03|1335.9|26|2.78|31

Where…

  1. Date of reading
  2. kWh
  3. days in between readings
  4. delta since last reading
  5. number of days of reading month.

So I would like to compute the average over a month. Any Ideas how to do that?

Best Answer

The formula I would use (assuming this started in column A) would be this:

=(B2-B1)/C2*E2

This will find the change in kWh since the last reading, divide it by the number of days in the reading (to get the daily average) then multiply it by the number of days in that month.

Since you have already calculated the daily average (in column 4) you could just do 

=D2*E2

and you would get the same results. If you were trying to find a general month average (not tied to specific days in each month) you could just take column 4 * 30 (=D2*30).

Related Topic