Sheet summaries OVERALL average

Ricky Mayfield

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?

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))

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!