system
#1
Hi,
I am trying to locate the last cell with data in a range. For example, in this sample range, A1:B4 …
___A___B
1 6/20 238
2 6/22
3 6/24 241
4 6/25
I need to return value of 241 and also return the date of 6/24. I initially used this formula in Excel, which worked great to find this information:
={(MAX((B$1:B$4<>"")*ROW(B$1:B$4)))-ROW(B$1:B$4)+1}
However, I then discovered that Xcelsius does not support ROW(). Does anyone have an alternative to using ROW() in this scenario? Thanks!
jknowlton (BOB member since 2010-06-25)
system
#2
try this:
get the max value of col2:
=max(b1:b4)
then get the corresponding date (col1, is that date) hehehe:
=INDEX(b1:b4,MATCH(MAX(b1:b4),b1:b4))
R.A. 
(BOB member since 2008-10-28)
system
#3
This solution will not work – It is only a coincidence that the second value is greater than the first.
See Chip Pearson’s article for the detail on the array function I was trying to use:
If only Xcelsius supported ROW() formula, I could use that array function…
jknowlton (BOB member since 2010-06-25)
system
#4
It works for me.
Except replace the B1:B4 in the index with A1:A4
First it finds the max value. Then it finds the first index with that value. So if it’s a tie then it doesn’t work, but what can you do in that case.
Then it uses that index in the Index function to get the corresponding value from A.
Edit:
I see, it’s because even though you used max in your original post, you want the last non-empty value, not the largest value.
Bolandb (BOB member since 2010-06-08)
system
#5
I stand corrected! That did work… amazing what match function can do! Thanks!
jknowlton (BOB member since 2010-06-25)
system
#6
system
#7
As long as the region of data is continuous you could also count the number of items in the column and then offset down that number.
BarneyL 
(BOB member since 2010-01-29)