Forum Discussion
MIN function exclude "Zero" values - Non Consecutive group of cells
One more variant
=MIN( CHOOSE( ROW(A1:A4), IF(A1,A1), IF(C25,C25), IF(E4,E4), IF(G5,G5)))- Martin_AngostoJul 11, 2024Iron Contributor
Interesting!
Just replacing MIN by SMALL to meet Tom's requirement of retrieveing the second smallest number and beautiful alternative.
- SergeiBaklanJul 11, 2024Diamond Contributor
With IF() we return FALSE if the value is zero, and logical value is ignored by MIN()
- Martin_AngostoJul 11, 2024Iron Contributor
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).
- SergeiBaklanJul 11, 2024Diamond Contributor
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.