How do I select the last number in a column in Open Office Calc?

July 9, 2009 4:41 PM

How do I select the last number in a column in Open Office Calc?

I have a column of numbers which I add to daily. I would like the bottom, most recent number to be automatically shown in a cell on a different tab.

So, on tab 2 I have column A filled with numbers, with today's number being the very bottom number.

On tab 1 I would have a field which would show that latest number.

The versions of this that I found online, such as

=INDEX(A:A,MATCH(1E+30,A:A))

give me a 508 error. What am I doing wrong? I have very little understanding of formulas like this as I've never really dabbled with them before.

posted by tomble to Computers & Internet (3 answers total)

Best answer: Try this:
=INDEX(A1:A65536,COUNTA(A1:A65536))

Explanation:

COUNTA gives you a count of how many cells are non-blank in that row. 65536 is the maximum number of rows that OOcalc can handle, so you're counting everything in that row, from the start to the end.

INDEX first defines a region, then returns the value at a given spot.

So if you have ten items, you run COUNTA on your column and it which returns 10. Then INDEX is defined as the same column, and returns the item at position 10. (which should be the value you're looking for.

One caveat: If there are any blank rows in your column, you have to compensate for them. If you have one blank row at the top, for example, your new formula would be:
=INDEX(A1:A65536,COUNTA(A1:A65536)+1)
posted by chrisamiller at 5:01 PM on July 9, 2009

Response by poster: Thanks Chrisamiller (the explanation was very much appreciated!) - I still get the 508 error which, apparently means a 'missing bracket'. I can't see how it thinks there is a missing bracket from this function!
posted by tomble at 5:48 PM on July 9, 2009

Response by poster: Ah, got it (after some tinkering, guesswork and luck!)

=INDEX(A1:A65536,COUNTA(A1:A65536))

Should be

=INDEX(A1:A65536;COUNTA(A1:A65536))

It seems open office doesn't like a comma between the functions!

Thans again!
posted by tomble at 5:55 PM on July 9, 2009