calculating a rolling mean

ScottO · 07-05-2005, 05:05 AM

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
| > |
| > |
| > |
| >
| >
|
|

Delboy · 07-05-2005, 06:05 AM

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
> | > |
> | > |
> | > |
> | >
> | >
> |
> |
>
>

Delboy · 07-05-2005, 08:05 AM

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
> > | > |
> > | > |
> > | > |
> > | >
> > | >
> > |
> > |
> >
> >
>
>