Prev: Conditional Format a range based on 1 cell
Next: IF
From: heater on 13 Apr 2010 11:01
I have 12 tabs (same worksheet) that I need to average a number in cell b6.
The issue is sometimes there is a zero in b6 and I do not want to count it in
the average. example: tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
5=0, and so on... What is a good formula?
From: Bob Phillips on 13 Apr 2010 11:26
Try

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0"))
/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0
--

HTH

Bob

"heater" <heater(a)discussions.microsoft.com> wrote in message
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471(a)microsoft.com...
>I have 12 tabs (same worksheet) that I need to average a number in cell b6.
> The issue is sometimes there is a zero in b6 and I do not want to count it
> in
> the average. example: tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on... What is a good formula?


From: Pete_UK on 13 Apr 2010 11:29
Have you tried:

=AVERAGE('tab 1:tab 12'!B6)

?

Hope this helps.

Pete

On Apr 13, 4:01 pm, heater <hea...(a)discussions.microsoft.com> wrote:
> I have 12 tabs (same worksheet) that I need to average a number in cell b6.  
> The issue is sometimes there is a zero in b6 and I do not want to count it in
> the average.  example:  tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on...  What is a good formula?

From: RagDyeR on 13 Apr 2010 11:49
Try this:

=SUM(Sheet1:Sheet12!B6)/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),">0"))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"heater" <heater(a)discussions.microsoft.com> wrote in message
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471(a)microsoft.com...
I have 12 tabs (same worksheet) that I need to average a number in cell b6.
The issue is sometimes there is a zero in b6 and I do not want to count it
in
the average. example: tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
5=0, and so on... What is a good formula?


From: T. Valko on 13 Apr 2010 19:21
See your other post

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90

--
Biff
Microsoft Excel MVP


"heater" <heater(a)discussions.microsoft.com> wrote in message
news:E6A200BC-61BF-4FFD-8A66-24BB9D8B5471(a)microsoft.com...
>I have 12 tabs (same worksheet) that I need to average a number in cell b6.
> The issue is sometimes there is a zero in b6 and I do not want to count it
> in
> the average. example: tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab
> 5=0, and so on... What is a good formula?


 |  Next  |  Last
Pages: 1 2
Prev: Conditional Format a range based on 1 cell
Next: IF