+ Start a Discussion

How to have the MIN function ignore 0?

I have fields that add up the number of days. I want the formula to pick the mininum number out of the Days it has counted. What it is doing is if the number of days is zero it chooses that as its min. How do I get it to ignore zero?




NoDaysOne = 0

NoDaysTwo = 52

NoDaysThree = 60


Out of these three number of Days I want to return 52 (the real min)  not 0.


Current formula:



Min( NoDaysOne__c , NoDaysTwo__c , NoDaysThre__c)


Any ideas?



Will the values always be sequential, or will they vary?


For example will the values always be increasing? -

NoDaysOne = 0

NoDaysTwo = 2

NoDaysThree = 6


Or do you expect the values to be non-sequential? -

NoDaysOne= 15

NoDaysTwo = 7

NoDaysThree = 29




They will be non sequential. That is why Its driving me nuts!


I was hoping you wouldn't say that!


Will there ever be a scenario where two values could equal 0?



NoDaysOne = 0

NoDaysTwo = 4

NoDaysThree = 0


Or will zero only be a value once in the series?


Yes there are. They could all have numbers greater than zero or all but one is greater than zero. Never would they all be zero.




Once again I was hoping you wouldn't say that haha.


Here's what I was able to come up with, it's probably more complicated then it needs to be, but I couldn't figure any other way around it.  Also if you have two values of 0 this will not work properly (it will return 0).  I'm pretty much stumped but maybe my code is a good starting point -



IF( NoDaysOne__c =0, MIN( NoDaysTwo__c,  NoDaysThree__c ),
  IF( NoDaysTwo__c =0, MIN( NoDaysOne__c , NoDaysThree__c ),
   IF( NoDaysThree__c =0, MIN( NoDaysOne__c , NoDaysTwo__c ),
         MIN( NoDaysOne__c , NoDaysTwo__c , NoDaysThree__c )