Average using Sumproduct or ....

H

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?
 
B

Bob Phillips

Try

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

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?
 
R

RagDyeR

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

T. Valko

I need a website!

--
Biff
Microsoft Excel MVP


RagDyeR said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top