average range of cells with zero and blanks

's Computer Specifications

Hello
I need to average a range of cells where there are blanks and zero’s and
both have meaning. I’ve used a generic formula =average(b21:b27) which works
with the exception of when there is a range of seven days of no data (looks
blank). The desired result would have the result be blank but I receive
#DIV/O for an error. Can you please help?

's Computer Specifications

=IF(COUNT(B21:B27)=0,"",AVERAGE(B21:B27))

--
__________________________________
HTH

Bob

"Lisa" <(E-Mail Removed)> wrote in message
news:F6AEEE8D-A58D-4BFA-A15C-(E-Mail Removed)...
> Hello
> I need to average a range of cells where there are blanks and zero's and
> both have meaning. I've used a generic formula =average(b21:b27) which
> works
> with the exception of when there is a range of seven days of no data
> (looks
> blank). The desired result would have the result be blank but I receive
> #DIV/O for an error. Can you please help?
>

's Computer Specifications

=IF(COUNT(B21:B27)=0,"",AVERAGE(B21:B27))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk

"Lisa" <(E-Mail Removed)> wrote in message
news:F6AEEE8D-A58D-4BFA-A15C-(E-Mail Removed)...
> Hello
> I need to average a range of cells where there are blanks and zero's and
> both have meaning. I've used a generic formula =average(b21:b27) which
> works
> with the exception of when there is a range of seven days of no data
> (looks
> blank). The desired result would have the result be blank but I receive
> #DIV/O for an error. Can you please help?
>
>

's Computer Specifications

Bob/Sandy
Thanks for the quick and accurate responses. much appreciated.
Lisa

"Lisa" wrote:

> Hello
> I need to average a range of cells where there are blanks and zero’s and
> both have meaning. I’ve used a generic formula =average(b21:b27) which works
> with the exception of when there is a range of seven days of no data (looks
> blank). The desired result would have the result be blank but I receive
> #DIV/O for an error. Can you please help?
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Take an average of a range of cells but exlude blanks from that av	=?Utf-8?B?QiBH?=	Microsoft Excel Misc	4	12th Oct 2006 02:46 PM
AVERAGE(with complicated range -blanks..)	=?Utf-8?B?ZXhjZWxlbnQ=?=	Microsoft Excel Worksheet Functions	7	29th Sep 2006 05:14 PM
copy range of cells with blanks then paste without blanks	=?Utf-8?B?anVzdGFndXlmcm9ta3k=?=	Microsoft Excel Programming	5	3rd Sep 2006 11:23 PM
copy range of cells with blanks then paste without blanks	=?Utf-8?B?anVzdGFndXlmcm9ta3k=?=	Microsoft Excel Worksheet Functions	1	3rd Sep 2006 07:56 PM
How do i get an average that ignores blanks in the range of cells.	=?Utf-8?B?dWNhc3RvcmVz?=	Microsoft Excel Worksheet Functions	7	11th Nov 2004 05:01 PM