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!
