Average using Sumproduct or ....
 

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?

Average using Sumproduct or ....
 

Try

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

HTH

Bob

"heater" wrote in message
...
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?

Average using Sumproduct or ....
 

Have you tried:

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

?

Hope this helps.

Pete

On Apr 13, 4:01*pm, heater 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?

Average using Sumproduct or ....
 

See your other post

http://www.microsoft.com/communities...d-60b9609a8e90

--
Biff
Microsoft Excel MVP


"heater" wrote in message
...
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?