SOLVED

Last number in a series?

%3CLINGO-SUB%20id%3D%22lingo-sub-1104344%22%20slang%3D%22en-US%22%3ELast%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104344%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20column%20of%20numbers%20that%20get's%20added%20to%20every%20day.%20I%20need%20a%20formula%20to%20return%20me%20the%20last%20number%20in%20the%20series%20please%3F%20Thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1104344%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1104445%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104445%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20if%20I%20understand%20you%20correctly%2C%20you%20have%20a%20single%20column%20of%20numbers%20and%20every%20day%20additional%20numbers%20are%20entered%20onto%20the%20end%20of%20this%20column%20of%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20assuming%20that%20the%20column%20of%20numbers%20does%20not%20contain%20a%20blank%20cell.%20In%20other%20words%2C%20the%20first%20blank%20cell%20in%20the%20column%20is%20at%20the%20end%20of%20the%20sequence%20of%20numbers%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20is%20to%20identify%20the%20first%20blank%20cell%20and%20then%20read%20the%20value%20of%20the%20cell%20immediately%20above%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Assuming%20the%20data%20is%20in%20column%20A%2C%20and%20the%20first%20blank%20cell%20is%20at%20the%20end%20of%20the%20numbers%2C%20then%20the%20row%20containing%20the%20first%20blank%20cell%20is%20found%20using%20this%20formula%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3DMIN(IF(A%3AA%3D%22%22%2CROW(A%3AA)))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E2.%20The%20value%20immediately%20before%20this%20row%20can%20now%20use%20the%20Offset%20function%20as%20follows%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3DOFFSET(A1%2C(MIN(IF(A%3AA%3D%22%22%2CROW(A%3AA))))-2%2C0)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20%22-2%22%20in%20the%20formula%20excludes%20the%20current%20row%20and%20the%20header%20row%20and%20assumes%20the%20data%20looks%20something%20like%20this%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EA1%3A%20Data%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA2%3A%201%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA3%3A%202%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA4%3A%203%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA5%3A%204%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA6%3A%2010%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA7%3A%2022%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EA8%3A%2011%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20blank%20rows%20above%20the%20data%20then%20you%20should%20be%20able%20to%20adjust%20the%20cell%20range%20A%3AA%20to%20specific%20row%20numbers%20accordingly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EHope%20this%20helps.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1104514%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F516127%22%20target%3D%22_blank%22%3E%40dc05_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternative%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(A%3AA%2CAGGREGATE(14%2C6%2C(ROW(A%3AA)*NOT(ISBLANK(A%3AA)))%2C1)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20one%20allows%20for%20blank%20cells%20and%20it%20doesn't%20matter%20if%20you%20have%20a%20header%20or%20not.%20It%20returns%20the%20value%20of%20the%20last%20cell%20in%20the%20column%20that%20is%20NOT%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1104768%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104768%22%20slang%3D%22en-US%22%3Ethank%20you%20very%20much%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981517%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat!%20I%20do%20want%20to%20put%20other%20things%20in%20that%20A%20column%20but%20about%2030%20Rows%20down%2C%20is%20there%20a%20way%20to%20stop%20the%20value%20counting%20at%20a%20specific%20row%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ERush%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1982237%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20number%20in%20a%20series%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1982237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827144%22%20target%3D%22_blank%22%3E%40Tucev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(A%3AA%2CXMATCH(%2CA%3AA)-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAnd%20it%20stops%20on%20first%20blank%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

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

@dc05_ 

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.

 

thank you very much

@Riny_van_Eekelen 

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

@Tucev 

As variant

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

And it stops on first blank cell