How do you create an "AverageIf" formula for more than 1 criteria in the same column?

Hi Team,

I am using a formula calculation sheet to try to work out how to create a formula to average %s in a Smartsheet where the Line of Business Column (in some cases) has more than 1 Business unit e.g. Sales and Sales (Local Sales).

I have tried Average If functions, AvgCollect, AverageAnd, but have had no luck. I just want the average of the 2 averages below, but would love it in one formula based on the master Smartsheet to make sure its accurate.

E.g.

Any help would be greatly appreciated

Find more posts tagged with

Formulas

Accepted answers

All comments

Faaez Kamaal

I don't use the parent/child hierarchy. So, shooting from the hip on this one.

Would =AVG(CHILDREN([FY21 Average]1),CHILDREN([FY21 Average]2)) work for you? Change the numbers to match your row numbers.

JayneB

Thank you for sharing, however sadly it did not, it came up with an error message:

Paul Newcome

I would just average the cells directly.

=AVG([FY21 Average]5, [FY21 Average]6)

JayneB

Thanks Paul, for some reason this doesn't come to the same average as the total column average. However there are some texts cells within the list so I am wondering if this is creating the discrepancy between the averages:

From the master sheet:

Avg of the avgs (top row):

I did another test with another group where there was a bigger gap:

From the master sheet:

Avg of the avgs (top row):

Paul Newcome

What are your existing formulas for the other individual rows that you are wanting to get the average of?

JayneB

Hi Paul,

=AVERAGEIF({Line of Business}, [Primary Column]@row, {Year Data})

Paul Newcome

Try this:

=AVERAGEIF({Line of Business}, OR(@cell = [Primary Column]5, @cell = [Primary Column]6), {Year Data})

(or whatever your row numbers are)

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.