AVG COLLECT formula with two sets of criteria (Week 52 in two years)

Laurie Mason

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

Paul Newcome

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

Paul Newcome

You are going to want to average two COLLECT functions together (not the AND function).

=IFERROR(AVG(COLLECT(..........), COLLECT(..........)), "")

Laurie Mason

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

Paul Newcome

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?

Laurie Mason

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.

Paul Newcome

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?

Laurie Mason

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)

Paul Newcome

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

Laurie Mason

Week formula =WEEKNUMBER(Date@row)

Month formula =MONTH(Date@row)

Year formula =YEAR(Date@row)

Paul Newcome

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

Paul Newcome

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

Laurie Mason

https://community.smartsheet.com/discussion/comment/357116#Comment_357116

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

Paul Newcome

Happy to help. 👍️