Average / Collect referencing another sheet and a date range

William Gray

I'm trying to average the ticket ages for the current month. I’ve tried several formulas and currently I’m trying this:

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, "April", {SourceClosedYear}, "2024")), "")

The formula needs to include ticket ages where the closed date is blank, and this seems to be where I’m struggling to find a solution.

To get the desired average from the “EOM Case Age” column I need to have the formula look for the following:

Closed: Yes and No

Include: Yes (Uses a Yes/No formula – IF Closed, Active, or Project = Yes)

Closed Month: Current Month (April) and Blanks

Closed Year: Current Year (2024) and Blanks

Date Submitted: All submission dates up to the last day of the current month

Leibel S

u missed a comma:

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell= "April", @cell= "") , {SourceClosedYear}, "2024")), "")

Leibel S

@William Gray

You can use the OR function to accomplish this.

For Example by the Closed month criteria put in:

OR(@cell="April", @cell="")

William Gray

I tried this and got #UNPARSABLE:

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell= "April", @cell= "") {SourceClosedYear}, "2024")), "")

What did I do wrong? I'm still new to most of this.

Leibel S

u missed a comma:

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell= "April", @cell= "") , {SourceClosedYear}, "2024")), "")

William Gray

Thank you so much! That solved my problem.

William Gray

@Leibel S I have a different problem with the formula now. I get the same number no matter what month I put into the formula.

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell = "March", @cell = ""), {SourceClosedYear}, OR(@cell = "2024", @cell = ""))), "")

Result 21.56

=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell = "April", @cell = ""), {SourceClosedYear}, OR(@cell = "2024", @cell = ""))), "")

Result 21.56

do I need to add an AND/OR or AND/IF somewhere in the formula to distinguish between different closed months and still include tickets that are still active?

Genevieve P.

Hi @William Gray

Were you able to figure this out? Is it possible that due to the blank rows, your averages are the same for those two months? You can test this by adding a filter on to your source sheet with the same conditions.

William Gray

I already had a filter that matches the same conditions to produce the averages manually until I could come up with a formula that does it automatically. The formula I used above doesn't produce the same result as the filter. There is a variance of about .04 to .02 between them. The average changes daily as the case ages change.