1 cell average across multiple worksheets

[curtll]

Hello,
I have 14 worksheets in my current workbook, 13 of which ar
different employees. Each day I am entering data that is the
automatically averaged into a mtd column. Sometimes there will be 0'
in those mtd columns and I dont want those counted when i take th
number from the single cell on all of the worksheets to produce
correct average on sheet 14 Please help i've been working on this for
days. Here is a list of different formulas Ive tried.

=SUM(sheet1:sheet13!B2)/COUNTIF(sheet1!B2,">0",sheet2,">0",sheet3,">0",sheet4,">0",sheet5,">0",sheet6,">0",sheet7,">0",sheet8,">0",sheet9,">0",sheet10,">0",sheet11,">0",sheet12,">0",sheet13,">0")

=AVERAGE(IF(sheet1:sheet13!B2<0,sheet1:sheet13!B2,"")

And other variations of those....either I get a REF or VALUE error whe
doing this. Ive searched and searched the last 3 days and im about t
give up. Please hel

 

[EdMac]

Have you tried

=AVERAGE(sheet1:sheet13!B2)

Average will ignore the cells that have no value in them

In your first formula you were omitting the cell reference for each
sheet.

HTH

Ed

 

[curtll]

Yes i've tried that, but that forumula includes zero values in cells
which I dont want.

 

[Domenic]

Let A2:A14 contain the sheet names, then try the following formula,
which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(INDIRECT("'"&$A$2:$A$14&"'!B2"))>0,N(INDIRECT("'"&$A$2:$A$1
4&"'!B2"))))

Hope this helps!

 

[curtll]

So in your formula where you have ("'"&$A$2:$A$14&"'!B2"), I should
enter it like this....("'"&Sheet1:Sheet13&"'B2")....???

 

[Peo Sjoblom]

No, A2:A14 should contain all the sheet names like

Sheet1
Sheet2
Sheet3
Sheet4
...........
Sheet13


so each cell in A2:A14 holds a sheet name

then you just use Domenic's formula

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[curtll]

Not really wanting to do it that way. I already have the spreadsheet
the way I like it. I have the dates in the A column. Why cant I just
average 1 cell across multiple worksheets and not include zeros in the
calculation? I mean obviously its difficult because I have been
working on this for almost a week now, but I know theres gotta be a
guru on here that can figure it out right????

 

[Peo Sjoblom]

You don't have to use that particular range, you can put your sheet names
somewhere else off view like in IV2:IV14 or hard code them. As far as a guru
I doubt you will find anyone more knowledgeable than Domenic when it comes
3D formulas. There is no way of using a condition over multiple sheets
without either hard code all sheets into the formula or using a range with
all sheet names

=AVERAGE(IF(N(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"Sheet10";"Sheet11";"Sheet12";"Sheet13"}&"'!B2"))>0,N(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"Sheet10";"Sheet11";"Sheet12";"Sheet13"}&"'!B2"))))

array entered


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Domenic]

In addition to Peo's suggestions, I believe if you download and install
the free add-in Morefunc.xll, you should be able to use the THREED
function...

=AVERAGE(IF(THREED('Sheet1:Sheet13'!B2)>0,THREED('Sheet1:Sheet13'!B2)))

....confirmed with CONTROL+SHIFT+ENTER. Note that I haven't been able to
test it since this add-in is not compatible with my Mac version of
Excel. If you'd like to try it, the add-in can be found at the
following link...

http://xcell05.free.fr/

Hope this helps!