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 TMCRNI, TMCARFI, FIGCRNI, FIGCARFI, 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 ="TMCRNI" criterion
=(AVG(COLLECT({FIU ScoreCard Score}, {FIU QC Scorecard Period}, =$[Review Period]$1, {FIU QC Scorecard Analyst}, =Analyst@row, OR({FIU QC Scorecard Function}, ="TMCRNI",
{FIU QC Scorecard Function}, ="TMCARFI", {FIU QC Scorecard
Function}, ="FIGCRNI", {FIU QC Scorecard Function},
="FIGCARFI", {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 = "TMCRNI", @cell = "TMCARFI", @cell = "FIGCRNI", @cell = "FIGCARFI", @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
Check out the Formula Handbook template!