• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Smallest (1st 2nd & 3rd ) Value in range including zero's

Hi Team
Please find attachment for your reference, in that I need formula 1st 2nd & 3rd Smallest value in range including zero's,

I tried by below formula
=IFERROR(INDEX($A$2:$J$2,IF(COUNTIFS(A3:J3,0)<=0,0,MATCH(SMALL(A3:J3,1),A3:J3,0))),"")
it's working for 1st only, I need 2nd & 3rd Smallest value in particular range including zero's

Thank you in advance
K H Basavaraj
 

Attachments

  • Smallest Value 1st 2nd 3rd.xlsx
    11.1 KB · Views: 7
Perhaps like so?
=IF(COLUMNS($K:K)>COUNTIF($A3:$J3,0),0,INDEX($A$2:$J$2,AGGREGATE(15,6,($A$1:$J$1)/COUNTIF($K$1,$A3:$J3),COLUMNS($K:K))))
 

Attachments

  • Copy of Smallest Value 1st 2nd 3rd.xlsx
    12.8 KB · Views: 4
You seem to be on Excel 2013 so I can't use TEXTJOIN so in the First zero column in row 3:
=IFERROR(INDEX($A$2:$J$2,FIND("¬",SUBSTITUTE($A3&$B3&$C3&$D3&$E3&$F3&$G3&$H3&$I3&$J3,"0","¬",1))),"")
in the Second zero column, the same formula but change the red 1 to a 2
in the Third zero column, the same formula but change the red 1 to a 3
Then you can copy the 3 cells down.

Provisos:
No blank cells among the data.
No cells with more than one character in.

Stick with GraH - Guido's

COUNTIF($K$1,$A3:$J3)
love it!
 
Last edited:
My first formula is a minor variation on Guido's that gives the row of up to 3 values as an array (my ideal it to return all the results with one array formula but that is not straightforward for this problem).
= IF(
{1,2,3}<=COUNTIF(data.rows, 0 ),
INDEX( header, SMALL( IF( data.rows=0, idx ), {1,2,3} ) ),
"" )

Something that has just become possible with Office 365 insider is to reduce the nesting levels by using LET
= LET(
n, COUNTIFS(data.rows, 0 ),
match.idx, IF( data.rows=0, idx ),
selected.idx, SMALL( match.idx, {1,2,3} ),
return.hdr, INDEX( header, selected.idx ),
IF( {1,2,3}<=n, return.hdr, "" )
)
 

Attachments

  • Smallest Value 1st 2nd 3rd (PB).xlsx
    22.2 KB · Views: 6
Try this in K3 (with Ctrl + Shift + Enter) and drag across and down
=IFERROR(INDEX($A$2:$J$2,,SMALL(IF($A3:$J3=0,COLUMN($A3:$J3)),COLUMNS($K$3:K3))),"")
 
Back
Top