I am making a new spreadsheet to record daily blood pressure readings. Each
day's readings go into a row below the previous day's readings. I can use
"=average" to get the average for all entries. Is there a function or other
means of getting the average for, for instance, the last 30 entries, which
would be for the last 30 days?

Thanks in advance!

Tim

if your values are continuous with no blanks in the middle then
=AVERAGE(OFFSET(INDIRECT(ADDRESS(1,1)),MATCH(99999 99999,A:A)-30,,30))
will average last 30 values in column A and will give error if you have less
than 30 readings.







"Sick Puppy" <Sick wrote in message
...
I am making a new spreadsheet to record daily blood pressure readings.
Each
day's readings go into a row below the previous day's readings. I can use
"=average" to get the average for all entries. Is there a function or
other
means of getting the average for, for instance, the last 30 entries, which
would be for the last 30 days?

Thanks in advance!

Tim

First, Harkawat, thanks to both you and Domenic for replying -- especially so
quickly!

Next, I hope you and Domenic will both forgive me. I should have been more
descriptive and specific in my initial post.

I actually have it so that I can enter information for each of these time
periods:

Date | Morning | Noon | Afternoon | Evening | Night

To help me explain easier, please open this screen-shot image of my
spreadsheet:
http://home.earthlink.net/~wiff.them/Image1.jpg

As you can see (hopefully), "Date" is a single, date formatted column.

The other, time-of-day items are actually headings which are centered across
4 columns. The 4 columns are not labeled, but a

Systolic (higher BP reading) | / | Diastolic (lower BP reading) | Pulse

On any given day, I may only take Blood Pressure readings in one, two,
three, etc., of the daily time periods. I also might skip taking readings
for a day -- or skip taking readings for several days.

Because of these situations, I would end up with occasional blank cells in
the range to be averaged, and it also might be that I would not have entries
for 30 consecutive days. I suppose it could be that I might only have 5 rows
of entries that extend back 30 days and should be averaged.

The key is that I want to be able t average all the readings I took during
the previous 30 days, but no further back.

I hope I am being clearer now.

THANKS AGAIN for your assistance!!!

Tim

From: The Mountain, Cough Drop Div.
···· Date & Time Composed ····
June 22, 2005
2:54 pm EASTERN STANDARD TIME -- USA

Try the following...

1) Select B11

2) Define the following ranges:

Insert Define Name

Name: Date

Refers to:
=Sheet1!$A$14:INDEX(Sheet1!$A$14:$A$65536,MATCH(9. 99999999999999E+307,She
et1!$A$14:$A$65536))

Click Add

Name: Readings

Refers to:
=Sheet1!B14:INDEX(Sheet1!B14:B65536,MATCH(9.999999 99999999E+307,Sheet1!$A
$14:$A$65536))

Click Ok

3) Enter the following formula in B11 and copy across:

=AVERAGE(IF((DateTODAY()-30)*(Readings0),Readings))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

4) Use conditional formatting to hide #DIV/0! error values that will
result when there's no data. If you need help with this, post back.

Hope this helps!

In article ,
"Sick Puppy" wrote:

First, Harkawat, thanks to both you and Domenic for replying -- especially so
quickly!

Next, I hope you and Domenic will both forgive me. I should have been more
descriptive and specific in my initial post.

I actually have it so that I can enter information for each of these time
periods:

Date | Morning | Noon | Afternoon | Evening | Night

To help me explain easier, please open this screen-shot image of my
spreadsheet:
http://home.earthlink.net/~wiff.them/Image1.jpg

As you can see (hopefully), "Date" is a single, date formatted column.

The other, time-of-day items are actually headings which are centered across
4 columns. The 4 columns are not labeled, but a

Systolic (higher BP reading) | / | Diastolic (lower BP reading) | Pulse

On any given day, I may only take Blood Pressure readings in one, two,
three, etc., of the daily time periods. I also might skip taking readings
for a day -- or skip taking readings for several days.

Because of these situations, I would end up with occasional blank cells in
the range to be averaged, and it also might be that I would not have entries
for 30 consecutive days. I suppose it could be that I might only have 5 rows
of entries that extend back 30 days and should be averaged.

The key is that I want to be able t average all the readings I took during
the previous 30 days, but no further back.

I hope I am being clearer now.

THANKS AGAIN for your assistance!!!

Tim

From: The Mountain, Cough Drop Div.
···· Date & Time Composed ····
June 22, 2005
2:54 pm EASTERN STANDARD TIME -- USA

Domenic!

Whoa!!! Cool! It works!

I actually had 4 sheets in the workbook (one for each person in my family
that I'm tracking), so I had to modify the ~Sheet1~ to being ~'Family Member
Name'~, but I managed to figure that out.

I also wasn't familiar with the CTRL-SHIFT-ENTER thing. From what I can see
in the Help file, it is for arrays. This is my first array.

I'll see if I can figure out where the conditional formatting ~if~
statements go to get rid of the errors. At first I guessed that they would
be before the { symbol, but that doesn't seem to be right. If you want to
give me a clue as to where to put them, that would be great.

THANKS AGAIN!

Tim

From: The Mountain, Cough Drop Div.
···· Date & Time Composed ····
June 22, 2005
5:54 pm EASTERN STANDARD TIME -- USA

For conditional formatting, try the following...

1) Select B11

2) Format Conditional Formatting Formula Is:

=ISERR(B11)

3) Choose 'White' as your font color

4) Click Ok

5) Copy format to other cells using either the 'Format Painter' or 'Copy
Paste Special Formats'.

Hope this helps!

In article ,
"Sick Puppy" wrote:

Domenic!

Whoa!!! Cool! It works!

I actually had 4 sheets in the workbook (one for each person in my family
that I'm tracking), so I had to modify the ~Sheet1~ to being ~'Family Member
Name'~, but I managed to figure that out.

I also wasn't familiar with the CTRL-SHIFT-ENTER thing. From what I can see
in the Help file, it is for arrays. This is my first array.

I'll see if I can figure out where the conditional formatting ~if~
statements go to get rid of the errors. At first I guessed that they would
be before the { symbol, but that doesn't seem to be right. If you want to
give me a clue as to where to put them, that would be great.

THANKS AGAIN!

Tim

From: The Mountain, Cough Drop Div.
···· Date & Time Composed ····
June 22, 2005
5:54 pm EASTERN STANDARD TIME -- USA

Domenic,

I've got the ~if~ statement covered now. THANKS!

Another way...

=AVERAGE(OFFSET(A1,COUNTA(A:A)-30,0,30))

Also assumes no blanks.

Hope this helps!

In article ,
"Sick Puppy" <Sick wrote:

I am making a new spreadsheet to record daily blood pressure readings. Each
day's readings go into a row below the previous day's readings. I can use
"=average" to get the average for all entries. Is there a function or other
means of getting the average for, for instance, the last 30 entries, which
would be for the last 30 days?

Thanks in advance!

Tim