AverageIF formula

Jef Snyders
Jef Snyders ✭✭✭
in Formulas and Functions

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

image.png


· Share on FacebookShare on Twitter

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

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

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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
    image.png


    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!