Get aveages for last few days

[Guest]

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

 

[N Harkawat]

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

 

[Domenic]

Another way...

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

Also assumes no blanks.

Hope this helps!

 

[Guest]

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

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]

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.99999999999999E+307,Sheet1!$A
$14:$A$65536))

Click Ok

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

=AVERAGE(IF((Date>TODAY()-30)*(Readings>0),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!

 

[Guest]

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]

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!

 

[Guest]

Domenic,

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

 

[Guest]

I used

=IF(N14:N65536=0,"",AVERAGE(IF((Date>TODAY()-30)*(Readings>0),Readings)))

with a CTRL-SHIFT-ENTER to confirm it and it seemed to work. If I have
trouble I'll try the white text idea -- which, incidentally, is a cool idea.

Hmmmm.... maybe I like the white text way even better, Domenic. I'll give
it a try.

THANKS ONCE MORE!!

Tim

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

 

[Domenic]

Try...

=IF(SUM((Date>TODAY()-30)*Readings),AVERAGE(IF((Date>TODAY()-30)*(Reading
s>0),Readings)),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Domenic,

OK... as long as you are into this... Why isn't the formula

=COUNT(IF((Date>TODAY()-30)*(Readings>0),Readings))

in cell B10, confirmed with a CTRL-SHIFT-ENTER, working? It seems to always
return a value 1 less then I expect.

Tim

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

 

[Domenic]

That's because the row reference in the defined range 'Readings' is
relative and not absolute. Therefore, change the formula to the
following..

Insert > Name > Define

Click on 'Readings'

Refers to:
=Sheet1!B$14:INDEX(Sheet1!B$14:B$65536,MATCH(9.99999999999999E+307,Sheet1
!$A$14:$A$65536))

Click OK

Hope this helps!

 

[Guest]

Ahhhhh....

I'm an idiot.

Thanks again!

Tim

That's because the row reference in the defined range 'Readings' is
relative and not absolute. Therefore, change the formula to the
following..

Insert > Name > Define

Click on 'Readings'

Refers to:
=Sheet1!B$14:INDEX(Sheet1!B$14:B$65536,MATCH(9.99999999999999E+307,Sheet1
!$A$14:$A$65536))

Click OK

Hope this helps!

 

[Domenic]

Thanks again!

Tim

You're very welcome, Tim! I should have given you that last formula in
the first place.

 

[Guest]

You're very welcome, Tim! I should have given you that last formula in
the first place.

But if you would have, Domenic, I wouldn't have learned nearly as much!
<hee, hee>

Now I've got another anomaly that is NOT A BIG DEAL, but I'm curious as to
why it is happening.

I've got 4 spreadsheets in this Workbook. Each sheet is for a different
person in my family. I was editing the formulas and arrays you provided, and
was re-entering information in the second sheet (named "Jennifer Nash") when
I noticed what I will now explain.

Take a look at
http://home.earthlink.net/~wiff.them/Image2.jpg

See the "1" values in cell F10 and in the cells to the right of F10? I
would think they shouldn't be there. Instead, I would think they should be 0.

Now take a look at
http://home.earthlink.net/~wiff.them/Image3.jpg

Note that when I entered the date in cell A15 the value in cell F10 (and the
cells to the right of F10), recalculated to correctly being 0.

In the Image3.jpg you can see the formula I used in cell F10. The Named
array "DateJN" refers to:

='Jennifer Nash'!$A$14:INDEX('Jennifer
Nash'!$A$14:$A$65536,MATCH(9.99999999999999E+307,'Jennifer
Nash'!$A$14:$A$65536))

The Named array "ReadingsJN" refers to:

='Jennifer Nash'!B$14:INDEX('Jennifer
Nash'!B$14:B$65536,MATCH(9.99999999999999E+307,'Jennifer
Nash'!$A$14:$A$65536))

I believe I created both arrays while B11 was the selected cell. (IS THIS
IMPORTANT?)

Anyway, do you have an idea as to why this is happening? PLEASE DON'T try
to come up with a formula or anything to fix this. Even if I don't get it
fixed I can live with it since I would always have more than one day's
entries. Like I said, I'm just very curious as to why this is happening.

THANKS!

Tim

From: The Mountain, Cough Drop Div.
···· Date & Time Composed ····
June 23, 2005
12:09 pm EASTERN STANDARD TIME -- USA

 

[Domenic]

It has to do with the fact that the dynamic ranges defined each evaluate
to 1 Row x 1 Column in size...

=COUNT(IF((A14:A14>TODAY()-30)*(B14:B14>0),B14:B14))

Why this returns 1 when the IF statement is FALSE is a mystery to me.
Hopefully someone can shed some light. Otherwise, I'll post back if I
find an answer.

In the meantime, you could change the formula to the following...

=SUM(IF((DateJN>TODAY()-30)*(ReadingsJN>0),1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!