Divide by Zero error

LeAndre P
LeAndre P ✭✭
edited 06/27/22 in Formulas and Functions

Hello,

I am using the formula below to return the average value of a column. I chose not to use the standard AVG formula because it was considering blank cells and cells with zeros skewing the true average total. However, the formula below is returning DVIVD BY ZERO. I tried adding IFERROR and got incorrect argument. Where did I go wrong here?

=(SUM({Year_1})) / ((COUNT({Year_1})) - (COUNTIF({Year_1}, 0)))


Thank you,

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @LeAndre P

    The error indicates that the second half of your formula returns 0. To troubleshoot this, I would try out each COUNT formula separately to see what it returns:

    =COUNT({Year_1})

    =COUNTIF({Year_1}, 0)

    If you're getting the same number, then subtracting one from the other would result in 0, which you cannot divide by. If you shouldn't be getting the same number, can you post a screen capture of your {Year_1} column (but please block out sensitive data).

    Thanks!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @LeAndre P

    I'm glad you were able to solve it! Just a note... although this should work fine, you don't need quite so many parentheses. It may be easier to troubleshoot down the line if you remove out the extras:

    =IF(COUNT({Year_1}) = COUNTIF({Year_1}, 0), 0, SUM({Year_1}) / (COUNT({Year_1}) - COUNTIF({Year_1}, 0)))

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @LeAndre P

    The error indicates that the second half of your formula returns 0. To troubleshoot this, I would try out each COUNT formula separately to see what it returns:

    =COUNT({Year_1})

    =COUNTIF({Year_1}, 0)

    If you're getting the same number, then subtracting one from the other would result in 0, which you cannot divide by. If you shouldn't be getting the same number, can you post a screen capture of your {Year_1} column (but please block out sensitive data).

    Thanks!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi Genevieve,


    Thank for assisting. I think I managed to solve this one by using the following formula.

    =IF((COUNT({Year_1})) = (COUNTIF({Year_1}, 0)), 0, SUM({Year_1}) / ((COUNT({Year_1})) - (COUNTIF({Year_1}, 0))))


    Regards,

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @LeAndre P

    I'm glad you were able to solve it! Just a note... although this should work fine, you don't need quite so many parentheses. It may be easier to troubleshoot down the line if you remove out the extras:

    =IF(COUNT({Year_1}) = COUNTIF({Year_1}, 0), 0, SUM({Year_1}) / (COUNT({Year_1}) - COUNTIF({Year_1}, 0)))

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!