Use of Averageif while filtering data in multiple columns

Ed Olszanowski

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

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.

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. 👍️