Discussions

View Only

Using a formula numeric field to find an average score of several questions but want to exclude questions that are N/A Kevin Kevin01-29-2019 17:25 QuickBaseCoach Dev./Training01-29-2019 17:40 Kevin Kevin01-29-2019 18:13 • 1.  Using a formula numeric field to find an average score of several questions but want to exclude questions that are N/A

Posted 01-29-2019 17:25
I have a formula numeric field [score] that is assigning a value to checkboxes ( [meets], [partial meets], and [does not meet] ) and then calculating the average of those assigned values. I want to be able to a N/A to each question, and have the formula numeric field calculate the average of only those that are applicable. Is there any way to do this?  Thanks
Currently my formula looks like this:

var number Q1 = If([Q1-meets], 1, [Q1-partial meets], 0.6, 0.3);
var number Q2 = If([Q2-meets], 1, [Q2-partial meets], 0.6, 0.3);
var number Q3 = If([Q3-meets], 1, [Q3-partial meets], 0.6, 0.3);
var number Q4 = If([Q4-meets], 1, [Q4-partial meets], 0.6, 0.3);

(\$Q1 + \$Q2 + \$Q3 + \$Q4)/4

• 2.  RE: Using a formula numeric field to find an average score of several questions but want to exclude questions that are N/A

Posted 01-29-2019 17:40
Here is an example where there were "N/A" responses that we did not want to count.  So the denominator counts as "1" if the response was Yes or No. (but zero of "N/A" or blank)

The numerator count yes as a 1, else 0.

(Case([POS Data Representation Criteria Met], "Yes", 1, "No", 0, 0) +
Case([POS Reference # Criteria Met], "Yes",1, "No", 0, 0) +
Case([POS Registration Criteria Met],"Yes",1, "No", 0, 0) +
Case([POS Verify Payments Criteria Met], "Yes",1 , "No", 0, 0) +
Case([POS Verify Summary Page Criteria Met], "Yes",1 , "No", 0, 0) +
Case([POS Attachments Criteria Met], "Yes",1, "No", 0,0))

/
(
Case([POS Data Representation Criteria Met], "Yes", 1, "No", 1, 0) +
Case([POS Reference # Criteria Met], "Yes",1, "No", 1, 0) +
Case([POS Registration Criteria Met],"Yes",1, "No", 1, 0) +
Case([POS Verify Payments Criteria Met], "Yes",1 , "No", 1, 0) +
Case([POS Verify Summary Page Criteria Met], "Yes",1 , "No", 1, 0) +
Case([POS Attachments Criteria Met], "Yes",1 , "No", 1, 0))

• 3.  RE: Using a formula numeric field to find an average score of several questions but want to exclude questions that are N/A

Posted 01-29-2019 18:13
This reply was created from a merged topic originally titled Using a formula numeric field to find an average score of several questions but w....

• 4.  RE: Using a formula numeric field to find an average score of several questions but want to exclude questions that are N/A

Posted 01-29-2019 19:14
That seems like a fantastic solution. Thanks! I will be implementing that right away.