Prev: math
Next: Simplification of Formula
From: heater on 12 Apr 2010 17:56
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.
From: new1 on 12 Apr 2010 18:15
On 12 avr, 23:56, heater <hea...(a)discussions.microsoft.com> 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!
B6>0;Sheet2!B6>0,...,Sheet12!B6>0)

Is this right formula ?

HTH

new1@[no/spam]realce.net
From: T. Valko on 12 Apr 2010 23:30
>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" <heater(a)discussions.microsoft.com> wrote in message
news:D733C201-577A-40ED-BB9D-60B9609A8E90(a)microsoft.com...
>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.


 | 
Pages: 1
Prev: math
Next: Simplification of Formula