Average using Sumproduct or ....

[heater]

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?

 

[Bob Phillips]

Try

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

 

[Pete_UK]

Have you tried:

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

?

Hope this helps.

Pete

 

[RagDyeR]

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!
=====================================================


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?

 

[T. Valko]

See your other post

http://www.microsoft.com/communitie...&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90

 

[RagDyeR]

Ever given any thought to writing something on Frequency(), similar to what
Bob did with SumProduct()?

 

[T. Valko]

I need a website!

--
Biff
Microsoft Excel MVP

Ever given any thought to writing something on Frequency(), similar to
what
Bob did with SumProduct()?

--

Regards,

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

See your other post

http://www.microsoft.com/communitie...&p=1&tid=d733c201-577a-40ed-bb9d-60b9609a8e90

 

[RagDyeR]

True, but maybe something like:

http://www.oaltd.co.uk/MVP/Default.htm
--

Regards,

RD