Finding a maximum number...with an exception

[cubsfan]

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.

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

"cubsfan" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>