Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 28, 2021 09:20 AM
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.
Jun 28, 2021 10:17 AM
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!
Jun 28, 2021 10:24 AM
Thanks!
I was wondering about using one of the “count” functions, possibly COUNTA() - but not quite sure how to do that.
Jul 04, 2021 10:03 PM
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.