Forum Discussion

JPScottIII's avatar
JPScottIII
Copper Contributor
Nov 13, 2021

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...
excel
Formulas and Functions
Martin_Angosto's avatar
Martin_Angosto
Iron Contributor
Jul 11, 2024

TomA350 

 

You can use the following:

 

=SMALL(CHOOSE({1,2,3,4},T1,AQ1,BN1,CK1),2)

SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 11, 2024

Martin_Angosto 

If no zero formula returns second smallest.

If two or more zeroes formula returns zero.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 11, 2024

      Martin_Angosto 

      One more variant

      =MIN( CHOOSE( ROW(A1:A4), IF(A1,A1), IF(C25,C25), IF(E4,E4), IF(G5,G5)))
      • Martin_Angosto's avatar
        Martin_Angosto
        Iron Contributor
        Jul 11, 2024

        SergeiBaklan 

         

        Interesting!

        Just replacing MIN by SMALL to meet Tom's requirement of retrieveing the second smallest number and beautiful alternative.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 11, 2024

      Martin_Angosto 

      If 365 something like

      =LET(a, HSTACK(A1,C25,E4,G5), MIN( FILTER(a, a ) ) )

Resources