Skip to main content

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.

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!

Thanks!



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



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.

Reply


Terms & Conditions