CODEWITHSUNDEEP
excelfunctionmin
nbg15
nbg15

Reputation: 129

Excel Min Function returns 0

I have already checked the Excel Documentation... I am always getting 0

this is my Excel Tableenter image description here

And this is my Code:

=MIN(IF(C7="X";C20);IF(D7="X";D20))

and If there is an "X" in Cell C7 and D7, I want to get the SMALLEST (MIN) value of Cell C20 or D20 and it works fine, BUT --> When I remove one "X" let say in Cell D7, I only get 0 back, becasue the Min Function always returns 0, if my IF Functions are not a digit / number, but when I remove my X, i always get False back from my IF() functions....

So my question is, how could I ignore all If Return values who are not a number means, when of my IF() Functions returns false <-- how could I ignore this value, that my MIN() Function will give me a valud resulst back - and not 0

???

Thanks for any help!

Upvotes: 1

Views: 5938

Answers (2)

Mischinab
Mischinab

Reputation: 2911

In the IF statements you could add a very large value to be used in the FALSE case:

=MIN(IF(C7="X";C20;99999);IF(D7="X";D20;99999))

However, this would mean that if there are no X’s then the calculated value will be 99999. But for cases where there is at least one X you’ll get the correct minimum value. Just make sure that the FALSE case value is large enough to always be greater than values in row 20.

If you wanted a specific value in case there are no X’s found, you can have a hidden cell do the calculation above, and another cell check its value. e.g.:

A1 = MIN(IF(C7="X";C20;99999);IF(D7="X";D20;99999))
A2 = IF(A1=99999;"No Matches";A1)

Or you can also combine it all into one cell if you don’t mind the complex formula:

= IF( MIN(IF(C7="X";C20;99999);IF(D7="X";D20;99999)) = 99999 ;
      "No Matches" ; 
      MIN(IF(C7="X";C20;99999);IF(D7="X";D20;99999)) )

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27249

I just tested this formula against your scenarios and it worked for all cases:

1) X in both C7 and D7 (returns 9)
2) X in only D7 (returns 10)
3) X in only C7 (returns 9)
4) no X in C7 nor D7 (returns 0)

=IF(AND(C7="X",D7="X"),MIN(C20:D20),IF(COUNTIF(C7:D7,"X")>0,INDEX(C20:D20,MATCH("X",C7:D7,0)),0))

Upvotes: 2

Related Questions