Avg(Collect( using a date criteria?

Emory

Hello

=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.

Paul Newcome

I forgot the quotes around "Parent".

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

Paul Newcome

What error are you getting?

Emory

=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)), "")

With the Formula above, the error is showing a #Invalid Operation

I recently 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}, 1)), "")

{Testing Scheduled Year1}, 1: I used a helper column in the first sheet to show 1 if the date is within the last year, and a 0 if not. I thought by doing this I would be able to get around the first formulas problem.

I seem to still have an issue with this too. The cell shows blank. But when I remove the IFERROR function, the #Divide by zero error appears. Seems there is still something wrong with the argument {Testing Scheduled Year1}, 1

I am rather new to smartsheets, so if there is a better way to do this I am open to suggestions :)

Thanks

Paul Newcome

Try this:

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

Emory

Thanks,

There seems to be an issue with the @cell portion of this formula.

When putting your formula in directly, I had to link the {Date Column}, but the #UNPARSEABLE error was still occurring.

I reduced the formula to just: =IFERROR(AVG(COLLECT({Delta Testing1}, {Assigned To1}, TeamMember@row, {RT1}, @cell<>Parent)),"")

And the error remained. I am unsure what @cell does, but when I alter this shortened formula to this:

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

Replacing the @cell with "<>Parent", it seems to work and result as intended.

Paul Newcome

I forgot the quotes around "Parent".

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

Emory

This worked perfectly. I tested it out with some fake data to make sure it would update as desired and it does.

Thank you very much

Paul Newcome

Happy to help. 👍️