AverageIF formula

Jef Snyders

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'?

Nick Korna

Hi @Jef Snyders,

For your first question:

=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! 🙂

jmyzk_cloudsmart_jp

Hi @Jef Snyders

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

https://app.smartsheet.com/b/publish?EQBCT=94c81ff984024bddb2304861e7c02ae1

Nick Korna

Hi @Jef Snyders,

For your first question:

=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! 🙂

jmyzk_cloudsmart_jp

Hi @Jef Snyders

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

https://app.smartsheet.com/b/publish?EQBCT=94c81ff984024bddb2304861e7c02ae1

Jef Snyders

https://community.smartsheet.com/discussion/comment/403854#Comment_403854

Hi @jmyzk_cloudsmart_jp ,

Thanks for your wonderful and quick answer. This works indeed.

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.

Nick Korna

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

Jef Snyders

https://community.smartsheet.com/discussion/comment/403910#Comment_403910

Hi Nick,

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

Jef

Nick Korna

No problem, happy to help! 😊