Forum Discussion
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...
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
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
If no zero formula returns second smallest.
If two or more zeroes formula returns zero.
One more variant
=MIN( CHOOSE( ROW(A1:A4), IF(A1,A1), IF(C25,C25), IF(E4,E4), IF(G5,G5)))
- 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.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)
