Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Get aveages for last few days
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Domenic,
I've got the ~if~ statement covered now. THANKS! |
#9
|
|||
|
|||
I used
=IF(N14:N65536=0,"",AVERAGE(IF((DateTODAY()-30)*(Readings0),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 |
#10
|
|||
|
|||
Try...
=IF(SUM((DateTODAY()-30)*Readings),AVERAGE(IF((DateTODAY()-30)*(Reading s0),Readings)),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sick Puppy" wrote: I used =IF(N14:N65536=0,"",AVERAGE(IF((DateTODAY()-30)*(Readings0),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 |
#11
|
|||
|
|||
Domenic,
OK... as long as you are into this... Why isn't the formula =COUNT(IF((DateTODAY()-30)*(Readings0),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 |
#12
|
|||
|
|||
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.999 99999999999E+307,Sheet1 !$A$14:$A$65536)) Click OK Hope this helps! In article , "Sick Puppy" wrote: Domenic, OK... as long as you are into this... Why isn't the formula =COUNT(IF((DateTODAY()-30)*(Readings0),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 |
#13
|
|||
|
|||
Ahhhhh....
I'm an idiot. Thanks again! Tim "Domenic" wrote: 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.999 99999999999E+307,Sheet1 !$A$14:$A$65536)) Click OK Hope this helps! In article , "Sick Puppy" wrote: Domenic, OK... as long as you are into this... Why isn't the formula =COUNT(IF((DateTODAY()-30)*(Readings0),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 |
#14
|
|||
|
|||
In article ,
"Sick Puppy" wrote: Thanks again! Tim You're very welcome, Tim! I should have given you that last formula in the first place. :) |
#15
|
|||
|
|||
"Domenic" wrote:
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,'Je nnifer 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 |
#16
|
|||
|
|||
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:A14TODAY()-30)*(B14:B140),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((DateJNTODAY()-30)*(ReadingsJN0),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Sick Puppy" wrote: "Domenic" wrote: 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,'Je nnifer 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert # of days to years/days | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
HOW TO CALCULATE THE DAYS? | Excel Worksheet Functions | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |