| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
always calculate last 5 rows even after 1 inserted
i want to always calculate the average of the bottom 5 rows of a longer list
even after a new row is inserted and the list is resorted. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
=AVERAGE(INDEX(A:A,MATCH(10^10,A:A)-4):INDEX(A:A,MATCH(10^10,A:A)))
"Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
This didn't work. it didn't even return the correct result before adding a row.
"Teethless mama" wrote: =AVERAGE(INDEX(A:A,MATCH(10^10,A:A)-4):INDEX(A:A,MATCH(10^10,A:A))) "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
Hi,
In 2003 and earlier you could use this =SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{1,2,3,4,5})-1,)))/5 In 2007 this fails. -- Thanks, Shane Devenshire "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
It's not an elegant formula:
=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{1,2,3,4,5})-1,)))/5 Here is an elegant solution, but also works for XL-2007 =SUM(OFFSET(INDIRECT("A"&ROWS(A1:A20)),,,-5))/5 "ShaneDevenshire" wrote: Hi, In 2003 and earlier you could use this =SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{1,2,3,4,5})-1,)))/5 In 2007 this fails. -- Thanks, Shane Devenshire "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A 20<""),{1,2,3,4,5})-1,)))/5
In 2007 this fails. Very interesting. I wonder why? It should. In 2007 the SUM function isn't accepting the results of OFFSET but it does in Excel 2002 (my other version). This works in 2007: =SUMPRODUCT(N(OFFSET(A1,LARGE(ROW(A1:A21)*(A1:A21< ""),{1,2,3,4,5})-1,)))/5 I'm going to post this in the private ng. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, In 2003 and earlier you could use this =SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{1,2,3,4,5})-1,)))/5 In 2007 this fails. -- Thanks, Shane Devenshire "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
That worked like a charm, thank you.
"ShaneDevenshire" wrote: Hi, In 2003 and earlier you could use this =SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{1,2,3,4,5})-1,)))/5 In 2007 this fails. -- Thanks, Shane Devenshire "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
Hi,
Back again with a 2007 solution: =AVERAGE(INDIRECT("A"&MAX((A1:A21<"")*ROW(A1:A21) )-4&":A"&MAX((A1:A21<"")*ROW(A1:A21)))) This formula must be array entered - press Shift+Ctrl+Enter to enter it. -- Thanks, Shane Devenshire "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
Try......
=SUM(OFFSET(INDIRECT("A"&ROWS(A1:A20)),,,-5))/5 Regards Bosco "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
always calculate last 5 rows even after 1 inserted
That worked great as well, and a lot cleaner, thank you.
"bosco_yip" wrote: Try...... =SUM(OFFSET(INDIRECT("A"&ROWS(A1:A20)),,,-5))/5 Regards Bosco "Picman" wrote: i want to always calculate the average of the bottom 5 rows of a longer list even after a new row is inserted and the list is resorted. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transpose: many columns to inserted rows | Excel Discussion (Misc queries) | |||
| unwanted links come when new rows are inserted | Links and Linking in Excel | |||
| Weird formatting on inserted rows | Excel Discussion (Misc queries) | |||
| Unknown formatting on inserted rows...? | Excel Discussion (Misc queries) | |||
| Inserted Rows not re-calculating | Excel Discussion (Misc queries) | |||
Hybrid Mode
