MIN function exclude "Zero" values - Non Consecutive group of cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2962112%22%20slang%3D%22en-US%22%3EMIN%20function%20exclude%20%22Zero%22%20values%20-%20Non%20Consecutive%20group%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2962112%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20with%20ignore%20zero%20values%20when%20the%20group%20of%20cells%20to%20be%20tested%20is%20not%20a%20consecutive%20range%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%20%22%3DMIN(D7%2CM7%2CV7%2CAE7%2CAN7)%22%20works%20fine%20but%20I%20want%20to%20ignore%20lowest%20value%20if%20AE7%20and%20AN7%20are%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20examples%20for%20MINIFS%20require%20the%20group%20of%20cells%20to%20be%20tested%20to%20be%20a%20consecutive%20Range%26nbsp%3B%20(D7%3AAN7)%20however%2C%20I%20have%20values%20in%20the%207th%20row%20that%20I%20do%20not%20want%20factored%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20and%20I%20apologize%20for%20my%20nube-ness...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2962112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2962159%22%20slang%3D%22en-US%22%3ERe%3A%20MIN%20function%20exclude%20%22Zero%22%20values%20-%20Non%20Consecutive%20group%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2962159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1217579%22%20target%3D%22_blank%22%3E%40JPScottIII%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMIN(IF((MOD(COLUMN(D7%3AAN7)%2C9)%3D4)*(D7%3AAN7%26lt%3B%26gt%3B0)%2CD7%3AAN7%2C1E%2B300))%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Microsoft%20365%20or%20Office%202021%2C%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2962197%22%20slang%3D%22en-US%22%3ERe%3A%20MIN%20function%20exclude%20%22Zero%22%20values%20-%20Non%20Consecutive%20group%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2962197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1217579%22%20target%3D%22_blank%22%3E%40JPScottIII%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20like%20this%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DMIN(D7%2CM7%2CV7%2CLET(a%2CFILTER(AE7%2CAN7%2CAE6%2CAN6)%2CFILTER(a%2Ca%26gt%3B0%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2962348%22%20slang%3D%22en-US%22%3ERe%3A%20MIN%20function%20exclude%20%22Zero%22%20values%20-%20Non%20Consecutive%20group%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2962348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1217579%22%20target%3D%22_blank%22%3E%40JPScottIII%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(AE7%3D0%2CAN7%3D0)%2CMIN(D7%2CM7%2CV7)%2CIF(AND(AE7%3D0%2CAN7%26lt%3B%26gt%3B0)%2CMIN(D7%2CM7%2CV7%2CAN7)%2CIF(AND(AE7%26lt%3B%26gt%3B0%2CAN7%3D0)%2CMIN(D7%2CM7%2CV7%2CAE7)%2CMIN(D7%2CM7%2CV7%2CAE7%2CAN7))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Office365%20or%202021%20you%20can%20apply%20IFS%20formula%3A%3C%2FP%3E%3CP%3E%3DIFS(AND(AE7%3D0%2CAN7%3D0)%2CMIN(D7%2CM7%2CV7)%2C%3C%2FP%3E%3CP%3EAND(AE7%3D0%2CAN7%26lt%3B%26gt%3B0)%2CMIN(D7%2CM7%2CV7%2CAN7)%2C%3C%2FP%3E%3CP%3EAND(AE7%26lt%3B%26gt%3B0%2CAN7%3D0)%2CMIN(D7%2CM7%2CV7%2CAE7)%2C%3C%2FP%3E%3CP%3EAND(AE7%26lt%3B%26gt%3B0%2CAN7%26lt%3B%26gt%3B0)%2CMIN(D7%2CM7%2CV7%2CAE7%2CAN7))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 and AN7 are zero.

 

All the examples for MINIFS require the group of cells to be tested to be a consecutive Range  (D7:AN7) however, I have values in the 7th row that I do not want factored in.

 

Thanks in advance and I apologize for my nube-ness...

5 Replies

@JPScottIII 

=MIN(IF((MOD(COLUMN(D7:AN7),9)=4)*(D7:AN7<>0),D7:AN7,1E+300))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

@JPScottIII 

Something like this?

 

=MIN(D7,M7,V7,LET(a,FILTER(AE7:AN7,{1,0,0,0,0,0,0,0,0,1}),FILTER(a,a>0,"")))

 

@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.

@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))

@JPScottIII 

As variant

=LET(v, INDEX(D7:AN7,,SEQUENCE(,5,1,9)), MIN( FILTER(v,v)) )