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

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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!