Create a formula to give me an average after reviewing several columns

I am trying to write a complex formula for the following snippet.

I want to find the average of the % allocation if the Assigned To is Blake Steinke and the Status is not Complete. I keep getting an "Incorrect Argument Set". This is also a filtered view.

I can do this with 3 helper columns and smaller formulas:

Test Column 1. =SUMIFS([% Allocation]:[% Allocation], [Assigned To]:[Assigned To], "Blake Steinke", [Status]:[Status], <>"Complete")

Test Column 2. =COUNTIFS([Assigned To]:[Assigned To], "Blake Steinke", Status:Status, <>"Complete")

Test Column 3. =[Test 1]@row / [Test 2]@row

However, I do not want to do this. Ultimately, I would like to have this formula in a sheet summary. I will have about 25 people and want to put on a dashboard.

Thanks,

Find more posts tagged with

Accepted answers

JR90

@Lucinda Bugbee try this formula out:

=AVG(COLLECT([% allocation]:[% allocation], [assigned to]:[assigned to], "Blake Steinke", Status:Status, <>"Complete"))

Please let me know if it works.

All comments

JR90

@Lucinda Bugbee try this formula out:

=AVG(COLLECT([% allocation]:[% allocation], [assigned to]:[assigned to], "Blake Steinke", Status:Status, <>"Complete"))

Please let me know if it works.

Lucinda Bugbee

Yes, this worked! You are a rock star! Thank you.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.