CODEWITHSUNDEEP
google-sheetsdivide-by-zero
Aaron
Aaron

Reputation: 2305

How do I avoid the "#DIV/0!" error in Google docs spreadsheet?

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&answer=159999

But their example doesn't help me.

Upvotes: 112

Views: 165783

Answers (5)

Niloct
Niloct

Reputation: 10015

Check if the column has text format. Apply number formatting to the cells you're using in the average function.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

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

Upvotes: 8

peregrination
peregrination

Reputation: 2638

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.

Upvotes: 51

Suamere
Suamere

Reputation: 6258

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.

Upvotes: 2

Nik
Nik

Reputation: 2102

Wrap your formula with IFERROR.

=IFERROR(yourformula)

Upvotes: 207

Related Questions