Prev: mathNext: 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 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" 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: mathNext: Simplification of Formula