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

Hello Smart-Heads!

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

Find more posts tagged with

Accepted answers

All comments

Hollie Green

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

Romano el Polako

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

Romano el Polako

Hello Team,

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

Thanks in advance,

Roman

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.