In a Google sheet, I have an array of student results, with the score in marks for different objectives in some recent projects. Here’s an example: https://docs.google.com/spreadsheets/d/1yoX3t9wxj0B1YSYSBikVC7_DEgofFc-bwZoslSYQpjs/edit?usp=sharing
At the end of each row, I’m trying to create a list of the objectives where the student scored zero marks.
I have an array formula which does this by returning the column header wherever there is a 0 result, as follows:
=TEXTJOIN(", ",TRUE,ArrayFormula(IF(B2:I2=0,$B$1:$I$1,"")))
However, not all students have been set every objective- therefore some cells are blank. And at present the formula can’t distinguish between the blanks and the zeros.
Can anyone recommend a way to fix the formula so it only returns column headers for the zeros, but ignores the blanks?
>Solution :
try this formula in cell L2:
=BYROW(C2:J,LAMBDA(ax,IF(COUNTA(ax)=0,,TEXTJOIN(", ",1,IFNA(FILTER(C1:J1,ax=0,ax<>""))))))
–
