How to avoid #ERROR! when potential dividing by 0?

[Kiza_McDonald]

I’m running into an issue where there is the potential for a formula to divide by 0.

How can I display the text “Posting not allowed” instead of #ERROR!?

Here’s my formula: ROUND({# Posts Utilized}/ VALUE({Monthly allowance})*100)&"%"

[Jeremy_Oglesby]

You can wrap the entire thing in an ISERROR() catch, which will require some duplication. It looks like this:

IF(
   ISERROR(<< your formula >>),
   “Posting not allowed”,
   << your formula >
)

It says that if your formula would resolve to an error, then fill the cell with your error phrase, otherwise, resolve your formula and fill the cell with its result.

You could simplify the first << your formula >> to only include the division that might result in dividing by 0.

[W_Vann_Hall]

Alternatively, you could check the value of VALUE({Monthly allowance}):

IF(
    VALUE({Monthly allowance}),
    ROUND({# Posts Utilized}/ VALUE({Monthly allowance})*100)&"%",
    'Posting not allowed'
    )

For some reason, I got it in my head early on there were some instances when ISERROR() failed to trap on #ERROR!. I didn’t document it at the time, and I’ve never been able to duplicate it, so it was probably operator error.

It appears ISERROR() traps on #ERROR!, NaN, and Infinity, now.