Display the last value of an array

P

pateodoro

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.
 
A

Aladin Akyurek

pateodoro said:
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.
Click to expand...

For the last numeric value, invoke:

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

pateodoro

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

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

Barb R.

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)
 
J

JE McGimpsey

What errors?

If your data is continuous you can use

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

Aladin Akyurek

Barb said:
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.
Click to expand...

[...]

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.
 
Top