Find Average Number if Criteria is Met

cabbsman

I need to determine the AVG number from a list of numbers if the Year and Week Number criteria are met.

For example, if the Year is 2025 (Year Column) and the Week Number is 1 (Week Number Column), I need the average number of days in the FO Node Upgrades Duration column.

I know that this formula =COUNTIFS({Year}, "2025", {Week Number}, "1" will narrow down the count to Year and Day of Week. But I'm not sure how to get the AVG number from the list in the FO Node Upgrades Duration column.

Thank you,

Mike Tuccillo

Paul Newcome

Try this:

=AVG(COLLECT({Duration}, {Year}, @cell = 2025, {Week Number}, @cell = 1))

Paul Newcome

Try this:

=AVG(COLLECT({Duration}, {Year}, @cell = 2025, {Week Number}, @cell = 1))

cabbsman

That did it! Thank you

Paul Newcome

Happy to help. 👍️

nualamcd

Hi, I need to collect Average (of 'Days Since Created') if the 'Priority' is 'Critical'. How can I use Collect to do that? Thanks

Paul Newcome

@nualamcd You would use a similar syntax, but you would only have one range/criteria set.

=AVG(COLLECT({Range to average}, {Criteria Range}, criteria))

nualamcd

OK, seems we got it to work! Thank you :-)

It just doesn't look like the others because the range doesn't have : between... But glad it's working!

=AVG(COLLECT({Days Since Created}, {Priority}, [Column2]$21))

Paul Newcome

Happy to help. 👍️