Sumproduct...Average
 

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.

Sumproduct...Average
 

On 12 avr, 23:56, heater wrote:
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!
B60;Sheet2!B60,...,Sheet12!B60)

Is this right formula ?

HTH

Sumproduct...Average
 

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)

--
Biff
Microsoft Excel MVP


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