# Average Collect Formula to consider Quarter and Half of Year without help column

Options

I hope you guys are doing great! I am diving deeper and deeper into the abyss of smartsheet formulas, yet I would appreciate your help!

I am calculating average quality results in percentage, per team and per individual. With monthly results was pretty easy, as raw file includes column with month, see:

=IFERROR(AVG(COLLECT({PC_QA_Avg}; {PC_Reviewer}; \$[Grand (Total)]@row; {PC_QA_Month}; Jan\$2; {PC_MP}; \$[Grand (Total)]\$1)); "")

The problem is with Quarters and Half-Years. Raw data does not include this information in columns.

I have created help columns updated manually with applicable information, see:

I could have created an additional sheet with month // quarter // year and use vlookup, but I know that formula won't work with another formula results.

Desired formula should express something like this:

For Q1'23:

=IFERROR(AVG(COLLECT({PC_QA_Avg}; {PC_Reviewer}; \$[Grand (Total)]@row; HERE CONSIDER Jan'23 Feb'23 and Mar'23 from column Month; {PC_MP}; \$[Grand (Total)]\$1)); "")

Is there an option to use in this case AND formula?

As for now, I created walk around solution and have results, but moving forward, I don't want to update Quarter and Half-Year columns manually every time when bulk results are required.

=IFERROR(AVG(COLLECT({PC_QA_Average}; {PC_Reviewer}; \$[Grand (Total)]@row; {PC_QA_Quarter}; [Q1'23]\$1; {PC_MP}; \$[Grand (Total)]\$1)); "")

Thanks in advance for great as always support!,

Roman

• ✭✭✭✭✭✭
Options

You can include AND to bring in all the months within a Quarter/Half Year. It would mean updating your formula each Quarter and Half Year to bring in the correct results. If your raw data has a created date column that actually is always within the Quarter or half year you can also use that and greater than and less than in your formulas. I like this way best because I can add to my sheet summary some start and end date fields and reference those in the formula so I don't have to go in and manually update the formulas just those fields.

Below is how to incorporate the And in the formula

=IFERROR(AVG(COLLECT({PC_QA_Average}; {PC_Reviewer}; \$[Grand (Total)]@row; {Month reference};AND(@cell>1;@cell<4; {PC_MP}; \$[Grand (Total)]\$1)); "")

• Options

Hey,

Thank you for an update. Unfortunately, I don't have a column with month number, only option is to use regular names, like Jan'23, Feb'23 etc.

I have tried to adjust the formula but getting an error (Unparseable).

=IFERROR(AVG(COLLECT({PC_QA_Average}; {PC_Reviewer}; \$[Grand (Total)]@row; {PC_QA_Month}; AND(@cell="Jan'23"; @cell="Feb'23"; @cell="Mar'23"); {PC_MP}; \$[Grand (Total)]\$1)); ""))

Do you maybe know what I messed up here?

Thanks,

Roman

• Options

Hello Team,

I hope you are doing great, is there anybody willing to help on my last clarification?