How to calculate averages and ignore NA's

GetITDone

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?

 

Paul Newcome

Could you provide some screenshots? It is doable, but it would be a lot easier to help if we had a visual for this one.

GetITDone

I thought I added it. Here are 2 screen shots.   The Avg screen you will see I am only selecting Project Man.as these are sub totals (for granularity)

Just to add to this a bit. I only have selected  the Yes column to do my averages which will not work.  Business rule is to Calculate the average off all answers (YES and No but exclude the NA's.

tx

GetITDone

My apologies adding to this rule. I only want to calculate the avg for all the yes's, count the no's and exclude the NA from the average scores.

Paul Newcome

I am still not seeing any screenshots (may be my computer). Going row by row, to average the Yes's...

 

=COUNTIFS([First Column]@row:[Last Column]@row, "Yes") / COUNTIFS([First Column]@row:[Last Column]@row, NOT(@cell = "N/A"))

 

Counting the No's would be...

 

=COUNTIFS([First Column]@row:[Last Column]@row, "No")