How to calculate averages and ignore NA's

GetITDone
GetITDone ✭✭
edited 12/09/19 in Smartsheet Basics

Hi,

I really appreciate anyone's feedback. 

I have a scorecard based on 300 questions for surveys.  There are times when certain sections of the survey is not applicable and other times where it is.  Is there a formula I can use that will account or discount for that? 

Use Case; Surveyor does not need to survey anything on Engineering for this survey but does for everything else.

All scores are automatically updated when they are answer to Yes and 1 point is added to the cell yes column and calc are based on all yes’s using this formula.

=IF([Yes/No/N/A]4 = "", "", IF([Yes/No/N/A]4 = "Yes", 1, IF([Yes/No/N/A]4 = "No", "", IF([Yes/No/N/A]4 = "N/A", ""))))

Then the black rows add up all the blue sub averages make up the entire average using this formula

=SUM([Score per  Project MGT]330, [Score per  Project MGT]177, [Score per  Project MGT]86, [Score per  Project MGT]2) / COUNT([Score per  Project MGT]330, [Score per  Project MGT]177, [Score per  Project MGT]86, [Score per  Project MGT]2)

The black row are the overall average and the blue row are only for that process (sorry name of process is not on this screen shot but the parent is called ‘Customer Service on dark blue row.

There are times when certain sections of the survey is not applicable and other time where it is.  Is there a formula I can use that will account or discount for that if the surveyor enters NA under the N/A column so I do not muck up the scoring?

 

Comments