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.