Combining multiple columns that meet criteria

Jennifer Parins

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.

Dale Murphy

@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

Samuel Mueller

@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.

Dale Murphy

@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

Jennifer Parins

Worked PERFECTLY!!

Thanks for your expertise.