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
Answers
-
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)); "")
-
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
-
Hello Team,
I hope you are doing great, is there anybody willing to help on my last clarification?
Thanks in advance,
Roman
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!