4

I am relatively new to Google Spreadsheets. I would like to use spreadsheets to better analyze classroom data.
I am trying to write a formula to merge column headings into one cell based on a specific value. In the example below, I would like every column heading that has a 0 for a particular student to be written in one cell:

My goal is this:

intended result

I've been searching through forums without much luck. Any advice to get me started would be much appreciated.

4

Try this formula for cell H2:

=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))

This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.

You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.

  • Worked perfectly. Thank you very much! – Josh Mar 6 '14 at 18:20
  • Will do. I was just playing around with your formula in some other spreadsheets I created. Is it possible to do a quick add on to the formula so that for students that did not get any words wrong (i.e. they have all 1s) that cell will read something like "all correct" instead of #N/A? Thanks again. That's a big help. – Josh Mar 6 '14 at 18:33
  • Yes, something like =IF(SUM(B2:E2) = 4, "All correct", JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))). So if the total score for the student is 4, display All correct, else display the problematic words. – Vidar S. Ramdal Mar 6 '14 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.