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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 13, 2021

JPScottIII 

Another 365 solution

= LET(
    k, SEQUENCE(1,5,1,9),
    array,   INDEX(dataRow, k),
    nonzero, IF(NOT((array=0)*(k>=28)), array),
    MIN(nonzero) )

This builds an array from every 9th cell in the row.  Any zeros found in the back end of the array are converted to FALSE.  MIN returns the required result.

 

If you do not have 365, it might be the time to say.

Resources