Creating a "running tally" and overall percentage score of multiple Yes/No/N/A questions

JonathanB · ‎Jun 28, 2021

There’s probably an easier way to do this, but I was quite pleased with the solution I came up with. I’d be interested to hear what you think…

I’m building an auditing tool composed of a large number of questions that can be answered Yes, No, or N/A. This needs to give an overall “score” as the final output, as a percentage, where questions for which “N/A” is selected aren’t counted towards the overall score, i.e. the score is calculated by the total number of questions answered “Yes”, divided by the total number of applicable questions.

I set up a test version of this tool and filled in some results here.

The “Score” field is worked out as follows:

{Yes}
IF({Q1}="Yes", "1", "") & IF({Q2}="Yes", "1", "") & IF({Q3}="Yes", "1", "") & IF({Q4}="Yes", "1", "") & IF({Q5}="Yes", "1", "")

{Yes or no}
IF(OR({Q1}="Yes", {Q1}="No"), "1", "") & IF(OR({Q2}="Yes", {Q2}="No"), "1", "") & IF(OR({Q3}="Yes", {Q3}="No"), "1", "") & IF(OR({Q4}="Yes", {Q4}="No"), "1", "") & IF(OR({Q5}="Yes", {Q5}="No"), "1", "")

{Total yes}
LEN({Yes})

{Total Yes or No}
LEN({Yes or no})

{Score}
SUM({Total Yes}/{Total Yes or No})

Just sharing this in case it’s helpful to anyone, and to see if there are any alternatives to the {Yes} and {Yes or no} fields, which will get very long as there are about 120 questions in the auditing tool!

Peace.

jonathanlaniado · ‎Jun 28, 2021

Thanks for sharing. I’m also not sure if this is the most optimal way to reach what you’re trying to achieve, but I hope it helps!

You can get rid of the {Yes} and {Yes or No} fields by replacing the {Total Yes} and {Total Yes or No} formulas:

New {Total Yes}:

SUM(
  IF(Q1="Yes",1),
  IF(Q2="Yes",1),
  IF(Q3="Yes",1),
  IF(Q4="Yes",1),
  IF(Q5="Yes",1)
)

New {Total Yes or No}:

SUM(
  IF(Q1!="N/A",1),
  IF(Q2!="N/A",1),
  IF(Q3!="N/A",1),
  IF(Q4!="N/A",1),
  IF(Q5!="N/A",1)
)

And if you want just one field called Score, you can combine the two fields above:

SUM(IF(Q1="Yes",1),IF(Q2="Yes",1),IF(Q3="Yes",1),IF(Q4="Yes",1),IF(Q5="Yes",1))
/
SUM(IF(Q1!="N/A",1),IF(Q2!="N/A",1),IF(Q3!="N/A",1),IF(Q4!="N/A",1),IF(Q5!="N/A",1))

I would be interested to see if there’s an even more efficient way to do this!

JonathanB · ‎Jun 28, 2021

Thanks!

I was wondering about using one of the “count” functions, possibly COUNTA() - but not quite sure how to do that.

Justin_Barrett · ‎Jul 04, 2021

There is. An expression like Q1 = "Yes" returns True or False, but Airtable will often treat these as 1 and 0 respectively. In that light, your first formula could be simplified further to:

SUM(
  Q1="Yes",
  Q2="Yes",
  Q3="Yes",
  Q4="Yes",
  Q5="Yes"
)

Tricks like this—and many others—are part of a course that I’m building all about Airtable formulas. It’s taking longer than I hoped to finish it, but I’m hoping to have it wrapped in the next month or two.