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

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,"<>0"),"")

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

User · Answer

Wrap your formula with IFERROR    IFERROR yourformula

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  gt  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

[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