Rolling 12 Months using AVG COLLECT formula help

Options
✭✭✭

Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

• ✭✭✭✭✭✭
Options

I provided a link in my last comment. Is it not working?

Basically I suggested that you use 365 days (or -183 to +183) instead of trying to use dates.

AND(@cell>= TODAY(-183), @cell<= TODAY(183))

• ✭✭✭✭✭✭
Options

Did you see my comment on the other thread from earlier today?

• ✭✭✭
Options

Hi Paul, I don't see it. Can you direct me to it?

• ✭✭✭✭✭✭
Options

I provided a link in my last comment. Is it not working?

Basically I suggested that you use 365 days (or -183 to +183) instead of trying to use dates.

AND(@cell>= TODAY(-183), @cell<= TODAY(183))

• ✭✭✭
Options

Thank you, I will give it a try!

• ✭✭✭
Options

It worked perfectly, thank you so much and I am so sorry I didn't see the early post:)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!