How do I derive percentage value from multiple picklist fields

Question

I have multiple picklists that have three possible values namely "Yes", "No" and "NA". For every "Yes" value, a score of 1 will be assigned while for every "No" value a score of 0 will be assigned and for "NA" values I do not want any score to be assigned. I want to aggregate all the scores for those picklist values and divide them by the picklist questions that has scores (exclude NA values). How do I do that?

Assuming that I have only two picklist questions, currently I have

(CASE (Result_2_1__c, "Yes", 1, "No", 0, "N/A", NULL, "Not Reviewed", NULL, NULL) + CASE (Result_2_2__c, "Yes", 1, "No", 0, "N/A", NULL, "Not Reviewed", NULL, NULL))/2

However, that is wrong since I am aggregating even the NULL values and also dividing by all questions available regardless of whether they are NA. (I only want to divide by number of picklists that has score.)

Answer 1

I think you can re-write your formula as follow:

(
CASE( Result_1__c , "Yes", 1, 0) + CASE( Result_2__c , "Yes", 1, 0)
)
/ 
(
CASE( Result_1__c , "Yes", 1, "No", 1, 0) + CASE( Result_2__c , "Yes", 1, "No", 1, 0)
)

meaning: in your numerator, you add +1 only if the selected option is Yes, any other option would be 0. then in your denominator, you add +1 whether the selected option is Yes or No. hope it helps!