How do I average the following column:

1
2
3

4
5
6


The blank row after the three should also be counted as zero. However
I have a very large spreadsheet, and I don't want to go back and enter
zeros in all of the blank cells.

Is there a way?

Thanks

"Blake" wrote:
How do I average the following column:
1
2
3

4
5
6
The blank row after the three should also be counted
as zero. However I have a very large spreadsheet,
and I don't want to go back and enter zeros in all of
the blank cells.

Use the following array-entered formula (press ctrl+shift+Enter instead of
just Enter):

=AVERAGE(IF(A1:A1000="",0,A1:A1000))

If all of the "blank" data are truly empty cells (no constant and no
formula, not even the null string), you might be able to use the following
array-entered formula (again, press ctrl+shift+Enter instead just Enter):

=AVERAGE(--A1:A1000)

Another approach would be to enter zeros in all the blank cells...
Select the data
Use Edit | Go To | Special | Blanks
Enter a 0
Hold down the Ctrl key and press Enter.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
Editorial review of Special Sort Excel add-in (30 ways to sort)





"Blake"
wrote in message
...
How do I average the following column:

1
2
3

4
5
6


The blank row after the three should also be counted as zero. However
I have a very large spreadsheet, and I don't want to go back and enter
zeros in all of the blank cells.

Is there a way?

Thanks

On Fri, 9 Mar 2012 16:22:13 -0800 (PST), Blake wrote:

How do I average the following column:

1
2
3

4
5
6


The blank row after the three should also be counted as zero. However
I have a very large spreadsheet, and I don't want to go back and enter
zeros in all of the blank cells.

Is there a way?

Thanks


=sum(a1:a7)/rows(a1:a7)

On Mar 9, 5:39*pm, Ron Rosenfeld wrote:
On Fri, 9 Mar 2012 16:22:13 -0800 (PST), Blake wrote:
How do I average the following column:

1
2
3

4
5
6

The blank row after the three should also be counted as zero. *However
I have a very large spreadsheet, and I don't want to go back and enter
zeros in all of the blank cells.

Is there a way?

Thanks

=sum(a1:a7)/rows(a1:a7)

This one I like the best. Thanks to all who responded.

On Fri, 9 Mar 2012 18:10:55 -0800 (PST), Blake wrote:

=sum(a1:a7)/rows(a1:a7)

This one I like the best. Thanks to all who responded.

Glad to help. Thanks for the feedback.