I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number of respondents failed to fill out either their "before" or "after" results. This creates a number of obvious issues with respect to accuracy of calculations.

Blank cells resulting from a lack of collected data seem to be read in Google Sheets as 0. Ideally, blank cells would not be included in any calculation, or they would be labelled as something else like "incomplete data". How would I exclude/label the cells that are left blank in an equation like:

=ARRAYFORMULA(ifs({D2:D61}>0, "positive", {D2:D61}=0, "no change", {D2:D61}<0, "negative"))

Alternatively, general advice on how to deal with the absence of "after" or "before" data would be good, in terms of calculating a margin of error or excluding rows in specific instances. The survey has multiple questions, and so the distribution of blank answers across all questions makes it impossible to just exclude a few respondents.

  • Welcome to webapps. Sharing an example spreadsheet would really help. – marikamitsos Aug 6 '19 at 22:22

Your Answer

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

Browse other questions tagged or ask your own question.