Array Function - ignore blank values

I am using the following array function, however, the MIN part of the function is selecting blank values as the minimum value. How can I get it to ignore a cell that has no entry or blank value?

Thanks!

=IF(SUMPRODUCT(--(D2:D22="x"),--(C2:C22=MIN(IF(D2:D22="x",C2:C22))))>1,
"Roll Over",
INDEX(B2:B22,MATCH(1,(D2:D22="x")*(C2:C22=MIN(IF(D 2:D22="x",C2:C22))),0)))

=IF(SUMPRODUCT(--(D2:D22="x"),--(C2:C22=MIN(IF((D2:D22="x")*(C2:C22<>""),C2:
C22))))>1,"Roll Over",
INDEX(B2:B22,MATCH(1,(D2:D22="x")*(C2:C22=MIN(IF((D2:D22="x")*(C2:C22<>""),C
2:C22))),0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gillemi" <[email protected]> wrote in
message news:[email protected]...
>
> I am using the following array function, however, the MIN part of the
> function is selecting blank values as the minimum value. How can I get
> it to ignore a cell that has no entry or blank value?
>
> Thanks!
>
> =IF(SUMPRODUCT(--(D2:D22="x"),--(C2:C22=MIN(IF(D2:D22="x",C2:C22))))>1,
> "Roll Over",
> INDEX(B2:B22,MATCH(1,(D2:D22="x")*(C2:C22=MIN(IF(D
> 2:D22="x",C2:C22))),0)))
>
>
> --
> gillemi
> ------------------------------------------------------------------------
> gillemi's Profile:
http://www.excelforum.com/member.php...o&userid=33694
> View this thread: http://www.excelforum.com/showthread...hreadid=536524
>

gillemi wrote...
>I am using the following array function, however, the MIN part of the
>function is selecting blank values as the minimum value. How can I get
>it to ignore a cell that has no entry or blank value?
>
>Thanks!
>
>=IF(SUMPRODUCT(--(D2:D22="x"),--(C2:C22=MIN(IF(D2:D22="x",C2:C22))))>1,
>"Roll Over",INDEX(B2:B22,MATCH(1,(D2:D22="x")
>*(C2:C22=MIN(IF(D2:D22="x",C2:C22))),0)))

The problem is that when col C is blank but col D is "x", the IF call
will return 0 rather than FALSE, which MIN would include in its
calculations. You also need to check that col C contains numbers in the
IF function's condition.

Try the array formula

=IF(SUM((D2:D22="x")*(C2:C22=MIN(IF((D2:D22="x")*ISNUMBER(C2:C22),C2:C22))))>1,
"Roll Over",INDEX(B2:B22,MATCH(1,(D2:D22="x")
*(C2:C22=MIN(IF((D2:D22="x")*ISNUMBER(C2:C22),C2:C22))),0)))

I modified the formula to the columns and range that I am actually using and I am getting an error message when I hit Ctrl,shift,enter.

=IF(SUMPRODUCT(--(AJ2:AJ151="Y"),--(M2:M151=MIN(IF((AJ2:AJ151="Y")*(M2:M151<>""),M2:M151))))>1,"Roll Over",
INDEX(AK2:AK151,MATCH(1,(AJ2:AJ151="Y")*(M2:M151=MIN(IF(( AJ2:AJ151="Y")*(M2:M151<>""),M
2:M151))),0)))

To summarize, M3, M8, M13, M18 etc. to M148 are the golf putts per week for the 30 players (I am using 2:151 but this shouldn't matter). There are unrrelated numbers in between the cells that should be ignored (thus, Coulumn AJ = "Y"). Some golfers will miss some weeks so the M "blank" values need to be ignored. If two or more golfers tie then "Rollover". The above formula looks good to me, but I am getting an error message.

Thanks!!!!!

It works okay for me. Have you checked all that wrap-around.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gillemi" <[email protected]> wrote in
message news:[email protected]...
>
> I modified the formula to the columns and range that I am actually using
> and I am getting an error message when I hit Ctrl,shift,enter.
>
>
=IF(SUMPRODUCT(--(AJ2:AJ151="Y"),--(M2:M151=MIN(IF((AJ2:AJ151="Y")*(M2:M151<
>""),M2:M151))))>1,"Roll
> Over",
> INDEX(AK2:AK151,MATCH(1,(AJ2:AJ151="Y")*(M2:M151=MIN(IF((
> AJ2:AJ151="Y")*(M2:M151<>""),M
> 2:M151))),0)))
>
> To summarize, M3, M8, M13, M18 etc. to M148 are the golf putts per week
> for the 30 players (I am using 2:151 but this shouldn't matter). There
> are unrrelated numbers in between the cells that should be ignored
> (thus, Coulumn AJ = "Y"). Some golfers will miss some weeks so the M
> "blank" values need to be ignored. If two or more golfers tie then
> "Rollover". The above formula looks good to me, but I am getting an
> error message.
>
> Thanks!!!!!
>
>
> --
> gillemi
> ------------------------------------------------------------------------
> gillemi's Profile:
http://www.excelforum.com/member.php...o&userid=33694
> View this thread: http://www.excelforum.com/showthread...hreadid=536524
>

It does work...thanks!

Question...there are a total of 20 weeks/columns. Therefore I copied the formula over 19 more coulmns and input the $ into the formula where necessary.

For all of those future coulmns with no entries as of yet, the cell displays "Roll Over" already. Is there a way to get them to display something different like "#NA"??

Thanks again!

=IF(COUNTIF(D2:D22,"x")=0,NA(),
IF(SUMPRODUCT((D2:D22="x")*(C2:C22=MIN(IF((D2:D22="x")*(C2:C22<>""),C2:C22))
))>1,"Roll Over",
INDEX(B2:B22,MATCH(1,(D2:D22="x")*(C2:C22=MIN(IF((D2:D22="x")*(C2:C22<>""),C
2:C22))),0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"gillemi" <[email protected]> wrote in
message news:[email protected]...
>
> It does work...thanks!
>
> Question...there are a total of 20 weeks/columns. Therefore I copied
> the formula over 19 more coulmns and input the $ into the formula where
> necessary.
>
> For all of those future coulmns with no entries as of yet, the cell
> displays "Roll Over" already. Is there a way to get them to display
> something different like "#NA"??
>
> Thanks again!
>
>
> --
> gillemi
> ------------------------------------------------------------------------
> gillemi's Profile:
http://www.excelforum.com/member.php...o&userid=33694
> View this thread: http://www.excelforum.com/showthread...hreadid=536524
>