Can you average a column per year?

kimmy

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?

Paul Newcome

You would incorporate the COLLECT function.

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

Paul Newcome

You would incorporate the COLLECT function.

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

kimmy

@Paul Newcome

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.

kimmy

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

Paul Newcome

Happy to help. 👍️