EXCLUDE 0 FROM MIN

[Leticia]

Hi, I am obtaining the min date given several conditions.
When one of the conditions is nonexistent, it gives me that one as the min value.
How can I solve that?

I am obtaining the =MIN(IF(MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"OK";'EV80'!$G:$G;"<="&$F$4)>0;MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"OK";'EV80'!$G:$G;"<="&$F$4));IF(MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4)>0;MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4)))

 

[Fluff]

False equates to 0 which is why you are getting 0, try putting a very large number as the false part of both IF functions.

 

[Leticia]

False equates to 0 which is why you are getting 0, try putting a very large number as the false part of both IF functions.

It does work. But is there any other solution that does not involve putting random numbers? Thank you!

 

[jasonb75]

is there any other solution that does not involve putting random numbers?

A large number inserted into the formula is not random, it is a deliberate value that is set higher than the scope of the real data so that it never produces an incorrect result.

If you insist on an alternative then this might work. I've assumed that the results of the 2 MINIFS functions can only be positive or zero, if you have negative numbers then it will need some additional criteria.

=AGGREGATE(15,6,CHOOSE({1;2};1/(1/MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"OK";'EV80'!$G:$G;"<="&$F$4));1/(1/MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4)));1)

 

[Fluff]

Can you have negative numbers in col G?

 

[Leticia]

Can you have negative numbers in col G?

No! they were dates

 

[Leticia]

A large number inserted into the formula is not random, it is a deliberate value that is set higher than the scope of the real data so that it never produces an incorrect result.

If you insist on an alternative then this might work. I've assumed that the results of the 2 MINIFS functions can only be positive or zero, if you have negative numbers then it will need some additional criteria.

=AGGREGATE(15,6,CHOOSE({1;2};1/(1/MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"OK";'EV80'!$G:$G;"<="&$F$4));1/(1/MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4)));1)

It worked! Thank you! If you can, could you explain me the logic behind?