AVERAGE with empty cells

[mattia.b89]

Hi folks,
I’d like to calculate average of a (big) column. It contains (many) empty cells and I need to treat them as ZEROs.

Using simple AVERAGE() function lead to “wrong” result since it ignores those cells, so I overcome it “manually” and calculate the average by using SUM()/12.

I attach a sample file which shows the “issue”…
sample2.ods (13.0 KB)

I’d like to know if exist a proper/better way to calculate average in this context.
Thanks!

[JohnSUN]

May be

{=AVERAGE(IF(C2:C6="";0;C2:C6))}

or

=SUMPRODUCT(AVERAGE(IF(C2:C6="";0;C2:C6)))

[Steph1]

You can use the function ROWS() that returns the number of rows in a range:
sample2.ods (13.4 KB)

[karolus]

Hallo

{=AVERAGE(N(C5:C9))}

[mattia.b89]

your solution looks elegant the most! but I have never seen curly brackets in a formula,
indeed when I put it in, I get #VALUE!

How can I use it?

[karolus]

@mattia.b89

never seen curly brackets

take the formula without curly brackets around, and enter with ctrl+shift+enter
OR
alternativly with the [x]array-option in the function-wizard!