calculating a rolling mean

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

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

"Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
news:CSjye.23457$Ar5.7935@newsfe5-win.ntli.net...
| 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
|
|
|

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" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
news:OTW4aTPgFHA.572@TK2MSFTNGP15.phx.gbl...
> 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
>
> "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> news:CSjye.23457$Ar5.7935@newsfe5-win.ntli.net...
> | 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
> |
> |
> |
>
>

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



"Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
news:Jwrye.91$Dq.89@newsfe3-gui.ntli.net...
| 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" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
| news:OTW4aTPgFHA.572@TK2MSFTNGP15.phx.gbl...
| > 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
| >
| > "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
| > news:CSjye.23457$Ar5.7935@newsfe5-win.ntli.net...
| > | 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
| > |
| > |
| > |
| >
| >
|
|

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

"ScottO" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
news:%23A58VzTgFHA.2472@TK2MSFTNGP15.phx.gbl...
> 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
>
>
>
> "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> news:Jwrye.91$Dq.89@newsfe3-gui.ntli.net...
> | 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" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
> | news:OTW4aTPgFHA.572@TK2MSFTNGP15.phx.gbl...
> | > Try this ...
> | > Assuming that you want to average all readings from all times of day f
or
> | 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
> | >
> | > "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> | > news:CSjye.23457$Ar5.7935@newsfe5-win.ntli.net...
> | > | 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
> | > |
> | > |
> | > |
> | >
> | >
> |
> |
>
>

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

"Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
news:J2sye.97$Dq.41@newsfe3-gui.ntli.net...
> 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
>
> "ScottO" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in message
> news:%23A58VzTgFHA.2472@TK2MSFTNGP15.phx.gbl...
> > 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
> >
> >
> >
> > "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> > news:Jwrye.91$Dq.89@newsfe3-gui.ntli.net...
> > | 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" <scott_orchard_REMOVETHIS@hotmail.comTHISTOO> wrote in
message
> > | news:OTW4aTPgFHA.572@TK2MSFTNGP15.phx.gbl...
> > | > Try this ...
> > | > Assuming that you want to average all readings from all times of day
f
> or
> > | 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
> > | >
> > | > "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> > | > news:CSjye.23457$Ar5.7935@newsfe5-win.ntli.net...
> > | > | 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
> > | > |
> > | > |
> > | > |
> > | >
> > | >
> > |
> > |
> >
> >
>
>