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.)