Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #480 www.energyefficientbuild.com
user digitaltoast said:
Normalizing and grouping dates and ranges of days into weeks and calendar months
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 kWh Reading Day
18/04/2011 1 6 Mon
18/04/2011 7 6.08 Mon
02/05/2011 7 6.08 Mon
09/04/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 fews 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. Thanks - first "webapps" post so please be gentle :)
---------------------------------------------------
I am not quite sure about the data that digitaltoast has presented ...
so I have made some assumptions about the data ... but that should not affect the approach for solution to the problem ... so here we go
user digitaltoast said:
Normalizing and grouping dates and ranges of days into weeks and calendar months
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 kWh Reading Day
18/04/2011 1 6 Mon
18/04/2011 7 6.08 Mon
02/05/2011 7 6.08 Mon
09/04/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 fews 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. Thanks - first "webapps" post so please be gentle :)
---------------------------------------------------
I am not quite sure about the data that digitaltoast has presented ...
so I have made some assumptions about the data ... but that should not affect the approach for solution to the problem ... so here we go
No comments:
Post a Comment