google-sheetsgoogle-sheets-formuladata-quality

Check the data quality in Google Sheets (asking for suggestions)

I'm trying to create a sheet to check the data quality from a survey in Google Sheets the document have this format:

enter image description here

So basically I was using this formula =COUNTIF(B2:F2,"Don't know") to count Don't know, empty spaces, 0's and numbers > than 9, if the percentage from the counts is bigger than 0.31 or 31% the data quality is not good, as an example I'll take row 2, it has responses from Column B-F (5 cells) and this is the total number of responses, the count of bad data is 2, so in percentage will be represented the quality as 2/5 = 40%, but I want to do the same for the all survey questions in a survey with 1600 questions, this document,the document attached contains a small piece of data from the original survey. So I would like to ask for a better solution that counting all the columns and the rows, I'm asking for a recomendation or how I should check the data quality, basically in the document I have all the formulas that I need but I would like to have all the formulas in just one. Also in the document in the desirable output column there is the final result that I would like to have. Hoping my explanaiton was good about the desired output.

Solution

  • You may try this:

    =byrow(B2:F,lambda(z,if(offset(index(z,,1),0,-1)="",,
      let(x,(countif(z,"Don't Know")+countblank(z)+--(index(z,,5)=0)+--(index(z,,5)>9))/columns(z),
      if(x>0.3,"Bad","Good")&" Quality"))))

    enter image description here

    if you wish to see the score alongside the quality status then use:

    =byrow(B2:F,lambda(z,if(offset(index(z,,1),0,-1)="",,let(x,(countif(z,"Don't Know")+countblank(z)+--(index(z,,5)=0)+--(index(z,,5)>9))/columns(z),{to_percent(x),if(x>0.3,"Bad","Good")&" Quality"}))))