Finding a maximum number...with an exception

C

cubsfan

I am trying to find a maximum number in a row of cells. However, I a
looking in only 5 specific (spaced out) cells. The numbers range fro
from 0-6. The twist comes in that each number in the spaced out cell
will only appear if certain criteria is met in certain cells that the
reference.

Just to clarify, here's what I need:
Say #s 1 and 2 are populated and so is # 4, but #3 is not ye
populated, I need a cell to look at all these #s and recognize tha
although #4 is the highest, #2 needs to be shown because #3 is not ye
available.

Any help on this would be great. Thanks
 
B

Bob Phillips

=MAX(A1:INDEX(A:A,MIN(IF(A1:A5="",ROW(A1:A5)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top