Ask Your Question

How do I hide: #DIV/0!

asked 2015-04-29 23:34:26 +0200

TarryFaster gravatar image

updated 2015-04-29 23:35:37 +0200

These formula results are in a column and I can't add other numbers in that column with #DIV/0! showing in the otherwise vacant cells.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-04-30 00:03:26 +0200

Lupp gravatar image

updated 2015-04-30 00:10:49 +0200

Suppose you have =A7/B7 in cell C7 and row 7 shall be regarded "unused" if B7 is blank (disregarding A7?). You can avoid the "DivisionByZero error" using the formula =IF(B7="";"";A7/B7). The empty string won't interfere with calculating SUMs or AVERAGEs...

If B7 is actually zero there is no sense in hiding the error which is an inevitable fact then. Only Chuck Norris can divide by zero.

See attachment.ask50076AvoidDivByZero001.ods

edit flag offensive delete link more


Pitfall: While aggregating functions such as SUM() and AVERAGE() will disregard the non numeric content, explicit references in a calculation may throw an error.

When e.g. cell F9 evaluates to empty string (a text), =SUM(F1:F10) will be regarded by Calc as "nothing to sum in F9" and the formula is OK with it.

With formula =F1+...+F9+F10 Calc will see that F9 is not a valid number. Formula requires a number. ERROR!

A workaround (if you to calculate on the result elsewhere, and aggregating functions are not desirable) is to insert a text that has an unambiguous numerical interpretation. A simple "0" may be enough (by default it is left aligned, so it is easily identified as different from the number zero). A string of zeroes - "000" - may be desirable for clarity.

In the long run, explicit handling of special conditions is usually better. Using values ...(more)

keme gravatar imagekeme ( 2019-10-11 13:04:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-29 23:34:26 +0200

Seen: 5,744 times

Last updated: Apr 30 '15