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.

-
I was working on a solution since 18/11/2012. After a while I got lost in the reading and logic. Can you add an example file, with the expected outcome (hard coded). – Jacob Jan Tuinstra Apr 24 '13 at 19:26