# How do I hide: #DIV/0!

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.

First time here? Check out the FAQ!

How do I hide: #DIV/0!

add a comment

0

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

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)

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

Seen: **5,744 times**

Last updated: **Apr 30 '15**

How do I export a chart in an image format from LibreOffice Calc? [closed]

Are there plans for a "papercut" project for libreoffice [closed]

Is it normal for Calc goal seek to take very long? [closed]

Please refine "Search" in Calc - implement functions in Gnumeric [closed]

LibreOffice Calc will not link to external data via internet [closed]

Is there a LibreOffice .odt, .ods viewer for Android? [closed]

Why is Calc so much slower at opening/saving files than MS-Office? (win7 x64) [closed]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.