GOOGLE SHEETS: Ignoring text when calculating an average

[bluargent]

Hi! Reposting since I need this by the end of the month with more info.

I have a rather complex rating sheet that I am attempting to proliferate throughout my company. I have encountered the first use error in the wild when a coworker entered "NA" for a rating, rather than leaving the cell blank, which appears to have messed up my calculations. How can I not include strings in the sum?

Table looks like this:

The rating is calculated using the following function:
=IFERROR(20*SUMPRODUCT(G3:N3,'Grading Methodology'!$B$4:$I$4)/SUMPRODUCT(--((G3:N3)>0),'Grading Methodology'!$B$4:$I$4),"No Rating Yet")

where the "grading methodology" tab has percentage weights for each of the categories.

The intent is for the rating be out of 100 even if a score or two is missing. However, it seems that Sheets treats strings like "NA" as a zero - I would like for strings to be treated as a blank cell, so the rating can still be calculated
Another screenshot that shows the score working even if only one person scores:

Thanks!

To be clear, this is a Google Sheets spreadsheet

 

[Fluff]

Duplicate to: GOOGLE SHEETS: Treat text as a blank cell when running a calculation

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.