Collect and HAS Functions

Todd Aldrich

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?

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.