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

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

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

.