Display the last value of an array
 

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.

One way is to try JE's array formula
to a recent similar query: http://tinyurl.com/8gysf
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"pateodoro" wrote in message
...
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.

pateodoro wrote:
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)

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

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

"Max" wrote:

One way is to try JE's array formula
to a recent similar query: http://tinyurl.com/8gysf
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"pateodoro" wrote in message
...
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.

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.99999999 999999E+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)

"pateodoro" wrote:

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.

What errors?

If your data is continuous you can use

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


In article ,
pateodoro wrote:

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

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

Barb R. wrote:
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.99999999 999999E+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.