Need blank cell to = 0 for AVG function

I need to average an array of cells and I need the blank ones to have a value of ZERO in order for my average to be accurate. Right now, blank cells aren't contributing to the average. I don't want to have to populate all the empty cells with a 0 entry.

E.g. If one cell value is 8 and I need to calculate the avg across 2 cell values, I want the answer to be 4, but it's returning 8 as the average because the 2nd cell is blank.

In reality, I'm after the average of a larger array of cells, some may be filled and some may be empty, but I need the empty ones to count.

You can use this array* formula:

=AVERAGE(IF(P3:P9="",0,P3:P9))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

The formula will substitute any blanks in the range P3:P9 with zeros.

Hope this helps.

Pete

That definitely gets me closer and I learned something new, so thank you! But I'm not there yet. Your formula only accounts for number of rows, not number of cells. Let's say I have a value of 8 in a clump of 4 cells (two cells above and two cells below). Your formula gets me an average of 4, but the average should really be 2 across those 4 cells.

Yay! Pete_UK, that works! Thank you!

(Referring to =AVERAGE(IF(P3:P9="",0,P3:P9))

Hi TMS, you are right, assuming my array is within a single column. My array is over multiple columns and multiple rows, however.

Thank you, TMS! I like that solution. It's very straightforward.
And you are right. I should have been more clear in my explanation. I apologize for that. I will get better!