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.
This question is related to
google-sheets
divide-by-zero
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.
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"),"")
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.
Source: Stackoverflow.com