Finding a maximum number...with an exception
 

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

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

Any help on this would be great. Thanks.


--
cubsfan
------------------------------------------------------------------------
cubsfan's Profile: http://www.excelforum.com/member.php...o&userid=33272
View this thread: http://www.excelforum.com/showthread...hreadid=530933

Finding a maximum number...with an exception
 

=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)

"cubsfan" wrote in
message ...

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

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

Any help on this would be great. Thanks.


--
cubsfan
------------------------------------------------------------------------
cubsfan's Profile:
http://www.excelforum.com/member.php...o&userid=33272
View this thread: http://www.excelforum.com/showthread...hreadid=530933