Forum Discussion
JPScottIII
Nov 13, 2021Copper 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...
- TomA350Copper ContributorHi 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- Martin_AngostoIron Contributor
If no zero formula returns second smallest.
If two or more zeroes formula returns zero.
- OliverScheurichGold Contributor
=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))
- PeterBartholomew1Silver Contributor
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.
- Detlef_LewinSilver Contributor
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,"")))
=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.