# Simple Average Question

Blake
Guest
Posts: n/a

 10th Mar 2012
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

joeu2004
Guest
Posts: n/a

 10th Mar 2012
"Blake" <(E-Mail Removed)> 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)

Jim Cone
Guest
Posts: n/a

 10th Mar 2012
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
Editorial review of Special Sort Excel add-in (30 ways to sort)

"Blake" <(E-Mail Removed)>
wrote in message
news:f8a69e87-c664-49d5-bbd4-(E-Mail Removed)...
> 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
Guest
Posts: n/a

 10th Mar 2012
On Mar 9, 5:39*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Fri, 9 Mar 2012 16:22:13 -0800 (PST), Blake <sfc...@gmail.com> 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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post raylopez99 Microsoft Windows 2000 Networking 3 11th Feb 2007 02:31 PM dtpetty Microsoft Excel Discussion 1 20th Nov 2005 11:53 AM craigwojo Microsoft Excel New Users 3 29th Aug 2004 05:32 AM Dale Fye Microsoft Access Queries 0 10th Jul 2004 03:20 AM Duane Hookom Microsoft Access Queries 0 9th Jul 2004 03:05 PM

Features