Some times in Excel worksheet which we update frequently by adding new data to its columns and we need to reference the last value in a particular column. That is the value most frequently entered. We can achieve this combining two excel worksheet formulas COUNTA and INDEX. The source of this tips is John Walkenbach's Excel 2010 Tips &Tricks book. To learn about find the last row using Excel VBA click here.
Below is the simple example with a excel worksheet which tracks the value of three funds in columns B to D. The data updates each and every month. The motto is to get the latest value for each fund and add the values of this.These value will be updated in the cells G2:G4.
The Excel formula used in G2,G3,G4 are as follows.
=INDEX(B:B,COUNTA(B:B))
=INDEX(C:C,COUNTA(C:C))
=INDEX(D:D,COUNTA(D:D))
Explanation on how this Excel Formula works: The COUNTA worksheet formula used above counts the number of non empty cells in the selected Column.Then this value is used as the second argument for INDEX worksheet function. In the the first formula, in column B the last value is in row 6 hence COUNTA returns 6 and the INDEX function returns the 6th value in the column.
The above formula works on most of the situations. However, if the column has one or more empty cells interspersed(Empty cell in between the cells which has values), determining the last nonblank cells is challenging as COUNTA function doesnot count the empty cells.
The following array formula returns the content of the last non empty cell in the first 1000 rows of column C, even if column C contains blank cells.
=INDEX(C1:C1000,MAX(ROW(C1:C1000)*(C1:C1000<>"")))
Note: As this is a Array formula of Excel, you need to press Ctrl+Shift+Enter instead of just Enter. Otherwise it given wrong output.
You can change the formula to work with a column other than column C. To use a different column, change the column reference from C to whatever column you need. If the last non empty cell occurs in a row beyond row 1000, you need to change the two instances of 1000 to a desired row number. The fewer rows referenced in the formula, the faster the calculation speed.
To return the last non empty cell in a row, you need to use following excel array formula. It works similar to above formula, but finds last row instead of last column(In this example, row 1.
=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))
to use this formula for a different row, change the the three 1:1 row references to correspond to the correct row number.
Below is the simple example with a excel worksheet which tracks the value of three funds in columns B to D. The data updates each and every month. The motto is to get the latest value for each fund and add the values of this.These value will be updated in the cells G2:G4.
The Excel formula used in G2,G3,G4 are as follows.
=INDEX(B:B,COUNTA(B:B))
=INDEX(C:C,COUNTA(C:C))
=INDEX(D:D,COUNTA(D:D))
Explanation on how this Excel Formula works: The COUNTA worksheet formula used above counts the number of non empty cells in the selected Column.Then this value is used as the second argument for INDEX worksheet function. In the the first formula, in column B the last value is in row 6 hence COUNTA returns 6 and the INDEX function returns the 6th value in the column.
The above formula works on most of the situations. However, if the column has one or more empty cells interspersed(Empty cell in between the cells which has values), determining the last nonblank cells is challenging as COUNTA function doesnot count the empty cells.
The following array formula returns the content of the last non empty cell in the first 1000 rows of column C, even if column C contains blank cells.
=INDEX(C1:C1000,MAX(ROW(C1:C1000)*(C1:C1000<>"")))
Note: As this is a Array formula of Excel, you need to press Ctrl+Shift+Enter instead of just Enter. Otherwise it given wrong output.
You can change the formula to work with a column other than column C. To use a different column, change the column reference from C to whatever column you need. If the last non empty cell occurs in a row beyond row 1000, you need to change the two instances of 1000 to a desired row number. The fewer rows referenced in the formula, the faster the calculation speed.
To return the last non empty cell in a row, you need to use following excel array formula. It works similar to above formula, but finds last row instead of last column(In this example, row 1.
=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))
to use this formula for a different row, change the the three 1:1 row references to correspond to the correct row number.
.jpg)
Posts
.jpg)
0 comments:
Post a Comment