#DIVIDE BY ZERO Error message

Ricky Mayfield

Smartsheets.pdf

The below formula is being used to count items (Installed) & items (Complete) against a cell value (Protection plate) in a sheet summary to produce a %percentage (Complete). The is being used in multiple sheet summaries to compile an overall %percentage (Complete).

The formula works great, however if the column [Protection Plate]1:[Protection Plate] does not have a cell value I am getting a #DIVIDE BY ZERO Error message.

This in turn gives me an Error message on my overall average of %percentage (Complete).

=COUNTIFS([I (4)]1:[I (4)]9, 1, [C (4)]1:[C (4)]9, 1) / COUNT([Protection Plate]1:[Protection Plate]9)

=COUNTIFS([I (2)]:[I (2)], 1, [C (2)]:[C (2)], 1) / COUNT([Lock/Exit Device]:[Lock/Exit Device])

Paul H

Us an IFERROR function to assign a value rather than an error message

IFERROR( Your Formula, value if error )

https://help.smartsheet.com/function/iferror

Paul H

Us an IFERROR function to assign a value rather than an error message

IFERROR( Your Formula, value if error )

https://help.smartsheet.com/function/iferror

Ricky Mayfield

I was able to resolve the #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 in all summaries not labeled OVERALL that will only count averages for summaries greater than 0?