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
Check out the Formula Handbook template!