How do I avoid the DIV 0 error in Google docs spreadsheet

Question

I have a column with average K23 M23  that starts out with  DIV 0  when the K23 through M23 cells are empty  Preferably I d like to only do the average of cells that contain non-zero  non-blank values  I think it s possible using the query command   https   docs google com support bin answer py hl en amp answer 159999  But their example doesn t help me

User · Answer

Since you are explicitly also asking to handle columns that haven't yet been filled out, and I assume also don't want to mess with them if they have a word instead of a number, you might consider this:

=If(IsNumber(K23), If(K23 > 0, ........., 0), 0)

This just says... If K23 is a number; And if that number is greater than zero; Then do something ......... Otherwise, return zero.

In ........., you might put your division equation there, such as A1/K23, and you can rest assured that K23 is a number which is greater than zero.

User · Answer

Wrapping the existing formula in IFERROR will not achieve      the average of cells that contain non-zero  non-blank values    I suggest trying    if ArrayFormula isnumber K23 M23   AVERAGEIF K23 M23   lt  gt 0

User · Answer

Wrap your formula with IFERROR    IFERROR yourformula

User · Answer

You can use an IF statement to check the referenced cell s  and return one result for zero or blank  and otherwise return your formula result    A simple example     IF B1 0    A1 B1    This would return an empty string if the divisor B1 is blank or zero  otherwise it returns the result of dividing A1 by B1   In your case of running an average  you could check to see whether or not your data set has a value    IF SUM K23 M23  0    AVERAGE K23 M23     If there is nothing entered  or only zeros  it returns an empty string  if one or more values are present  you get the average

[google-sheets] How do I avoid the "#DIV/0!" error in Google docs spreadsheet?

Examples related to google-sheets

Examples related to divide-by-zero