Jan 13 2020 01:52 AM
Hi, I have a column of numbers that get's added to every day. I need a formula to return me the last number in the series please? Thanks in advance
Jan 13 2020 02:31 AM
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.
Jan 13 2020 03:01 AM
SolutionAs 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.
Dec 11 2020 03:11 PM
Great! I do want to put other things in that A column but about 30 Rows down, is there a way to stop the value counting at a specific row?
Thanks
Rush
Dec 12 2020 03:41 AM
Jan 13 2020 03:01 AM
SolutionAs 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.