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

Lori Flanigan

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

Paul Newcome

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.

Paul Newcome

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.

Lori Flanigan

Thank you, Paul! Yes, I have mentioned the concern you pointed out; they want to leave it as is for now, anyhow.

Thanks again!

Lori

Paul Newcome

Happy to help. 👍️

Lori Flanigan

Paul, I can't stop thinking about the month crossover. I know it will come up in the future. How would the formula change to accommodate that crossover?

Thanks,

Lori

Paul Newcome

You would make the start date less than or equal to the end of the month and the end date greater than or equal to the start of the month.