AVG formula #DIVIDE BY ZERO

DG 13

Hello, community!

I have a question. I'm using the "IF" formula to calculate a percentage based on the status in the "status" column.

now, I'm trying to calculate the average of those rows, but I get a "#DIVIDE BY ZERO" error when I use the formula AVG.

How can I resolve this?

MarceHolzhauzen

Hi @DG 13

The #DIVIDE BY ZERO error occurs when your denominator is zero. In this case, if you are trying to calculate the average for the number of items that are not started (for example), it means that no item is set to "Not Started".

If you know your formula is correct (i.e. it does the math correct everywhere else), you can add an "IFERROR" statement to the front of your entire formula. This will remove the errors (in this case #DEVIDE BY ZERO) and replace it with whatever character/word you choose.

Here's more information on the IFERROR function:
https://help.smartsheet.com/function/iferror

DG 13

Hi @Marce, thanks for replying my post.

This is the formula IF I used:

=IF(Status@row = "Not Started", "0%", IF(Status@row = "In Progress", "50%", IF(Status@row = "Completed", "100%", " "))) - To calculate 0%, 50%, and 100%.

I get the same error when using the AVG function, whether I select all three values or just 50% and 100%.

I don’t understand why this is happening or how to fix it.

Thanks in advance for your help!