Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
I'm hoping someone can help me figure out a way to create an Excel
worksheet that will help me to keep track of my daily glucose logs and give me the average of the last 7 days and for the last 30 days. As time goes on, I'll add more columns to the excel sheet to represent add'l dates. The table below is meant to serve as an example of such an excel sheet cells A2 and B2 are the cells I'm trying to put these averages in. If today is 7/16/06, A2 should show the averages of cells D4 through K10--but exclude from the average any EMPTY cells. It seems like there should be a way--but I'm just not seeing it. Thanks. Don I NOTE: Regarding the table that I spoke of above, I tried to show a table to illustrate what I'm asking about, but my reader won't allow me to do it. The following link shows the table in question--and nothing else. No images--no banner ads nothing but the table. http://www.donireland.com/ExcelFormu...Averaging.html |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
Don I wrote:
I'm hoping someone can help me figure out a way to create an Excel worksheet that will help me to keep track of my daily glucose logs and give me the average of the last 7 days and for the last 30 days. As time goes on, I'll add more columns to the excel sheet to represent add'l dates. The table below is meant to serve as an example of such an excel sheet cells A2 and B2 are the cells I'm trying to put these averages in. If today is 7/16/06, A2 should show the averages of cells D4 through K10--but exclude from the average any EMPTY cells. Hi Don, Strange table... In Excel we have letters for columns and numbers for rows, but your table has letters for rows and numbers for columns... You need just the AVERAGE function: just type (I'll use the references from the table you linked, but they should not correspond to the real references in Excel...) in A2: =AVERAGE(D4:K10) press Enter, select again A2 and drag the cell till A10. As you can see the references will change in each cell. The same is for a 30 days average. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
See if this get you headed in the right direction...
With Dates entered in B3:AZ3 (in ascending order) Blood sugar values in B4:AZ11 A1: 7 day average B1: =AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,7),B3 :AZ3,0)):INDEX(B4:AZ4,1,MATCH(LARGE(B3:AZ3,1),B3:A Z3,0))) A2: 30 day average B2: =AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,MIN(C OUNT(B3:AZ3),30)),B3:AZ3,0)):INDEX(B4:AZ4,1,MATCH( LARGE(B3:AZ3,1),B3:AZ3,0))) Note: Since text wrap will impact those formulas, they DO NOT contain any blanks. Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Don I" wrote: I'm hoping someone can help me figure out a way to create an Excel worksheet that will help me to keep track of my daily glucose logs and give me the average of the last 7 days and for the last 30 days. As time goes on, I'll add more columns to the excel sheet to represent add'l dates. The table below is meant to serve as an example of such an excel sheet cells A2 and B2 are the cells I'm trying to put these averages in. If today is 7/16/06, A2 should show the averages of cells D4 through K10--but exclude from the average any EMPTY cells. It seems like there should be a way--but I'm just not seeing it. Thanks. Don I NOTE: Regarding the table that I spoke of above, I tried to show a table to illustrate what I'm asking about, but my reader won't allow me to do it. The following link shows the table in question--and nothing else. No images--no banner ads nothing but the table. http://www.donireland.com/ExcelFormu...Averaging.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
Thanks I think that'll give me a pretty good start. Just needs a little
tweaking. Ron Coderre wrote: See if this get you headed in the right direction... With Dates entered in B3:AZ3 (in ascending order) Blood sugar values in B4:AZ11 A1: 7 day average B1: =AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,7),B3 :AZ3,0)):INDEX(B4:AZ4,1,MATCH(LARGE(B3:AZ3,1),B3:A Z3,0))) A2: 30 day average B2: =AVERAGE(INDEX(B11:AZ11,1,MATCH(LARGE(B3:AZ3,MIN(C OUNT(B3:AZ3),30)),B3:AZ3,0)):INDEX(B4:AZ4,1,MATCH( LARGE(B3:AZ3,1),B3:AZ3,0))) Note: Since text wrap will impact those formulas, they DO NOT contain any blanks. Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Don I" wrote: I'm hoping someone can help me figure out a way to create an Excel worksheet that will help me to keep track of my daily glucose logs and give me the average of the last 7 days and for the last 30 days. As time goes on, I'll add more columns to the excel sheet to represent add'l dates. The table below is meant to serve as an example of such an excel sheet cells A2 and B2 are the cells I'm trying to put these averages in. If today is 7/16/06, A2 should show the averages of cells D4 through K10--but exclude from the average any EMPTY cells. It seems like there should be a way--but I'm just not seeing it. Thanks. Don I NOTE: Regarding the table that I spoke of above, I tried to show a table to illustrate what I'm asking about, but my reader won't allow me to do it. The following link shows the table in question--and nothing else. No images--no banner ads nothing but the table. http://www.donireland.com/ExcelFormu...Averaging.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
<<Strange table...
In Excel we have letters for columns and numbers for rows, but your table has letters for rows and numbers for columns... Yes I realized that after I saw Ron's reply. The problem with average (yes I saw it before posting and tried it) is that if one of the cells in the range is blank, it's included in the average as a ZERO. Also, I only want to include the last 7 or 30 days worth of numbers. Ron's answer does what I'm looking for--but I found out after the fact that Docs To Go doesn't seem to support the "LARGE" function which is part of Ron's solution. -- Don Ireland "Franz Verga" wrote in message ... Don I wrote: I'm hoping someone can help me figure out a way to create an Excel worksheet that will help me to keep track of my daily glucose logs and give me the average of the last 7 days and for the last 30 days. As time goes on, I'll add more columns to the excel sheet to represent add'l dates. The table below is meant to serve as an example of such an excel sheet cells A2 and B2 are the cells I'm trying to put these averages in. If today is 7/16/06, A2 should show the averages of cells D4 through K10--but exclude from the average any EMPTY cells. Hi Don, Strange table... In Excel we have letters for columns and numbers for rows, but your table has letters for rows and numbers for columns... You need just the AVERAGE function: just type (I'll use the references from the table you linked, but they should not correspond to the real references in Excel...) in A2: =AVERAGE(D4:K10) press Enter, select again A2 and drag the cell till A10. As you can see the references will change in each cell. The same is for a 30 days average. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to Calc Daily Averages?
Don I wrote:
<<Strange table... In Excel we have letters for columns and numbers for rows, but your table has letters for rows and numbers for columns... Yes I realized that after I saw Ron's reply. The problem with average (yes I saw it before posting and tried it) is that if one of the cells in the range is blank, it's included in the average as a ZERO. Also, I only want to include the last 7 or 30 days worth of numbers. Are you sure? AFAIK the AVERAGE function ignores blank cells, logical values and text, while the AVERAGEA function uses also these values... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
The Excel cells are not automatically calc. existing formula. | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |