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

user_1759987 0
Viewed 136395

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.

divide-by-zero google-sheets
5 Answers

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_15051200

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

user_1520160
Related