Sumproduct...Average

[heater]

I have 12 different tabs. I want to average a number on each tab only if the
number is greater than zero. So, on tab 1 cell b6=140, tab 2 b6=245, tab 3
b6=0 and so on with b6 on each tab having a number greater than 0 sometimes.

 

[new1]

I have 12 different tabs.  I want to average a number on each tab only if the
number is greater than zero.  So, on tab 1 cell b6=140, tab 2 b6=245, tab 3
b6=0 and so on with b6 on each tab having a number greater than 0 sometimes.

Hello,

Maybe you can try this formula :
=average(Sheet1!B6;Sheet2!B6;...;Sheet12!B6)/average(Sheet1!
B6>0;Sheet2!B6>0,...,Sheet12!B6>0)

Is this right formula ?

HTH

new1@[no/spam]realce.net

 

[T. Valko]

I have 12 different tabs.

I want to average a number on each tab
only if the number is greater than zero.

Try something like this...

=SUM(Sheet1:Sheet12!B6)/INDEX(FREQUENCY(Sheet1:Sheet12!B6,0),2)