Combining multiple columns that meet criteria
I have an inspection form that allows employees to input a compliance value of 1-5. However, all of the questions don't apply for every employee and department. If the question does not apply the employee enters N/A. My goal is to calculate the average score. The problem is that the number of questions is not always the same.
How do I determine the number of questions answered with a 1,2,3,4, or 5 so I use the correct total of questions to calculate the average. Any thoughts would be appreciated.
Best Answer
-
@Jennifer Parins I would:
=COUNTIF([Column 15]@row:[Column25]@row, <>"N/A")
That formula should count all the columns in the selected range that have a value in them that is NOT N/A.
As long as the null answer is exactly N/A, that count should give you the denominator for calculating average (assuming that is how you calculate your average)
Cheers,
dm
Answers
-
@Jennifer Parins how are you writing your average formula? If you are using AVG() it will ignore text and only count numbers. so if they put n/a you should still get a correct average of the numbers they chose.
-
@Jennifer Parins I would:
=COUNTIF([Column 15]@row:[Column25]@row, <>"N/A")
That formula should count all the columns in the selected range that have a value in them that is NOT N/A.
As long as the null answer is exactly N/A, that count should give you the denominator for calculating average (assuming that is how you calculate your average)
Cheers,
dm
-
Worked PERFECTLY!!
Thanks for your expertise.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!