Google-sheets – Chart usage per interval from periodic readings

google sheetsgoogle-sheets-charts

I have a series of monotonically increasing values associated with arbitrary dates. In this case, they're meter readings, but could be other values like mileage, fuel usage, etc.

29/05/2015  844.79
01/07/2015  852.20
21/07/2015  856.26

Is it possible to chart this data as a "usage per month" type value rather than an ever increasing value?

I understand it won't be accurate without all the data points, but it should be possible to extrapolate from the points that are there.

Best Answer

To find the approximate usage, say, in June 2015, I would look at the last reading before this month began and at the first reading after it ended; then pro-rate the use accordingly. This guarantees the usage estimate will be based on an interval that's at least a month long.

I used the Match command to determine the relevant rows for each month, then Indirect to access the data. (Another possibility is Vlookup, but it's a bit too rigid for the present problem.) In the sample below:

  • column C contains the first day of each month (which can be generated with Date), with custom formatting that shows only the month and year.
  • column D has formula =MATCH(C2-1,A:A), etc. This is the last entry in A column preceding the month in column C.
  • column E is =TO_TEXT(C2), etc: this conversion to text is needed so that the chart comes out with month-based labels, and not with some other dates chosen by software.
  • column F contains the logic of computation: for example, F2 is 

=(INDIRECT("B"&D3+1)-INDIRECT("B"&D2))/(INDIRECT("A"&D3+1)-INDIRECT("A"&D2))

meaning that one should take the first reading after the month ended, and subtract the last reading from before it began; then divide by the corresponding dates. 

+-----------+--------+-----------+-------+------------+-------+
|   Date    | Meter  |   Month   | Match | Text Month | Usage |
+-----------+--------+-----------+-------+------------+-------+
| 4/25/2015 | 830    | May 15    |     2 | May 15     | 0.33  |
| 5/25/2015 | 844.79 | June 15   |     3 | June 15    | 0.20  |
| 7/1/2015  | 852.2  | July 15   |     3 | July 15    | 0.50  |
| 7/21/2015 | 856.26 | August 15 |     5 | August 15  |       |
| 8/3/2015  | 880    |           |       |            |       |
+-----------+--------+-----------+-------+------------+-------+

The above is also available as a shared spreadsheet.

Related Topic