calculating a rolling mean

[Delboy]

Hi all you gurus out there - cross posted from mpe.general

I am a diabetic and as part of my spreadsheet recording blood glucose
reading, I have Column B - Dates (01/01/05-31/12/05) Columns E-K will hold
blood glucose readings at different times of day (e.g. waking, 2 hrs after
breakfast etc etc)

There is a value called HbA1c which may be calculated from the average of
the last 2 weeks or 4 weeks of all readings.

So from today's readings I wish to average Row 04/07/05, cols E-K to Row
20/06/05 cols E-K; and average Row 04/07/05, cols E-K to Row 06/06/05 cols
E-K. (Ideally should be 1 month, but I can live with 4 weeks)

Can any body out there help me? You will have to spoon feed me as I am "a
bear of little brain" to quote Winnie the Pooh.

TIA

--
Delboy

A common mistake that people made when trying to design something completely
foolproof was to underestimate the ingenuity of complete fools.

Douglas Adams

 

[ScottO]

Try this ...
Assuming that you want to average all readings from all times of day for the previous 28 days,
put this formula into L29

=AVERAGE(OFFSET(K29,-27,-6,28,7))

You can then copy this down as far as you need.
Rgds,
ScottO

| Hi all you gurus out there - cross posted from mpe.general
|
| I am a diabetic and as part of my spreadsheet recording blood glucose
| reading, I have Column B - Dates (01/01/05-31/12/05) Columns E-K will hold
| blood glucose readings at different times of day (e.g. waking, 2 hrs after
| breakfast etc etc)
|
| There is a value called HbA1c which may be calculated from the average of
| the last 2 weeks or 4 weeks of all readings.
|
| So from today's readings I wish to average Row 04/07/05, cols E-K to Row
| 20/06/05 cols E-K; and average Row 04/07/05, cols E-K to Row 06/06/05 cols
| E-K. (Ideally should be 1 month, but I can live with 4 weeks)
|
| Can any body out there help me? You will have to spoon feed me as I am "a
| bear of little brain" to quote Winnie the Pooh.
|
| TIA
|
| --
| Delboy
|
| A common mistake that people made when trying to design something completely
| foolproof was to underestimate the ingenuity of complete fools.
|
| Douglas Adams
|
|
|

 

[Delboy]

Thanks Scott0
Now I've seen the function, I can tailor it after making a few more
amendments.
A supplementary question if I may. The AVERAGE(OFFSET(x,x,x,x,x)) function
has been put into a not normally seen column off to the right; call it Col
P. I have error trapped the function to return a null string in that cell if
no data entered. (data will normally be entered to cells (E today's date to
K today's date) and added 31 rows for December 2004 to give me a value on
01/01.05. How can I read the last value in that column to another cell? This
last value is not necessarily max or min, just the last in date order.
TIA

--
Delboy

A common mistake that people made when trying to design something completely
foolproof was to underestimate the ingenuity of complete fools.

Douglas Adams

 

[ScottO]

One way (not necessarily the best) would be a formula to find the largest date in your date column
(I'll assume Col A), and then return the rolling mean value from column P that's on the same row.
Try putting this formula in a cell that's NOT in Col A ...

=index($P:$P,match(max($A:$A),$A:$A,0))

Rgds,
ScottO



| Thanks Scott0
| Now I've seen the function, I can tailor it after making a few more
| amendments.
| A supplementary question if I may. The AVERAGE(OFFSET(x,x,x,x,x)) function
| has been put into a not normally seen column off to the right; call it Col
| P. I have error trapped the function to return a null string in that cell if
| no data entered. (data will normally be entered to cells (E today's date to
| K today's date) and added 31 rows for December 2004 to give me a value on
| 01/01.05. How can I read the last value in that column to another cell? This
| last value is not necessarily max or min, just the last in date order.
| TIA
|
| --
| Delboy
|
| A common mistake that people made when trying to design something completely
| foolproof was to underestimate the ingenuity of complete fools.
|
| Douglas Adams
|
| | > Try this ...
| > Assuming that you want to average all readings from all times of day for
| the previous 28 days,
| > put this formula into L29
| >
| > =AVERAGE(OFFSET(K29,-27,-6,28,7))
| >
| > You can then copy this down as far as you need.
| > Rgds,
| > ScottO
| >
| > | > | Hi all you gurus out there - cross posted from mpe.general
| > |
| > | I am a diabetic and as part of my spreadsheet recording blood glucose
| > | reading, I have Column B - Dates (01/01/05-31/12/05) Columns E-K will
| hold
| > | blood glucose readings at different times of day (e.g. waking, 2 hrs
| after
| > | breakfast etc etc)
| > |
| > | There is a value called HbA1c which may be calculated from the average
| of
| > | the last 2 weeks or 4 weeks of all readings.
| > |
| > | So from today's readings I wish to average Row 04/07/05, cols E-K to Row
| > | 20/06/05 cols E-K; and average Row 04/07/05, cols E-K to Row 06/06/05
| cols
| > | E-K. (Ideally should be 1 month, but I can live with 4 weeks)
| > |
| > | Can any body out there help me? You will have to spoon feed me as I am
| "a
| > | bear of little brain" to quote Winnie the Pooh.
| > |
| > | TIA
| > |
| > | --
| > | Delboy
| > |
| > | A common mistake that people made when trying to design something
| completely
| > | foolproof was to underestimate the ingenuity of complete fools.
| > |
| > | Douglas Adams
| > |
| > |
| > |
| >
| >
|
|

 

[Delboy]

Don't think so Scott
The sheet is already pre filled with dates now from 01/12/04-31/12/05 so all
I have to do is fill data into cols e-k along today's date row.
Another idea? ;-)

--
Delboy

A common mistake that people made when trying to design something completely
foolproof was to underestimate the ingenuity of complete fools.

Douglas Adams

 

[Delboy]

Yayyyyy! ScottO

Got it Sussed.
Dates 01/12/04-31/12/05 are in Col B.
Cols E-K contain data
Cols M & N contain 28 day & 14 day rolling means
Col O contains formula =IF(SUM(Exx:Kxx)=0,"",B147) which returns day code in
Oxx where there is data
Cells E11 & E12 contain the formula
=2.16+(0.505*INDEX($M:$M,MATCH(MAX($O:$O),$O:$O,0))) &
=2.16+(0.505*INDEX($M:$M,MATCH(MAX($O:$O),$O:$O,0)))

Thanks a bunch

--
Delboy

A common mistake that people made when trying to design something completely
foolproof was to underestimate the ingenuity of complete fools.

Douglas Adams