AverageIf Dropdown (Multi-Select) cell contains an attribute/value
Hi Smartsheet Community, thanks in advance for all your help. I've been able to learn so much from the help you provide to others.
I have two sheets I am working with for this experiment:
- A master project list, with unique projects aligned to an objective and the option for different key result attributes (Drop down Multi-select). Additionally these projects all have a project completion values.
- A second "Results" sheet holds a list of objective along with each possible key result.
I would like to return the average of the projects' completions based on the key result multi-select in the Master project list. I've been successful in getting a formula to work when there is one single Key Result selected for the project, but when there are multiple Key results attributed to a project, the formula breaks.
I believe this requires a combination of (SumIfs/CountIfs) or AverageIf or even Average(Collect(). I've tried several combinations but what I seem to keep running up against is the criterion syntax in the formula to evaluate the multi-drop down box.
I've attached pictures of my examples:
"Master Project List":
"Results" Sheet: (w/ Explanation column & highlighted desired results where formula needs to go)
Best Answer
-
I agree with @Genevieve P however I would use the CONTAINS function as an alternative instead of HAS.
=AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, CONTAINS([Objective/Key Results]@row, @cell)))
Answers
-
Hi @bburrets
You can use a combination of AVG(COLLECT to do this!
Since your Objectives are listed in the Parent row of the same column in your second sheet, you will need to reference the Parent as part of your criteria. In addition, since your Key Results column is a multi-select column you'll need to add in a HAS statement when looking for the Key Result in that row.
Try this:
=AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, HAS(@cell, [Objective/Key Results]@row)))
This will AVG the values in the % Complete column, but only if the following criteria are matched:
- The Objective in the other sheet = the Parent (the Objective) of this row in the Objective/Key Results column
- The Key Results in the other sheet = HAS the Key Result in this row
Here are some Help Center articles I used to build this: COLLECT Function / Cross Sheet Formulas / @cell and @row / PARENT function / HAS function
Let me know if this works for you, or if you have any other questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
As a quick follow-up, I realized you'll receive an error if there are no matches. To fix this, wrap an IFERROR Function around the whole formula:
=IFERROR(AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, HAS(@cell, [Objective/Key Results]@row))), "")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I agree with @Genevieve P however I would use the CONTAINS function as an alternative instead of HAS.
=AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, CONTAINS([Objective/Key Results]@row, @cell)))
-
Thank you @Genevieve P & @Paul Newcome . This is perfect and exactly what I was needing.
-
Great!! Happy to help
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Apparently I was mistaken in some info I provided. CONTAINS and HAS actually work the same way when referencing a multi-select type of column. I will edit my post to correct that.
@Genevieve P Thanks for setting me straight. Haha.
EDIT: I tried to edit my original post, but it won't let me. I am assuming it is because it has already been flagged as a "Best Answer".
All, please note that my solution using the CONTAINS function is actually only a second option. Genevieve's solution using the HAS function works just fine. I was mistaken.
-
Haha no problem!
@bburrets the only reason I'd use HAS over CONTAINS is in case you had some other value that had "Retire" in it... for example, if you had "Retire" and "Retire Again" but only searched if the cell CONTAINS "Retire", you'd receive data for both of these. In your instance it looks like all of your values are unique, so you can use either option. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P Please see my edit above. I can't edit my original comment. Normally I would just not worry about it, but I don't want incorrect information being flagged as the "Best Answer". Is there any way I can get that taken care of whether it be to flag my correction so that it gets bumped to right underneath or switching the "Best Answer" from my response to yours?
-
As a Moderator, I was able to adjust your comment - it's still a correct answer so I've left it as Accepted (I hope that's ok!)
Let me know if there's anything else you'd like to add to that first post and I'm happy to paste it in. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P That'll work just fine. Haha. Much obliged!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!