Formula Case Scoring using "N/A" Pick list value

Question

I Need to score a form that accounts for N/A selections. current the form is setup like this:

CASE(D_Q1__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q2__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q3__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q4__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q5__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q6__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q7__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q8__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q9__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q10__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +

CASE(D_Q12__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) /11

I would like to incorporate if the selection was "N/A" is doesn't count the question at all reducing the denominator accordingly. is this possible?

Amnon Kruvi · Accepted Answer

Hi Courtney,

The trick here would be changing the constant value 11 to the count of fields that are not N/A. So let's try this:

(CASE(D_Q1__c, "Strongly Disagree", 1,"Disagree", 2,"Neutral", 3,"Agree", 4,"Strongly Agree", 5, 0) +