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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!