Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
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

1 Answer 1

Till date the best tool for sorting and grouping would be Excel, with Google docs there are many concerns.I guess you haven't heard about this tool called CollateBox http://www.collatebox.com/ which does filtering and sorting in much simpler way than Google docs o even Excel for that matter. Have a look at this, revert back with ur inputs about this one.

share|improve this answer
Please review the FAQ - particularly regarding self promotion. This answer could be viewed as spam. –  Barry Mar 27 '12 at 6:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.