Collecting and Averaging survey score excluding ""N/A" as an answer

I have a roll-up sheet from a survey in another smart sheet. There are 12 questions and several Project managers that are being surveyed. I currently have 1 issue remaining. I can collect and average the survey scores for each project manager, unless the person completing the survey selected “N/A” as an option. My current code is below, but I can't figure out where to put the formula to ignore “N/A”.

=IFERROR(AVG(COLLECT({CBRE PM Client Satisfaction Survey Range 7}, {CBRE PM Client Satisfaction Survey Range 2}, [Project Manager]@row)), "”)

Find more posts tagged with

Accepted answers

All comments

AravindGP

Hi @scottposey

Smartsheet will usually ignore any result that is not a percentage, assuming you've the answers listed as a percentage or number in the Average/Collect formula. It will help if you could share a sample data set of the responses that correspond to CBRE PM Client Satisfaction Survey Range 7.

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.