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

Copper Contributor

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

23 Replies

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

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

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

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,"")))``

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

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.

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

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

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

As variant

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

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

Hi everyone, I'm reaching out here as I think I have a slightly differrent but similar need.

I'm trying to get the second lowest value among a few specific columns.
My prices are stored within columns T, AQ, BN & CK. The idea here is to get all of the different offers on the same line.

I tried the LOWEST VALUE function, (PETITE.VALEUR in french) that allows you to choose whih nth value you are willing to retrieve.

However, the cells range has to be continued which is not the case for me.

Anothe restrain is that I cannot aggregate the date on a continued cells range.

Anyone has a workaround ?

Thanks in advance for your time & help and I wish you a nice day.
Tom

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

You can use the following:

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

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

@Martin_Angosto,

Thanks for your reply, however, when typing =MIN(CHOISIR({1;2;3;4};AQ6;BN6;CK6;DH6);2) It reverts either 2 or the minimal value among AQ,BN;CK & DH.

Feels like I'm missing something here.

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

Sorry for not translating it before. You shouldn't use MIN but PETITE.VALEUR.

=PETITE.VALEUR(CHOISIR({1;2;3;4};AQ6;BN6;CK6;DH6);2)

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

@Martin_Angosto

Works like a charm, many thanks & have a nice day 🙂

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

Glad it worked! Have a nice day too

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

If no zero formula returns second smallest.

If two or more zeroes formula returns zero.

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

Was not aware of being an issue to @TomA350

Any proposal in this case?

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

If 365 something like

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

# Re: 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)))``

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

Interesting!

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

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

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

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

With IF() we return FALSE if the value is zero, and logical value is ignored by MIN()

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

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.