# Average Collect Formula

Options
edited 07/24/24

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)

• ✭✭✭✭✭✭
Options

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

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• Options

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

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

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

...

• ✭✭✭✭✭✭
Options

Give this a try:

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

• Options

It comes up with #DIVIDE BY ZERO

• ✭✭✭✭✭✭
Options

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)

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!