Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average only the last 3 cells in a row and self-update as added
I have a spreadsheet where I record individual scores on a daily and weekly
basis. The spreadsheet consists of a column of names down the left side to which I add new scores in the row next to each name every time someone competes. (example) John 100 80 90 110 Amy 90 70 90 120 80 etc..... I would like to create a formula which would search down to the end of the populated row and take the average of ONLY the last 3 cells. Optimally, the result would 'auto-update' every time I added scores to the end of the row. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average only the last 3 cells in a row and self-update as added
=AVERAGE(OFFSET(A1,,COUNT(1:1)-2,,3))
"thorshammer" wrote: I have a spreadsheet where I record individual scores on a daily and weekly basis. The spreadsheet consists of a column of names down the left side to which I add new scores in the row next to each name every time someone competes. (example) John 100 80 90 110 Amy 90 70 90 120 80 etc..... I would like to create a formula which would search down to the end of the populated row and take the average of ONLY the last 3 cells. Optimally, the result would 'auto-update' every time I added scores to the end of the row. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average only the last 3 cells in a row and self-update as added
Another...
=AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):IV1) "thorshammer" wrote: I have a spreadsheet where I record individual scores on a daily and weekly basis. The spreadsheet consists of a column of names down the left side to which I add new scores in the row next to each name every time someone competes. (example) John 100 80 90 110 Amy 90 70 90 120 80 etc..... I would like to create a formula which would search down to the end of the populated row and take the average of ONLY the last 3 cells. Optimally, the result would 'auto-update' every time I added scores to the end of the row. Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average only the last 3 cells in a row and self-update as added
On Mon, 11 Aug 2008 12:01:03 -0700, thorshammer
wrote: I have a spreadsheet where I record individual scores on a daily and weekly basis. The spreadsheet consists of a column of names down the left side to which I add new scores in the row next to each name every time someone competes. (example) John 100 80 90 110 Amy 90 70 90 120 80 etc..... I would like to create a formula which would search down to the end of the populated row and take the average of ONLY the last 3 cells. Optimally, the result would 'auto-update' every time I added scores to the end of the row. Is this possible? IF your formula is in column B, and data is entered contiguously in each row (i.e. no blanks) then this should work: =AVERAGE(OFFSET(B1,0,COUNT(C1:IV1),1,-3)) If there might be blanks that should not be counted, then this **array** formula should work: =AVERAGE(TRANSPOSE(INDIRECT(ADDRESS(ROW(),LARGE(IS NUMBER(C1:IV1)*COLUMN(C1:IV1),{1,2,3}))))) To enter an **array** formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. Once entered into B1, either formula can be "filled-down" and the references should adjust properly. As written, the formulas will NOT work if they are not entered in the same row as the data. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
update consolidated sheet when data is added to worksheets | Excel Discussion (Misc queries) | |||
added line won't update accross sheets | Excel Discussion (Misc queries) | |||
Linking a cell to update when rows added | Excel Discussion (Misc queries) |