Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last several values in a column
Oh Wise Ones,
The below array formula finds the average of the last 5 values in a column. How do I modify it to display the last 5 values of the column? =AVERAGE(A65535:INDEX(A1:A65535,LARGE(ROW(1:65535) * (A1:A65535<""),5))) Thanks, Mike |
#2
|
|||
|
|||
Try the following... B1, copied down: =INDEX(A:A,LARGE(IF($A$1:$A$65535<"",ROW($A$1:$A$ 65535)),ROW()-ROW($B$1)+1)) or for the reverse order... =INDEX(A:A,LARGE(IF($A$1:$A$65535<"",ROW($A$1:$A$ 65535)),ROW($B$6)-ROW())) Both these formula need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Mike K Wrote: Oh Wise Ones, The below array formula finds the average of the last 5 values in a column. How do I modify it to display the last 5 values of the column? =AVERAGE(A65535:INDEX(A1:A65535,LARGE(ROW(1:65535) * (A1:A65535<""),5))) Thanks, Mike -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=316181 |
#3
|
|||
|
|||
Domenic,
Oh, that is so beautiful! Many thanks, Mike -----Original Message----- Try the following... B1, copied down: =INDEX(A:A,LARGE(IF($A$1:$A$65535<"",ROW ($A$1:$A$65535)),ROW()-ROW($B$1)+1)) or for the reverse order... =INDEX(A:A,LARGE(IF($A$1:$A$65535<"",ROW ($A$1:$A$65535)),ROW($B$6)-ROW())) Both these formula need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Mike K Wrote: Oh Wise Ones, The below array formula finds the average of the last 5 values in a column. How do I modify it to display the last 5 values of the column? =AVERAGE(A65535:INDEX(A1:A65535,LARGE(ROW(1:65535) * (A1:A65535<""),5))) Thanks, Mike -- Domenic ---------------------------------------------------------- -------------- Domenic's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=316181 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If column A and column B have values how do i get the total in co. | Excel Discussion (Misc queries) | |||
PivotTable Column Field Values | Excel Discussion (Misc queries) | |||
Finding the max of a column where values less than a specified number | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions |