Can you average a column per year?

Options
✭✭

I have set-up a metric for the Average of Actual Days to Completion (=AVG({CActual Days to Completion}) column for the whole tracker. The tracker is composed of requests received from 2022 to 2024.

Now my boss has asked me for the Average of Actual Days to Completion for requests received in 2023, only to see how we're doing. Can you please give me a formula regarding this?

• ✭✭✭✭✭✭
Options

You would incorporate the COLLECT function.

=AVG(COLLECT({Range To Average}, {Date Range}, IFERROR(YEAR(@cell), 0) = 2023))

• ✭✭✭✭✭✭
Options

You would incorporate the COLLECT function.

=AVG(COLLECT({Range To Average}, {Date Range}, IFERROR(YEAR(@cell), 0) = 2023))

• ✭✭
Options

I think I need more guidance on this, so I followed the formula as =AVG(COLLECT({Actual Days to Completion},{Date Received}, IFERROR(@cell),0) = 2023)) but I'm getting #UNPARSEABLE result.

• ✭✭
Options

@Paul Newcome, Thank you; it worked! for the 1st try I forgot to write the YEAR function. Again Thank you!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!