AVG COLLECT for End Date with current YEAR
I am getting an #UNPARSEABLE error with this Sheet Summary formula:
=AVG(COLLECT(Duration:Duration,Level, 0, Status:Status, "Complete",Status:Status, "In Progress", [End Date]:[End Date], IFERROR(YEAR(@cell),0)=YEAR(TODAY())))
I would like to return the average Duration where: row Level is 0, Status is Complete or In Progress, and End Date contains 2023.
Any help would be appreciated.
Best Answer
-
@Hollie Green thank you for your input. Your suggestion got me thinking, so I made another revision to the formula which seems to work without using a helper column.
=AVG(COLLECT(Duration:Duration, Level:Level, 0, Status:Status, OR(@cell = "In Progress", @cell = "Complete"), [End Date]:[End Date], AND(@cell <= DATE(2023, 12, 31), @cell >= DATE(2023, 1, 1))))
Answers
-
You will need to add an Or formula. Your current formula would require the Status to be both Complete and In Progress which is not possible. I'm not the best at AND OR formulas so may need some tweaking but would try the below.
=AVG(COLLECT(Duration':Duration,Level,0,Or(Status:Status,"Complete",Status:Status,"In Progress"),[End Date]:[End Date],IFERROR(YEAR(@cell),0)=YEAR(TODAY())))
-
@Hollie Green I appreciate your time and desire to help, but unfortunately that did not work. I still get the #UNPARSEABLE error.
-
Update: I revised the formula slightly (including Level:Level) and now I get #INVALID COLUMN VALUE
=AVG(COLLECT(Duration:Duration, Level:Level, 0, Status:Status, "Complete", Status:Status, "In Progress"), [End Date]:[End Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
I found a resolution. It requires creating a helper column for the year that references your End Date. Below is the formula.
=AVG(COLLECT(Duration:Duration, Level:Level, 0, Status:Status, OR(@cell = "In progress", @cell = "Complete"), Year:Year, YEAR(TODAY())))
-
@Hollie Green thank you for your input. Your suggestion got me thinking, so I made another revision to the formula which seems to work without using a helper column.
=AVG(COLLECT(Duration:Duration, Level:Level, 0, Status:Status, OR(@cell = "In Progress", @cell = "Complete"), [End Date]:[End Date], AND(@cell <= DATE(2023, 12, 31), @cell >= DATE(2023, 1, 1))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!