SOLVED

New Contributor

# Last number in a series?

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

5 Replies

# Re: Last number in a series?

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.

Best Response confirmed by dc05_ (New Contributor)
Solution

# Re: 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.

# Re: Last number in a series?

thank you very much

# Re: Last number in a series?

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

# Re: Last number in a series?

As variant

``=INDEX(A:A,XMATCH(,A:A)-1)``

And it stops on first blank cell