How to calculate averages and ignore NA's
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
-
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.
-
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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives