Sheet summaries OVERALL average

Options

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?


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!