weighted scoring formula to exclude not applicables

**sav1979** · 06-12-2011, 07:45 AM

Hi All,

I am stuck big time with this. The attached shows a report to collect data for an evaluation. A score of 1 is entered against each category depending on the outcome of the call. The part where I am stuck is if a category is N/A I want the total percentage score to not take this into consideration. As an example. All the categories could score excellent with the exception of telephony skills which is N/A. In this case, the score is 96% but I want the calculation to only include those categories where the whole section is not N/A.

Really hoping that someone can assist with this. I know there are enough bright sparks out there to dig deep and help me through this!!!
Thanks in advance guys

**Roger Govier** · 06-12-2011, 08:27 AM

Hi

Perhaps in cell T9
=IF($U$35=1,0,IF($U$36=1,0.3,IF(COUNTIF(L10:N12,"N/A"),"",IF(SUM(N10:N13)=0,"",N14/M14))))

If this is what you want, then adjust ranges for other areas of your report.

**sav1979** · 06-12-2011, 08:50 AM

Hi Roger, I still get the same total of 96% when I include that formula. If NA is selected for the whole of one category then I lose that percentage from the total. In the case that I put excellent for everything except 'telephony skills' (K15:K17) then I get a total of 96% instead of the 100%. If possible, when there is a full category that is NA, the total should be made up of the remaining categories and if they score excellent in all other areas then the total score would be 100%.

**otidwell** · 06-15-2011, 01:08 PM

I am new to the forum so I hope I am adding this to the correct spot. I am working on a similar scoring spreadsheet and having problems getting the total scoring for all sections but figured out this part. Try adding "<> NA" into your formula. The <> basically says (not to include NA). let me know if this helped.

Originally Posted by sav1979

Hi Roger, I still get the same total of 96% when I include that formula. If NA is selected for the whole of one category then I lose that percentage from the total. In the case that I put excellent for everything except 'telephony skills' (K15:K17) then I get a total of 96% instead of the 100%. If possible, when there is a full category that is NA, the total should be made up of the remaining categories and if they score excellent in all other areas then the total score would be 100%.