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.

Find more posts tagged with

Accepted answers

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,

All comments

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,

Jennifer Parins

Worked PERFECTLY!!

Thanks for your expertise.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.