CODEWITHSUNDEEP
excel
user1717622
user1717622

Reputation: 323

MIN array function non zeros only

I have been trying to get this array function to output (non-zero) minimum values in the 'FINAL DATA' AE column. Can you see a structural error in this formula?

       =IF($C$4="All EMEA",

MIN(IF('FINAL DATA'!$2:$AE$250000<>0,

('FINAL DATA'!$J$2:$J$250000=$C$4)*('FINAL DATA'!$E$2:$E$250000=$E$4)*( 'FINAL DATA'!$AE$2:$AE$250000))),

MIN(IF('FINAL DATA'!$AE$2:$AE$250000<>0, 

('FINAL DATA'!$K$2:$K$250000=$C$4)*('FINAL DATA'!$E$2:$E$250000=$E$4)*( 'FINAL DATA'!$AE$2:$AE$250000)))

)

Upvotes: 0

Views: 1634

Answers (4)

chris neilsen
chris neilsen

Reputation: 53127

Barry has identified the core problem (tests returnimg 0 to the MIN function).

Here's a refactor of your formula (still an array formula) that solves this, and is quite a bit shorter

=MIN(IF(($S:$S<>0)*($E:$E=$E$4)*(IF($C$4="All EMEA",$J:$J,$K:$K)=$C$4),
 ($S:$S)))

Note that this (as would your original formaul, when fixed) will return 0 if there are no qualifying values >0 in the ranges

Upvotes: 1

James L.
James L.

Reputation: 9453

You can eliminate the zeros by using an IF() function in an array formula. Consider the following:

       A
Row  -----
 1       0
 2       7
 3       5
 4       6
 5        
 6       3

The array formula =MIN(IF($A$1:$A$6>0,$A$1:$A$6)) will return 3 because the 0 and blank cell are eliminated with the >0 portion of the if statement.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

By using <>0 that will eliminate zeroes and blanks, so that isn't the problem.....[although if you only want to eliminate blanks and have zero as a valid return value you should use <>""]

You can't multiply the conditions with the number range because by multiplying you get zeroes for any rows where the conditions are not satisfied, use multiple IFs instead, like this:

=MIN(IF('FINAL DATA'!$AE$2:$AE$250000<>0,IF('FINAL DATA'!$J$2:$J$250000=$C$4,IF('FINAL DATA'!$E$2:$E$250000=$E$4,'FINAL DATA'!$AE$2:$AE$250000))))

Upvotes: 2

P. Stallworth
P. Stallworth

Reputation: 162

Second line, you have !$2, no column specified.

MIN(IF('FINAL DATA'!$2:$AE$250000<>0,

Also, it looks like you are trying to run a single If comparison against a range, which I don't think will work the way you are trying to use it.

Upvotes: 1

Related Questions