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

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!