Microsoft Office Tutorials and References
In Depth Information
Suppose that you have a worksheet that you update frequently by adding new data to columns. You may need a
way to reference the last value in column A (the value most recently entered). If column A contains no empty
cells, the solution is relatively simple and doesn't require an array formula:
=OFFSET(A1,COUNTA(A:A)–1,0)
This formula uses the COUNTA function to count the number of nonempty cells in column A. This value
(minus 1) is used as the second argument for the OFFSET function. For example, if the last value is in row 100,
COUNTA returns 100 . The OFFSET function returns the value in the cell 99 rows down from cell A1 in the
same column.
If column A has one or more empty cells interspersed, which is frequently the case, the preceding formula won't
work because the COUNTA function doesn't count the empty cells.
The following array formula returns the contents of the last nonempty cell in column A:
{=INDEX(A:A,MAX(ROW(A:A)*(A:A<>””)))}
You can, of course, modify the formula to work with a column other than column A. To use a different column,
change the column references from A to whatever column you need.
This formula does not work if the column contains any error values.
You can't use this formula, as written, in the same column in which it's working. At-
tempting to do so generates a circular reference. You can, however, modify it. For ex-
ample, to use the function in cell A1, change the references so that they begin with row
2 rather than the entire columns. For example, use A2:A1000 to return the last
nonempty cell in the range A2:A1000.
As I was finishing up this chapter, I found an alternate (non-array) formula that returns
the last value in a column. This formula returns the last nonempty cell in column A:
=LOOKUP(2,1/(A:A<>””),A:A )
The only problem is that I don't understand how it works, but it seems to be perfectly reliable and is
probably more efficient than the array formula. It differs from the array formula in one way: It ignores er-
ror values. So it actually returns the last nonempty non-error cell in a column.
Returning the last value in a row
Search JabSto ::




Custom Search