I have a spreadsheet pulling together various readings taken of hours heater has run, electricity units use for that period, and outside temperature.
For the last 3 months, the electricity usage and heater hours have been taken every Monday. For 5 months before that, it was taken randomly between 5 days and a nearly a month in one case.
The external average temperature is taken daily and added to the spreadsheet.
I'd now like to display this data as broken down into 7 day weeks, and summarised by month. Having just about wrapped my head around the differences between Google Charts, Gadgets and Visualization APIs, I know that the display of it can be done.
Where I'm struggling is grouping the daily temperature readings into weekly averages, the old "non-weekly" readings into weeks, and the fact that a Monday isn't always the start of the month and a month isn't always x amount of days.
Thankfully, Google Docs makes the difference between dates as easy as A2-A1. And there's the WEEKDAY(number, type) which returns the day of the week for the given number (date value).
Date #days kWh Reading Day 15/04/2011 1 6 Tue 10/05/2011 25 152 Fri 15/05/2011 5 28 Sun
I know how many days there are between 15/04 and 10/05 (UK dd/mm/yyyy date format!) and I know how much that makes the average to be; what I'd like to end up with is:
Date #days daily av. kWh Reading Day 18/04/2011 1 6 Mon 18/04/2011 7 6.08 Mon 02/05/2011 7 6.08 Mon 09/05/2011 7 6.08 Mon
And this is where I'm failing. I can round to the nearest week, I can average 7 days of temperature to a week, but turning random ranges into neat weeks and week into neat calendar months is melting my brain.
I'm happy to sacrifice a few days data in 04/11 to "normalize" it to a new week. I realise that the "granularity" of the few readings won't be as good as the new weekly readings.
Given that this is a "one-off" correction, if it needs to be done outside Spreadsheets, either externally or tediously manually, then so be it. But I feel there must be a function that can support this.