AverageIf Statement Not Right
Hello,
I am trying to get the average of a column based on the owner. The range is a column with a formula resulting in a number (=[Planned % Complete]@row- [Actual Complete]@row). The criteria is a contact column. My formula is as follows:
=AVERAGEIF({What The Frog 1}, CONTAINS("Big Bird", {What The Frog 2}))
I have tried several different ways, but continue to get a return of 0. I also tried SumIf, but that did not work either. *Note, I am referencing a different sheet
Thanks for the guidance!
Comments
-
-
It was worth a try, but still returned 0.
-
Are you able to provide screenshots of the source and target sheets? Sensitive/confidential information can be removed, blocked, or replaced with "dummy data".
-
The second screen shot is the source document. The target document is just blank right now, as I haven't fully gotten into it. But I have shown the few cells I have. The first cell in the document is the average without any parameters.
-
=AVERAGEIF({Contact Column}, FIND("Big Bird", @cell) > 0, {Column to Average}
.
Apparently CONTAINS doesn't like contact type columns...
-
That did the trick. I would not have come up with on my own. Many thanks!
Do you know what it would look like to add AND into the function to sort through another parameter? Such as Done is not true. I tried a few different variations of these.... Done Column is currently a check box.
=AVERAGEIF(AND{Contact Column}, FIND("Big Bird",@cell)>0, Complete="false", {Column Average}
=AVERAGEIF({Contact Column}, AND( FIND("Big Bird", @cell) > 0, {Done Column}=false), {Column Average})
-
That would be more along the lines of an AVERAGEIFS function. Kind of like COUNTIF vs COUNTIFS or SUMIF vs SUMIFS. Unfortunately AVERAGEIFS doesn't exist within Smartsheet. Feel free to Submit a Product Enhancement Request.
In the meantime, you can use the COLLECT function within an AVG function to basically work the same way.
.
Here's the layout:
=AVG(COLLECT({Range to average}, {Criteria Range 1}, Criteria 1, {Criteria Range 2}, Criteria 2, ...............................................................))
.
Here's how it would look using your above info:
=AVG(COLLECT({Column Average}, {Contact Column}, FIND("Big Bird", @cell) > 0, {Done Column}, 0))
.
The basic idea behind it:
You are going to COLLECT all of the data within the first range based off of the criteria set forth within the following range/criteria sets.
When you wrap that in an AVG function, you are saying to average all of that data you just collected.
.
COLLECT is a very flexible and very useful function. It is one that I strongly suggest familiarizing yourself with. It has the potential to take your formulas to an entirely different level.
-
Thank you! I appreciate the explanation. That is very helpful for future uses.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!