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.

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.

Cayla Davis

@Paul Newcome you are a serious wizard!!! thank you for the extra detail, im trying so hard to learn all of this, i'm starting the Smartsheet University so hopefully i'll be able to learn more about this without having to bug everyone - appreciate you always helping me out! you're a star!

Paul Newcome

Happy to help. 👍️

Good luck in your learning!