Average Collect Formula

Archie123456

i want to add =Average(Collect()) to the formula below while wanting to know the average for {Formal Total}. The below is the criteria for it.

({Formal Capacity}, >=10, {Formal Capacity}, <=269, {Date Budget Formal}, IFERROR(YEAR(@cell ), 0) = 2021)

Itai Perez

Hi @Archie123456

Can you share some more details and perhaps a screenshot to help understand the formula? Also, what are the issues/errors you are facing?

Archie123456

I have a capacity for example 100. I want to average how much that 100 capacity costs with a rolling month by month.

heyjay

=AVG(COLLECT(
[ColToAvg]:[ColToAvg], 
[Range1]:[Range1], "Criteria1", 
[Range2]:[Range2], "Criteria2",
… … …
))

You can follow the format above to set up your formula.

Here are some similar posts, https://community.smartsheet.com/discussion/110049/average-collect-w-3-criteria

Paul Newcome

Give this a try:

=AVG(COLLECT({Formal Total}, {Formal Capacity}, >=10, {Formal Capacity}, <=269, {Date Budget Formal}, IFERROR(YEAR(@cell), 0) = 2021))

Archie123456

It comes up with #DIVIDE BY ZERO

Paul Newcome

That means you do not have any rows matching the range/criteria sets. What does this output:

=COUNTIFS({Formal Capacity}, >=10, {Formal Capacity}, <=269, {Date Budget Formal}, IFERROR(YEAR(@cell), 0) = 2021)

Archie123456

Hi Paul,

I sorted it out thanks, next bit of it. i want to be able to add this so it counts it as a rolling 12 month period and averages out for each month, do you know why this formula does not work?

=AVG(COLLECT({Formal Total}, {Formal Capacity}, >=10, {Formal Capacity}, <=269, {Date Budget Formal}, AND(IFERROR(MONTH(@cell ), 0) = 2, IFERROR(YEAR(@cell ), 0) = IF(TODAY() >= DATE(YEAR(TODAY()), 2, 1), YEAR(TODAY()), YEAR(TODAY()) - 1))))@cell

Paul Newcome

What happens if you delete the "@cell" from the end?

Archie123456

It says Divide By Zero but when i go back to the other formula where it calculates it by year it comes back with an average.

Paul Newcome

Do you have any rows in the source data where the capacity is between 10 and 269 and the date is in February 2024?