Collect and HAS Functions

I need to find the average dollar amount across a range only if the fiscal year IS 2018 and a multiselect column HAS a given value

=AVG(COLLECT([Funding Amount]:[Funding Amount], Subsector:Subsector, HAS(@cell "Cell Therapy"), [Fiscal Year]:[Fiscal Year], "2019"))

Is what I'm trying to do feasible?

Find more posts tagged with

Formulas

Accepted answers

All comments

Mark Cronk

Your formula should work. Are you getting an error? HAS only find exact matches. Confirm "Cell Therapy" is a response.

=AVG(COLLECT([Funding Amount]:[Funding Amount], Subsector:Subsector, HAS(@cell "Cell Therapy"), [Fiscal Year]:[Fiscal Year], "2019"))

Mark

Todd Aldrich

Thanks Mark, yes it still returns "unparseable". Does the HAS function need to be nested within an IF function?

Greg F

I am trying to do something similar. I think the problem is you have to put the search within HAS(). Mine are exact matches and nothing is working.

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.