Array Function - ignore blank values

[gillemi]

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(--(D222="x"),--(C2:C22=MIN(IF(D222="x",C2:C22))))>1,
"Roll Over",
INDEX(B2:B22,MATCH(1,(D222="x")*(C2:C22=MIN(IF(D
222="x",C2:C22))),0)))

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Harlan Grove]

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(--(D222="x"),--(C2:C22=MIN(IF(D222="x",C2:C22))))>1,
"Roll Over",INDEX(B2:B22,MATCH(1,(D222="x")
*(C2:C22=MIN(IF(D222="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((D222="x")*(C2:C22=MIN(IF((D222="x")*ISNUMBER(C2:C22),C2:C22))))>1,
"Roll Over",INDEX(B2:B22,MATCH(1,(D222="x")
*(C2:C22=MIN(IF((D222="x")*ISNUMBER(C2:C22),C2:C22))),0)))

 

[gillemi]

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!!!!!

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[gillemi]

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!

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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