Forum Discussion
Last number in a series?
As an alternative:
=INDEX(A:A,AGGREGATE(14,6,(ROW(A:A)*NOT(ISBLANK(A:A))),1),1)This one allows for blank cells and it doesn't matter if you have a header or not. It returns the value of the last cell in the column that is NOT blank.
Hi, if I understand you correctly, you have a single column of numbers and every day additional numbers are entered onto the end of this column of numbers.
I am assuming that the column of numbers does not contain a blank cell. In other words, the first blank cell in the column is at the end of the sequence of numbers:
My solution is to identify the first blank cell and then read the value of the cell immediately above this.
1. Assuming the data is in column A, and the first blank cell is at the end of the numbers, then the row containing the first blank cell is found using this formula:
=MIN(IF(A:A="",ROW(A:A)))
2. The value immediately before this row can now use the Offset function as follows:
=OFFSET(A1,(MIN(IF(A:A="",ROW(A:A))))-2,0)
The "-2" in the formula excludes the current row and the header row and assumes the data looks something like this:
A1: Data
A2: 1
A3: 2
A4: 3
A5: 4
A6: 10
A7: 22
A8: 11
If you have blank rows above the data then you should be able to adjust the cell range A:A to specific row numbers accordingly.
Hope this helps.