Excel: Formula To Find Last Valid Cell in a Range

ID: Q85234

The information in this article applies to:

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 "range" is the address of the cells containing the data and "startcell" 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.


REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 156-157

"Microsoft Excel User's Guide," for Windows or OS/2, version 3.0, pages 119-120

"Microsoft Excel User's Guide," for the Macintosh, version 3.0, pages 113-114

Additional query words: 3.0 3.00 4.0 4.00 max nonblank non-blank 


Keywords          : 
Version           : MACINTOSH:3.0 and 4.0; WINDOWS:3.0 and 4.0
Platform          : MACINTOSH WINDOWS 
Issue type        :

Last Reviewed: July 9, 1999