Average / Collect referencing another sheet and a date range
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
Best Answer
-
u missed a comma:
=IFERROR(AVG(COLLECT({SourceEOMCaseAge}, {SourceIncludeEOM}, "Yes", {SourceClosedMonth}, OR(@cell= "April", @cell= "") , {SourceClosedYear}, "2024")), "")
Answers
-
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")), "")
-
Thank you so much! That solved my problem.
-
@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?
-
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.
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!