Average Collect formula

I'm trying to collect the average survey response number for a quarter for the current year and multiply it by 0.2, for a percentage, but it's coming back unparseable.

The first referenced column is a 1-5 number, the months are from a numerical month helper column, 'Created' is the auto-create date. Any ideas?

=AVERAGE(COLLECT({My provider treated me with respect}, {Month}, 4, {Month}, 5, {Month}, 6, YEAR({Created}), YEAR(TODAY()))) * 0.2

Find more posts tagged with

Formulas

Accepted answers

Philip Robbins

I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.

I would change it to:

=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2

Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.

I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.

All comments

Philip Robbins

I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.

I would change it to:

=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2

Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.

I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.

Amy Swan

This worked! Thank you so much!!!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.