Last number in a data string

[jblair626]

In a data table i have many columns and at the bottom I would like the last number displayed. so if i were to add data to it, ex 1-3 it would not change my LAST Row. But if I were to add data on 1-5 It would because jan 5th would then become my last input.


Date |Item 1|Item 2|Item 3|Item 4|
1-1 |12 |23 |12 |2
1-2 |11 |21 |29 |4
1-4 |13 |12 |19 |2
LAST |13 |12 |19 |2

 

[Aladin Akyurek]

In a data table i have many columns and at the bottom I would like the last number displayed. so if i were to add data to it, ex 1-3 it would not change my LAST Row. But if I were to add data on 1-5 It would because jan 5th would then become my last input.


Date |Item 1|Item 2|Item 3|Item 4|
1-1 |12 |23 |12 |2
1-2 |11 |21 |29 |4
1-4 |13 |12 |19 |2
LAST |13 |12 |19 |2

A5, just enter and copy across:

=LOOKUP(9.99999999999999E+307,A$2:A$4)

 

[TMShucks]

One way:

LAST(offset(0,1): =INDEX(B:B,COUNTA(B:B)-1) and drag across.

This is a circular reference so you'd need to set iterations to 1.

If the LAST line is not at the bottom of the data, you could use:

=INDEX(B:B,COUNTA(B:B))

 

[jblair626]

could you explain what each part of the lookup formula does? Incase I need to adjust it accordingly

 

[Aladin Akyurek]

could you explain what each part of the lookup formula does? Incase I need to adjust it accordingly

See:

http://www.mrexcel.com/forum/showthread.php?t=102091
http://www.mrexcel.com/forum/showthread.php?t=310278

The latter explains/pictures why/how LOOKUP picks out the last numeric value from the reference it's fed with.

 

[jblair626]

thanks now i understand, and thanks for the help.

 

[Aladin Akyurek]

thanks now i understand, and thanks for the help.

You are welcome. Thanks for providing feedback.