# AverageIF formula

Options
✭✭✭

Hi all,

I probably have a stupid question.

But in the attached example, I would like to calculate the average from 'chance to win' column if 'Football teams = KRCG'.

In addition, I would also like to calculate the average from 'chance to win' column if 'Football teams = RSCA' and 'Yes or No = Yes'?

• ✭✭✭✭✭✭
Options

Hi @Jef Snyders,

=AVERAGEIF([Football teams]:[Football teams], "KRCG", [Chance to win]:[Chance to win])

For the second:

=AVG(COLLECT([Chance to win]:[Chance to win], [Yes or No]:[Yes or No], "Yes", [Football teams]:[Football teams], "RSCA"))

Hope this helps, but if you've any problems/questions, then just post! 🙂

• ✭✭✭✭✭✭
Options

Not stupid at all. You've raised an important point about the limitations of the AVERAGEIF function. Dealing with the lack of AVERAGEIFS can be challenging, but some workarounds can help us perform similar calculations. 😀

Here is what I would do;

KRCG:

=AVERAGEIF([Football teams]:[Football teams], "KRCG", [chance to win]:[chance to win])

RSCA & Yes

=AVG(COLLECT([chance to win]:[chance to win], [Yes or No]:[Yes or No], "Yes", [Football teams]:[Football teams], "RSCA"))

• ✭✭✭✭✭✭
Options

Hi @Jef Snyders,

=AVERAGEIF([Football teams]:[Football teams], "KRCG", [Chance to win]:[Chance to win])

For the second:

=AVG(COLLECT([Chance to win]:[Chance to win], [Yes or No]:[Yes or No], "Yes", [Football teams]:[Football teams], "RSCA"))

Hope this helps, but if you've any problems/questions, then just post! 🙂

• ✭✭✭✭✭✭
Options

Not stupid at all. You've raised an important point about the limitations of the AVERAGEIF function. Dealing with the lack of AVERAGEIFS can be challenging, but some workarounds can help us perform similar calculations. 😀

Here is what I would do;

KRCG:

=AVERAGEIF([Football teams]:[Football teams], "KRCG", [chance to win]:[chance to win])

RSCA & Yes

=AVG(COLLECT([chance to win]:[chance to win], [Yes or No]:[Yes or No], "Yes", [Football teams]:[Football teams], "RSCA"))

• ✭✭✭
Options

And what do I have to do in case I don't want to have average, but I would like to check the maximum value:

• KRCG
• RSCA & YES

There is nothing like an 'IF' formula and not sure if I use the 'COLLECT' formula correct.

• ✭✭✭✭✭✭
Options

You would indeed use COLLECT, like so:

=MAX(COLLECT([Chance to win]:[Chance to win], [Football Teams]:[Football Teams], "KRCG"))

=MAX(COLLECT([Chance to win]:[Chance to win], [Football Teams]:[Football Teams], "RSCA", [Yes or No]:[Yes or No], "Yes"))

• ✭✭✭
Options

Hi Nick,

This works indeed. I get more insights of the collect function now! Thanks a million.

Jef

• ✭✭✭✭✭✭
Options

No problem, happy to help! 😊

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!