function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
WPCMSWPCMS 

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?

 

Example:

 

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?

 

tklinetkline

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

 

 

WPCMSWPCMS

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

tklinetkline

I was hoping you wouldn't say that!

 

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

 

EX:

NoDaysOne = 0

NoDaysTwo = 4

NoDaysThree = 0

 

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

WPCMSWPCMS

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.

 

Thanks!

tklinetkline

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 )
           )
         )
       )