Sheet summaries OVERALL average

I was able to resolve a #DIVIDE BY ZERO Error message by using this formula =IFERROR(COUNTIFS(I:I, 1, C:C, 1) / COUNT(Hinge:Hinge), 0).

However, I am currently hitting a road block on getting an accurate average from my sheet summaries.

Is there a way to get an average of all summaries not labeled OVERALL that will only count averages for summaries greater than 0?

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

Genevieve P.

Hi @Ricky Mayfield

Sheet Summary fields are individual ranges so you won't be able to use one "range" to reference every field in the Panel.

What I would do in this instance is use IF statements to add together all of the values in the desired fields (if they are greater than 0)

IF([Field 1]# > 0, [Field 1]#) + IF([Field 2]# > 0, [Field 2]#)

then divides that total by the number of fields that are greater than 0.

IF([Field 1]# > 0, 1) + IF([Field 2]# > 0, 1)

Ex:

=(IF([Field 1]# > 0, [Field 1]#) + IF([Field 2]# > 0, [Field 2]#) + IF([Field 3]# > 0, [Field 3]#) + IF([Field 4]# > 0, [Field 4]#)) / (IF([Field 1]# > 0, 1) + IF([Field 2]# > 0, 1) + IF([Field 3]# > 0, 1) + IF([Field 4]# > 0, 1))

Screen Shot 2022-04-22 at 11.58.32 AM.png

Cheers,

Genevieve

Ricky Mayfield

I have about 22 separate summaries within that sheet to track all task by % live for reporting and will need to create these sheets for about 20-30 projects so I want to create template sheets that will be used over and over without any formula editing for my team.

would it be better to create a separate sheet with a sheet reference that captures all the data and then use a AVERAGEIF sheet reference to capture the OVERALL to the main tracking sheet.

My goal is to use the OVERALL in reports and dashboards across the 20-30 projects.

Genevieve P.

Hi @Ricky Mayfield

If you had a separate sheet for your metrics, you would need to set that up again and re-configure the cross-sheet references each time a new sheet is created.

An alternative could be to use 2 helper columns in the actual sheet grid for the 22 summaries, then use the Sheet Summary fields to calculate the Overalls. That way you can use a range from the Grid (ex. Row 1 - 22) in an AVERAGEIF.

Ricky Mayfield

HMMMM, Great feed back let me give that a shot!

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.