Forum Discussion
MIN function exclude "Zero" values - Non Consecutive group of cells
I am struggling with ignore zero values when the group of cells to be tested is not a consecutive range The function "=MIN(D7,M7,V7,AE7,AN7)" works fine but I want to ignore lowest value if AE7 a...
One more variant
=MIN( CHOOSE( ROW(A1:A4), IF(A1,A1), IF(C25,C25), IF(E4,E4), IF(G5,G5)))
Interesting!
Just replacing MIN by SMALL to meet Tom's requirement of retrieveing the second smallest number and beautiful alternative.
With IF() we return FALSE if the value is zero, and logical value is ignored by MIN()
And just to add to the pot, an interesting small challenge:
Imagine range from large negative to large positive. We aim to retrieve the second smallest positive number from a discontinued range of cells. Hundreds of cells to take into account. The second smallest number retrieved (k) should not be equal to k -1, nor k +1. That is, a unique second smallest positive (non-zero) number.
See initial structure:
=SMALL(LET(continued,HSTACK(A1,C1,E1,G1,I1),FILTER(continued,continued>0)),2)
In the example, 8 should be the value retrieved and not 1.
Also, how to set the HSTACK or similar in an efficient way (hundreds of cells, cannot be entered mannually - one space between each one always).
Not to enter hundred of references it shall be some logic defined, i.e. take every third cell or like. If no such logic when only manually.
What about the retrieval of the second smallest postive unique number?
In a range {-1,1,1,8,9} it should be 8.
In a range {-1,1,1,8,8,9} it should be 9.
Note the aim is not the second smallest number by position (which would be 1) but for uniqueness (first unique second smallest value).