AVG COLLECT for End Date with current YEAR

Options

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

  • Paula King
    Paula King ✭✭
    Answer ✓
    Options

    @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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Options

    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
    Options

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

  • Paula King
    Options

    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
    Hollie Green ✭✭✭✭✭✭
    Options

    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
    Paula King ✭✭
    Answer ✓
    Options

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