Avg(Collect( using a date criteria?

Emory ✭✭✭✭
edited 08/17/23 in Formulas and Functions


=IFERROR(AVG(COLLECT({Delta Testing1}, {Assigned To1}, TeamMember@row, {RT1}, "<>Parent", {Delta Testing1}, "<>", {Delta Testing1}, "<30", {Testing Month1}, "=6")), "")

The above formula works as intended, but I am trying to add one last condition. I need it to also filter only data that is within the last year.

I have tried this:

=IFERROR(AVG(COLLECT({Delta Testing1}, {Assigned To1}, TeamMember@row, {RT1}, "<>Parent", {Delta Testing1}, "<>", {Delta Testing1}, "<30", {Testing Month1}, "=6", {Testing Scheduled Year1}, >{General Metrics Year} - 1)), "")

{General Metrics Year} is the current year - a reference to a generic page my company uses. The cell it references reads "2023"

{Testing Scheduled Year1} References the column I want to apply this criteria to. The year is written in this column such as "2023". So, I know that there should be a match.

I have tried many other ways to get the ">CurrentYear-1" to work. I am trying to put this in a way that will auto update each year, which is why I am referencing the current year in this formula.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!