MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find last cell in row with negative number


Posted by Dan Stickel on November 16, 2001 3:49 PM

How can I find the last cell in a row containing a negative number? I then ultimately want to look at its column header and see what month that is.


Posted by Aladin Akyurek on November 16, 2001 4:13 PM

Dan --

=INDIRECT(ADDRESS(ROW(1:1),SUMPRODUCT(MAX((2:2<0)*(COLUMN(2:2))))))

will produce the date in row 1 corresponding to the last negative cell in row 2. Adjust 1:1 (dates) and 2:2 the row of interest to your situation.

Aladin

Posted by Mark W. on November 16, 2001 4:19 PM

Suppose cells A1:F2 contains...

{"Jan","Feb","Mar","Apr","May","Jun"
;10,20,-5,7,-2,8}

"May" will be returned by the array formula...

{=OFFSET(A1,0,MAX((A2:F2<0)*COLUMN(A2:F2))-1)}

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Dan Stickel on November 16, 2001 4:37 PM

Very elegant. Thank you!