Share via

MIN array formula not to include blank cells

Anonymous
2018-01-30T04:39:15+00:00

Hi,

I am using the below array formula but due to the range having blank cells I get an answer of zero. Any suggestions?

=MIN(IF('Cleansed Data'!$I:$I=Summary!$D$4,'Cleansed Data'!$F:$F))

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2018-01-30T05:01:47+00:00

=MIN(IF('Cleansed Data'!$I$1:$I$10000=Summary!$D$4,

IF('Cleansed Data'!$F$1:$F$10000<>"",'Cleansed Data'!$F$1:$F$10000)))

Note the use of a limited range like I1:I10000.  Change 1 and/or 10000 to suit your needs.

With I:I, Excel creates an array with 1+ million elements, and it performs 1+ million sets of comparisons. That is very inefficient, if it is unnecessary. I suspect you do not have anything close to 1+ million rows of data.

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-01-30T18:52:18+00:00

    That worked. Thanks you, its greatly appreciated!

    Was this answer helpful?

    0 comments