Am trying to return the minimum value of cells excluding zero but when ever I do it using this function, it returns a zero not the minimum
Function:{=MIN(IF(DW2:EE2 = 0,"",DW2:EE2))}
The values in the cells are created by a function which is:
=IF(BJ2="",0,IF(BJ2="D1","1",IF(BJ2="D2","2",IF(BJ2="C3","3",IF(BJ2="C4","4",IF(BJ2="C5","5",IF(BJ2="C6","6",IF(BJ2="P7","7",IF(BJ2="P8","8",IF(BJ2>="F9","9"))))))))))
This answer is wrong, but it might give you an idea.
=IF(MIN(A1:A5)=0,SMALL(A1:A5,2),MIN(A1:A5))
This means the following (I'm always working from a list of values in the cells "A1" till "A5"):
However, there is one problem with the implementation of that approach: I expected =Small(range,2) to give the second smallest number of a list, which it does, but it does not, let me show you:
Range : 0, 1, 2, 3, 4, 5 => Small(Range,2) = 1 => OK
Range : 0, 1, 2, 3, 4, 0 => Small(Range,2) = 0 => NOK
Apparently, Small(,2) just orders the range, and takes the second element, regardless of the fact that it might be equal to Small(,1).
Does anybody know a solution or workaround for this issue?