AVG COLLECT for End Date with current YEAR

Paula King

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.

Paula King

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

Hollie Green

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())))

Paula King

@Hollie Green I appreciate your time and desire to help, but unfortunately that did not work. I still get the #UNPARSEABLE error.

Paula King

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()))

Hollie Green

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())))

Paula King

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