Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

337 3
cancel
Showing results for
Did you mean:  8 - Airtable Astronomer

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.

3 Replies 3  5 - Automation Enthusiast

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!  8 - Airtable Astronomer

Thanks!

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

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. 