Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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... |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display the last value of an array | Excel Worksheet Functions | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |