# Average / Collect referencing another sheet and a date range

edited 04/09/24

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

• ✭✭✭✭✭✭

u missed a comma:

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

• ✭✭✭✭✭✭

You can use the OR function to accomplish this.

For Example by the Closed month criteria put in:

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

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

• ✭✭✭✭✭✭

u missed a comma:

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

• edited 04/09/24

Thank you so much! That solved my problem.

• edited 04/09/24

@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?

• Employee

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.