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

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

## Answers

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭
edited 01/12/23
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Week formula =WEEKNUMBER(Date@row)

Month formula =MONTH(Date@row)

Year formula =YEAR(Date@row)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!