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

But their example doesn't help me.

Belongs on webapps.stackexchange.com. – Justin Niessner Jul 06 '11 at 17:26 — Justin Niessner, Jul 06 '11 at 17:26
Odd. 0/3 should be 0, not a divide-by-zero error. – Marc B Jul 06 '11 at 17:27 — Marc B, Jul 06 '11 at 17:27

score 195 · Accepted Answer · edited Aug 21 '15 at 14:45

195

Wrap your formula with IFERROR.

=IFERROR(yourformula)

edited Aug 21 '15 at 14:45

Willi Mentzel

27,862
20
113
121

answered Jul 05 '13 at 08:19

Nik

1,982
1
13
3

8

This seems to be the simplest answer, although I wish google would add functions to allow checking for specific types of errors. Wrapping the function in a generic IFERROR makes it harder to debug if a different error is occurring. – Brionius Aug 12 '15 at 03:44
2

This breaks some very basic principles in logic and programming. If you know what error/exception you're avoiding, then you should explicitly handle it that way. Peregrination's answer is better in that sense. But I'll post yet another answer. – Suamere Nov 11 '16 at 16:41
7

Might help to know you can specify a default value: `=IFERROR(K23/M23, "Invalid data")`. [Source](https://support.google.com/docs/answer/3093304?hl=en) – rideron89 Sep 19 '19 at 17:33

score 48 · Answer 2 · edited Jul 05 '18 at 09:17

48

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.

edited Jul 05 '18 at 09:17

Fedir RYKHTIK

9,844
6
58
68

answered Sep 02 '14 at 02:19

peregrination

2,460
1
25
21

The formula for AVERAGE worked perfectly for me in LibreOffice Calc. – RevNoah Oct 25 '18 at 03:04

score 7 · Answer 3 · answered Nov 08 '18 at 00:01

7

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

answered Nov 08 '18 at 00:01

pnuts

58,317
11
87
139

1

Very glad to have come across this, thank you! Wish it were higher up in the answers. – Pär Winzell Sep 07 '20 at 18:20

score 2 · Answer 4 · answered Nov 11 '16 at 16:45

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.

score 1 · Answer 5 · answered Oct 26 '21 at 16:01

1

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

answered Oct 26 '21 at 16:01

Niloct

9,491
3
44
57

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

5 Answers5