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.
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 for purposes they were not intended for is bad practice, but we all take that shortcut at times. Just be aware of implications.