Hi! 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
Thanks!
To be clear, this is a Google Sheets spreadsheet
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
Thanks!
To be clear, this is a Google Sheets spreadsheet
Last edited by a moderator:
