Combining multiple columns that meet criteria
I have an inspection form that allows employees to input a compliance value of 15. 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!