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

Find more posts tagged with

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

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, ......................))

Ed Olszanowski

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?

Paul Newcome

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.

Ed Olszanowski

That did the trick. Thank you for your help with this!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.