calculating a rolling mean

Hi all you gurus out there

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

Del,

The following formula will average columns E:K for the row it's in, and the
27 prior. This particular one goes in row 30, and you'd copy it up and down
with the Fill Handle. If you copy it to far up (where there aren't 27 prior
days), you'll start getting goofy stuff:

=AVERAGE(OFFSET(E30,0,0,-28,7))

Change the -28 for a different number of rows (dates) to include in the
average. If you want a month, maybe -30 would be closer.
--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
news:cNjye.4425$qt3.1728@newsfe5-gui.ntli.net...
> Hi all you gurus out there
>
> 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 Earl
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

"Earl Kiosterud" <nothanks@nospam.com> wrote in message
news:OdufamPgFHA.1204@TK2MSFTNGP12.phx.gbl...
> Del,
>
> The following formula will average columns E:K for the row it's in, and
the
> 27 prior. This particular one goes in row 30, and you'd copy it up and
down
> with the Fill Handle. If you copy it to far up (where there aren't 27
prior
> days), you'll start getting goofy stuff:
>
> =AVERAGE(OFFSET(E30,0,0,-28,7))
>
> Change the -28 for a different number of rows (dates) to include in the
> average. If you want a month, maybe -30 would be closer.
> --
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
> news:cNjye.4425$qt3.1728@newsfe5-gui.ntli.net...
> > Hi all you gurus out there
> >
> > 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,

I've read your question a few times, but I just don't quite get it. You
don't say how you "error-trapped" the function. I think maybe this has to
do with rows that don't yet have data in them -- rows for future dates,
perhaps. They're empty, and you don't want errors or incomplete averages in
such calculations for HbA1c . Is that it? Give a little more detail. Give
examples. Give formulas you're using.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
news:yurye.88$Dq.18@newsfe3-gui.ntli.net...
> Thanks Earl
> 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
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message
> news:OdufamPgFHA.1204@TK2MSFTNGP12.phx.gbl...
>> Del,
>>
>> The following formula will average columns E:K for the row it's in, and
> the
>> 27 prior. This particular one goes in row 30, and you'd copy it up and
> down
>> with the Fill Handle. If you copy it to far up (where there aren't 27
> prior
>> days), you'll start getting goofy stuff:
>>
>> =AVERAGE(OFFSET(E30,0,0,-28,7))
>>
>> Change the -28 for a different number of rows (dates) to include in the
>> average. If you want a month, maybe -30 would be closer.
>> --
>> Earl Kiosterud
>> www.smokeylake.com/
>> -------------------------------------------
>>
>> "Delboy" <derek.hoy1nospam@ntlworld.com> wrote in message
>> news:cNjye.4425$qt3.1728@newsfe5-gui.ntli.net...
>> > Hi all you gurus out there
>> >
>> > 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
>> >
>> >
>>
>>
>
>