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
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 14, 2021

JPScottIII 

=IF(AND(AE7=0,AN7=0),MIN(D7,M7,V7),IF(AND(AE7=0,AN7<>0),MIN(D7,M7,V7,AN7),IF(AND(AE7<>0,AN7=0),MIN(D7,M7,V7,AE7),MIN(D7,M7,V7,AE7,AN7))))

 

With Office365 or 2021 you can apply IFS formula:

=IFS(AND(AE7=0,AN7=0),MIN(D7,M7,V7),

AND(AE7=0,AN7<>0),MIN(D7,M7,V7,AN7),

AND(AE7<>0,AN7=0),MIN(D7,M7,V7,AE7),

AND(AE7<>0,AN7<>0),MIN(D7,M7,V7,AE7,AN7))

Resources