Calculating average with various criteria that need to be met first

Cayla Davis

I have never completed an AVG formula yet, and I am looking at past posts, and i cant find something close to what I am looking for.

I have criteria it needs to meet, before it can calculate the Average

Must contain English
Must contain NDC
Must contain Open
Must Contain 'ALL'

Then i need it to take all the data in the column AverageAHTOccurrence and calculate the AVG

I figured I needed to use the AVERAGEIF since i have criteria it needs to meet, but i'm not sure how i wrap up the formula.

This is what I have so far:
=AVERAGEIF({Language}, HAS(@cell, "English"), {NDC/Graphical}, HAS(@cell, "NDC"), {Status}, HAS(@cell, "Open"), {Airline}, HAS(@cell, "ALL"), {AverageAHTOccurrence})

and im pulling the data from another sheet that sometimes has other values which is why im using HAS

Paul Newcome

The AVERAGEIF function only allows for a single range/criteria set for "filtering". Since you need multiple range/criteria sets, you will need an AVG/COLLECT instead.

=AVG(COLLECT({AverageAHTOccurrence}, {Language}, HAS(@cell, "English"), {NDC/Graphical}, HAS(@cell, "NDC"), {Status}, HAS(@cell, "Open"), {Airline}, HAS(@cell, "ALL")))

Also keep in mind that the only time you need to use the HAS function is if you are searching a multi-select dropdown or multi-select contact type column. If there is only ever going to be one thing in a cell, you can use "@cell = ". A good example of this is Status. Typically, only one status is ever present at any given time within a cell. That means you could use

=AVG(COLLECT(……………………………., {Status}, @cell = "Open", ………………..))

This will cut down on the back-end requirements for processing which can help with sheet speed in larger and/or more complex sheets, and it cuts down on parenthesis that can be missed when you haven't had enough coffee yet.