AVERAGEIF function not returning expected value

Hi Smartsheet Community!

I have a sheet with a score column where I want to average the points in the score column for a particular category (which is a multi-select field). I've tried AVERAGEIF and AVG(COLLECT formulas but I'm unable to get either of them to work. They either return "0" or are blank, respectively. What am I doing wrong?

=AVERAGEIF(Category:Category, "Gap Closure", Score:Score)

=IFERROR(AVG(COLLECT(Score:Score, Category:Category, "Gap Closure")), "")


Best Answer

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

    @Nate H,

    Without changing the formula I have it working as a sheet summary with the score coming from an IF statement:

    The only thing I can think of is if your score determining IF statement is IF(<whatever condition>,"1") etc. - if you remove the ""s and just leave the number then it should work - using this I can duplicate your 0 outcome:

    Do you want to give this a try and see if this is the culprit?

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Nate H,

    I think this formula should do what you're after:

    =AVERAGEIF(Category:Category, HAS(@cell, "Gap Closure"), Score:Score)

    Hope this is of some assistance. If you've any questions etc. then just post! 😊

  • Nate H
    Nate H ✭✭✭✭

    @Nick Korna Thanks for the response. I neglected to mention that I'm looking to use the formula in a sheet summary field. Not sure if that is making a difference. But I tried your formula there and I'm still getting a "0" return.

  • Nate H
    Nate H ✭✭✭✭
    edited 02/08/23

    So I've figured out why I'm not getting a return. My Score column is formula driven as well. I have it putting in a 1, 2, or 3, based on the selection of another single dropdown column that isn't a numeric value but a name. I guess the AVERAGEIF won't work against an IF formula.

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

    @Nate H,

    Without changing the formula I have it working as a sheet summary with the score coming from an IF statement:

    The only thing I can think of is if your score determining IF statement is IF(<whatever condition>,"1") etc. - if you remove the ""s and just leave the number then it should work - using this I can duplicate your 0 outcome:

    Do you want to give this a try and see if this is the culprit?

  • Nate H
    Nate H ✭✭✭✭

    Thanks, @Nick Korna . The quotes were indeed the problem!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No worries; glad you have a solution now! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!