AverageIF formula
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'?
Best Answers
-
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! 🙂
-
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"))
Answers
-
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! 🙂
-
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"))
-
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.
-
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"))
-
Hi Nick,
This works indeed. I get more insights of the collect function now! Thanks a million.
Jef
-
No problem, happy to help! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!