Average(Collect with OR Function
Hi there,
I am trying to average a subset of items based on multiple criteria:
Intent is to average the score if the following criteria apply:
*Period equals review period selected in absolute cell reference
*Analyst equals analyst in that row
*and the function includes TM-CRNI, TM-CARFI, FIG-CRNI, FIG-CARFI, or Account Closure.
Basically, I'd like to exclude 3 other functions and only average the above. My current formula is as follows but it is not working. (I get an #Invalid Ref error). I have tried it with and without the comma before the ="TM-CRNI" criterion
=(AVG(COLLECT({FIU ScoreCard Score}, {FIU QC Scorecard Period}, =$[Review Period]$1, {FIU QC Scorecard Analyst}, =Analyst@row, OR({FIU QC Scorecard Function}, ="TM-CRNI",
{FIU QC Scorecard Function}, ="TM-CARFI", {FIU QC Scorecard
Function}, ="FIG-CRNI", {FIU QC Scorecard Function},
="FIG-CARFI", {FIU QC Scorecard Function}, ="Account Closure"))))
thanks in advance!!!
Leslie
Best Answer
-
Unfortunately, I am still getting the #Invalid Ref error.
I'm trying to average an entire column on another sheet if those criteria match.
The data on the other sheet consists of multiple Analysts, multiple review periods (months), and multiple functions.
My sheet where this formula is located has a row for each analyst and uses a drop down to select the month we want to see the averages for.
Answers
-
Below should be the correct syntax. Try and let me know
=AVG(COLLECT({FIU ScoreCard Score}, {FIU QC Scorecard Period}, $[Review Period]$1, {FIU QC Scorecard Analyst}, Analyst@row, {FIU QC Scorecard Function}, OR(@cell = "TM-CRNI", @cell = "TM-CARFI", @cell = "FIG-CRNI", @cell = "FIG-CARFI", @cell = "Account Closure")))
-
Unfortunately, I am still getting the #Invalid Ref error.
I'm trying to average an entire column on another sheet if those criteria match.
The data on the other sheet consists of multiple Analysts, multiple review periods (months), and multiple functions.
My sheet where this formula is located has a row for each analyst and uses a drop down to select the month we want to see the averages for.
-
The #Invalid Ref error means that there is something wrong with one of your cross sheet references. Review those to see if they are set up properly.
-
thank you @Leibel S! that worked!
appreciate your help!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!