Last nonblank row formula
Posted by Murray Couper on December 03, 2001 11:51 AM
I would like to know if there is a formula or function whereby I can get the last nonblank cell in a column of cells and display this in another cell. The range of cells will all be nonblank to the last nonblank cell. As an example, if the column of data represents data for January to December in cell A1:A12 but we are only up to March with data, then cell A13 should show the last available cell data which will be March.
I have tried the IF function nested but it only nests up to 7 times.
Posted by Aladin Akyurek on December 03, 2001 12:03 PM
=MATCH(9.99999999999999E+307,A:A)
will give you the row number of the last non-blank cell in A. The formula presupposes that A is of a numeric type.
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1)
gives you the address of the last non-blank cell in A.
Aladin
==========
Posted by Mark W. on December 03, 2001 12:04 PM
=INDEX(A1:A12,MATCH(9.99999999999999E+307,A1:A12))
...where A1:A12 contains numeric values.
