I have a monthly survey that is sent to our customer to monitor performance. I used grouped/option buttons to answer the questions in which each option is assigned a number and I scored each answer as follows:
1 Exceptional = 4
2 Satisfactory = 3
3 Marginal =2
4 Unacceptable = 1
5 Not applicable = 0
There are 8 questions with a total possible score of 32 points, 100%, if all areas are Exceptional. The simple formula I used was =sum(d4:d11)/32, which works as long as the customer does not choose Option 5, NA. If selected, the total possible points are reduced based on the number of the remaining questions answered, (e.g., 7 of 8 questions answered = total score/28.)
I need an excel formula that will automatically exclude questions that were answered "NA" and calculate the total number of points based on the number of questions answered and divide by the total possible score remaining.

I hope this makes sense. I feel like I am really over thinking this, but it has got me stumped.