AVG COLLECT GREATER THAN VALUE

I have a formula to calculate the average amount within a sheet. I want to change the formula to only collect and average any amounts that are >£10,000.

=AVG(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1"))

Thinking I may need IF and @cell but it keeps giving me #UNPARSEABLE.

Find more posts tagged with

Accepted answers

Leela Lodhi

Hi,
You can modify your formula to include a condition for amounts greater than £10,000 like this:

=AVG(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Amount}, ">10000"))This will average only the amounts that meet your criteria and are greater than £10,000.

Hope this will help!

All comments

Leela Lodhi

Michelle Maas

Fabulous, that worked great! Thank you @Leela Lodhi

Michelle Maas

@Leela Lodhi To build onto this formula, I have another one that needs to AVG only those in the {Archive Sales Conversion} column where the {Archive Sales Amount} is greater than £10,000:

=AVG(COLLECT({Archive Sales Conversion}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1"))

Leela Lodhi

Hi @Michelle Maas,

You can try the following formula to achieve your requirement:

=AVG(COLLECT({Archive Sales Conversion}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Amount}, ">=10000"))

Let me know if you need further assistance!

Michelle Maas

Fantastic, that worked great. Thanks for your help, @Leela Lodhi

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.