Force Excel formula to interpret blank cell as zero

[psaulm119]

I have a formula to calculate the average of a row of cells. Where I want that average to appear, I have had Excel 2010 insert the average formula, and in the cell itself it reads =AVERAGE(L7:Q7)

Now it is giving me a good average, except that if I have not entered a score, Excel doesn't count that as a zero. By way of example, in cells 1A, 1B, and 1C, there might be 50, 100, and nothing in the blank cell. Excel calculates the average as 75. How can I get Excel to count the blank cell 1C as zero, and give me the average as 50?

How can I get Excel to interpret blank cells as a zero?

 

[Jonmo1]

Usually, people want the opposite..For the average to ignore blanks.
I think that's why that is the default behavior of Average...

Try

=SUM(L7:Q7)/6

Or to be more dynamic

=SUM(L7:Q7)/COLUMNS(L7:Q7)


Or enter the average as an array with +0..

=AVERAGE(L7:Q7+0)
Entered as an array using CTRL + SHIFT + ENTER


Hope that helps.

 

[njimack]

Average ignores cells that are blank. Try something like this:
=SUM(L7:Q7)/COLUMNS(L7:Q7)

However, this won't return 50, since it's evaluating all 6 cells in the range

 

[psaulm119]

Average ignores cells that are blank. Try something like this:
=SUM(L7:Q7)/COLUMNS(L7:Q7)

However, this won't return 50, since it's evaluating all 6 cells in the range

Thanks to both of you for this solution. Yes, that works.

As far as returning 50, that was just an example I provided so that people would understand what I want to do.

Your forumula did it. Thanks again.