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