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 multiselect 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

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

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

@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.

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.

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?

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

No worries; glad you have a solution now! 😊
Help Article Resources
Categories
Check out the Formula Handbook template!