# Average(Collect with OR Function

Options
✭✭✭

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

Leslie

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
edited 06/02/21
Options

thank you @Leibel S! that worked!