Display the last value of an array

[Guest]

How can I display in a cell the last value of an array that is continuously
being updated? E.g. I have the array A1:A8 as follows:
A1 5
A2 10
A3 16
A4 20
A5
A6
A7
A8
As in the example, the last value of the array is “20â€. But when I fill in
the cell A5 with 35, I would like that it would appear that number. Thank you
for your help.

 

[Max]

One way is to try JE's array formula
to a recent similar query: http://tinyurl.com/8gysf

 

[Aladin Akyurek]

How can I display in a cell the last value of an array that is continuously
being updated? E.g. I have the array A1:A8 as follows:
A1 5
A2 10
A3 16
A4 20
A5
A6
A7
A8
As in the example, the last value of the array is “20â€. But when I fill in
the cell A5 with 35, I would like that it would appear that number. Thank you
for your help.

For the last numeric value, invoke:

=LOOKUP(9.99999999999999E+307,A:A)

 

[Guest]

Unfortunalely your answer did not help me... :-(

That formula resulted in lots of erros which I could not repair...

 

[Guest]

Let's say that the array you want to check is in column B. Put this into
another column besides B. This SHOULD give you the value of the last
entered cell in Column B.


=CELL("contents",INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,B:B),2)))

Let's say I learned something here today. Thanks!

If you want another column besides row B, you'll have to change the very
last 2 to match the column # (a=1, b=2, c=3, d=4, etc)

 

[JE McGimpsey]

What errors?

If your data is continuous you can use

=INDEX(A:A,COUNTA(A:A))

 

[Aladin Akyurek]

Let's say that the array you want to check is in column B. Put this into
another column besides B. This SHOULD give you the value of the last
entered cell in Column B.


=CELL("contents",INDIRECT(ADDRESS(MATCH(9.99999999999999E+307,B:B),2)))

Let's say I learned something here today.

[...]

Try to pick up the most efficient way of doing it...

=MATCH(9.99999999999999E+307,B:B)

returns the native position of the last numeric value.

=LOOKUP(9.99999999999999E+307,B:B)

or

=INDEX(B:B,MATCH(9.99999999999999E+307,B:B)

return the last numeric value.