AVG(COLLECT) Formula with Multiple Criteria, Including Date Range

I'm trying to write an AVG(COLLECT) formula on a separate metrics sheet to calculate the average number of days in a month for all rows with:

Priority = High

Polling Question = Yes

Start Date >=1/1/2024

Closed Date <=1/31/2024

I've tried numerous formulas, the latest of which is =AVG(COLLECT({IRI-NumberOfDays}:{IRI-NumberOfDays}, {IRI-Priority}:{IRI-Priority}, @cell = "High", {IRI-PollingQuestion}:{IRI-PollingQuestion}, @cell = "Yes")), which comes back as UNPARSEABLE.

Thanks for any help.

Lori Flanigan

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Cross sheet references should only be entered once as a single range.


    Same sheet:

    [Column name]:[Column name]


    Cross Sheet:

    {Range Name}


    Something to keep in mind is that your date ranges will miss anything that is (for example) opened in December and closed in January or opened in January and closed in February. It will only capture rows that were both opened and closed in the month of January.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!