Last several values in a column

[Mike K]

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]

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]

Domenic,
Oh, that is so beautiful!

Many thanks,
Mike