How to transpose the 5 highest row values only from cells whose value doesn't equal 0?

Question

I have a code that creates a comma separated text (in a cell) of the 'column titles' for the 5 largest cell values within a row. However, for example if a row only has 3 cells with values above 0 and the rest are 0, the script still adds 2 of the cells with a 0 value as "column titles" to the comma separated text to complete the 5 items.

What formula modifications would carry out this same formula but ignore any cells that have a 0 value when creating the 'column titles' on the comma separated cell/text?

A little greater detail: In the example above where only 3 cells have values above 0, the formula should only show the 'column titles' for the top 3 largest cell values (that are > than 0) and not add any column titles to the comma separated text from cells that = 0. How would the formula be modified to accomplish this? Thanks

Google Sheets Code Example:

=join(" , ",sortn(transpose($BN$1:$CM$1),5,0,transpose($BN3:$CM3),false))

Reference:

• $BN$1:$CM$1 (Are the Column Titles [text])
• $BN3:$CM3 (Are the cells in row 3 that are transposed to show the (column titles) of the cells with the top 5 largest values.

Answer 1

Use filter(), like this:

=join( 
  " , ", 
  sortn( 
    transpose(filter($BN$1:$CM$1, $BN3:$CM3 <> 0)),
    5, 0, 
    transpose(filter($BN3:$CM3, $BN3:$CM3 <> 0)), 
    false 
  ) 
)