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?

Tags:

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

  • Greg F
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!