Average(Collect with OR Function

Leslie Ambs

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

Leslie Ambs

@Leibel S

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.

Leibel S

@Leslie Ambs

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

Leslie Ambs

@Leibel S

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.

Leibel S

@Leslie Ambs

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.

Leslie Ambs

thank you @Leibel S! that worked!

appreciate your help!!