Microsoft KB Archive/85234

{|
 * width="100%"|

Excel: Formula To Find Last Valid Cell in a Range

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 3.0 and 4.0
 * Microsoft Excel for the Macintosh, version 3.0 and 4.0
 * Excel for OS/2, version 3.0

-

SUMMARY
Microsoft Excel can return the value of the last valid cell in a range of cells using an array formula. The following formula is an example:

  =IF(NOT(ISERROR(range)),OFFSET(startcell,COUNT(range)-1,0))

where &quot;range&quot; is the address of the cells containing the data and &quot;startcell&quot; is the address of the first cell of that range.

This formula is an array formula and must be entered by pressing COMMAND+RETURN on the Macintosh, and by pressing CTRL+SHIFT+RETURN for Windows and OS/2.

More information:

If you had a range of cells containing data and you wanted to return the last entry of that range, the formula described above would return the desired result. The following is an example:

  A1: 23 A2: 7 A3: 78 A4: A5: A6: =IF(NOT(ISERROR(A1:A5)),OFFSET(A1,COUNT(A1:A5)-1,0))

Cell A6 would return 78. However, if the cell A3 was blank and A4 contained 78, A6 would return a zero. This formula requires data entries to be contiguous, with blank cells, if any, at the end of the range.