Use of Averageif while filtering data in multiple columns
I am attempting to average %complete by team (Team#) where sessions are active (Sessions Scheduled). This requires two HAS(@Cell, Criteria) but I am getting an #UNPARSEABLE
=AVERAGEIF([Team#]:[Team#], HAS(@cell, "1" AND [Session Scheduled]:[Session Scheduled], HAS(@cell,"1"), [% Story completed]:[% Story completed])
How can I write this formula so it looks at
- Team# Where = 1 AND
- Session Scheduled = 1 THEN
- Average the above results (% Complete columns)
I feel like I'm close.....🤔
Best Answer
-
That shouldn't be throwing that particular error, but here it is for a checkbox...
=AVG(COLLECT([% Story completed]:[% Story completed], [Team#]:[Team#], HAS(@cell, "1"), [Session Scheduled]:[Session Scheduled], @cell = 1))
Double check that the column name is spelled correctly including spaces.
Answers
-
AVERAGEIF can only house one range/criteria set. To include more, you will need to use an AVG/COLLECT.
=AVG(COLLECT([% Story completed]:[% Story completed], [Team#]:[Team#], HAS(@cell, "1"), [Session Scheduled]:[Session Scheduled], HAS(@cell,"1")))
=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ......................))
-
Hmm.. UNPARSEABLE? Any errors here:
=AVG(COLLECT([% Story completed]:[% Story completed], [Team#]:[Team#], HAS(@cell, "1"), [Session Scheduled]:[Session Scheduled], HAS(@cell,"1")))
Session Scheduled is a checkbox. If I remove it, the Scheduled Session from the formula it works as anticipated: #DIVIDE BY ZERO
Is there a different criteria for the Session Scheduled when checked?
-
That shouldn't be throwing that particular error, but here it is for a checkbox...
=AVG(COLLECT([% Story completed]:[% Story completed], [Team#]:[Team#], HAS(@cell, "1"), [Session Scheduled]:[Session Scheduled], @cell = 1))
Double check that the column name is spelled correctly including spaces.
-
That did the trick. Thank you for your help with this!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!