!ERROR! division by zero

  • Resolved Jordon Rupp

    (@jordonrupp)


    6 years, 2 months ago

    Is there a workaround for eliminating the !ERROR! division by zero issue which occurs from formulas referencing cells with zero values? Perhaps an IF conditional formula? Also, either the “then” or “else” value would also need to be a formula.

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Tobias B?thge

    (@tobiasbg)

    Hi,

    thanks for your question, and sorry for the trouble.

    You basically have the solution already ?? Indeed, IF() is the way to go:

    =IF( A1<>0, B1/A1, 0 )

    Regards,
    Tobias

    Thread Starter Jordon Rupp

    (@jordonrupp)

    Thanks, Tobias! I’m still receiving !ERROR! division by zero for the formula =IF( F2>0, D2/F2, 0 ) when cell F2 has a value of zero.

    Plugin Author Tobias B?thge

    (@tobiasbg)

    Hi,

    ah, yes. The problem here is that this division is still evaluated (because of the recursion).
    You could maybe try this trick:

    =IF( F2>0, D2/( IF( F2>0, F2, 1 ) ), 0 )

    Basically, in the case that F2 is zero, the division will now divide by 1 instead of F2 – but never use that division result, because of outer IF.

    Regards,
    Tobias

    Thread Starter Jordon Rupp

    (@jordonrupp)

    Brilliant, that did the trick. Thank you!

    Plugin Author Tobias B?thge

    (@tobiasbg)

    Hi,

    no problem, you are very welcome! ?? Good to hear that this helped!

    Best wishes,
    Tobias

    P.S.: In case you haven’t, please rate TablePress here in the plugin directory. Thanks!

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘!ERROR! division by zero’ is closed to new replies.