calculating a rolling mean

[Delboy]

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

 

[Earl Kiosterud]

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.

 

[Delboy]

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]

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.