AVG COLLECT formula with two sets of criteria (Week 52 in two years)
I'm trying to get an average of time spent in each week reviewing documentation. The problem with counting it each week is that week 52 is divided between the years of 2022 and 2023. I want to to collect the time for week 52 when it is in December 2022 and January 2023. Here is what I have so far. I've tried multiple forms of the formula to no avail. This is the closest I have gotten. I don't get an error message, but it is returning nothing (a blank). When I take out the IFERROR and "" at the end, I get #INVALID DATA TYPE.
=IFERROR(AVG(COLLECT({IPC Review TAT Review}, {IPC Review TAT Week}, Week@row, {IPC Review TAT Year}, ="2022", {IPC Review TAT Month}, ="12", AND({IPC Review TAT Review}, {IPC Review TAT Year}, ="2023", {IPC Review TAT Week}, Week@row, {IPC Review TAT Month}, ="1"))), "")
Best Answer

Try removing the quotes from around the numbers in your COLLECT functions.
Answers

You are going to want to average two COLLECT functions together (not the AND function).
=IFERROR(AVG(COLLECT(..........), COLLECT(..........)), "")

Thanks Paul,
I changed the formula as you suggested (see below) and still get a blank field, no error message. I know from looking at the sheet where this is pulling from that it should give me a number. If I take the IFERROR out then it gives me a #DIVIDE BY ZERO error, so it must not be collecting anything. Any other thoughts?
=IFERROR(AVG(COLLECT({IPC Review TAT Review}, {IPC Review TAT Week}, Week@row, {IPC Review TAT Year}, ="2022", {IPC Review TAT Month}, ="12"), COLLECT({IPC Review TAT Review}, {IPC Review TAT Week}, Week@row, {IPC Review TAT Year}, ="2023", {IPC Review TAT Month}, ="1")), "")

How are you populating the Week, Year, and Month columns? Are you able to provide a screenshot of those columns with a few rows that should be pulling in for both of the COLLECTs?

My formula for all the other weeks (below) is working fine. For this formula I had to add the month.
=IFERROR(AVG(COLLECT({IPC Review TAT Review}, {IPC Review TAT Year}, "2022", {IPC Review TAT Week}, Week@row)), "")
All three of the columns (Week, Year, and Month) in the source sheet are populated using formulas.
Here is a sample of the source sheet.

Are you averaging the TAT (days) for Review column? If so, it looks like those are being stored as text strings (left justified instead of right justified). What formula is being used to output the data in that column?

Yes, we are averaging the TAT (days) for Review colunm. Here is the formula in that column.
=IF([Date Complete]@row = "", {General Metrics Date}  Date@row, [Date Complete]@row  Date@row)

And what are the formulas in the Week, Month, and Year columns?

Week formula =WEEKNUMBER(Date@row)
Month formula =MONTH(Date@row)
Year formula =YEAR(Date@row)

Have you applied any formatting at all to the TAT (days) for Review column?

Try removing the quotes from around the numbers in your COLLECT functions.

That worked! Thank you so much Paul! Something so small, but can make a big difference.

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!