1 cell average across multiple worksheets
 

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

=SUM(sheet1:sheet13!B2)/COUNTIF(sheet1!B2,"0",sheet2,"0",sheet3,"0",she et4,"0",sheet5,"0",sheet6,"0",sheet7,"0",sheet 8,"0",sheet9,"0",sheet10,"0",sheet11,"0",sheet 12,"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 when doing this. Ive searched and searched the last 3 days and im about to give up. Please help

1 cell average across multiple worksheets
 

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


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=540150

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

1 cell average across multiple worksheets
 

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!

In article ,
curtll wrote:

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

=SUM(sheet1:sheet13!B2)/COUNTIF(sheet1!B2,"0",sheet2,"0",sheet3,"0",she et4,
"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 when
doing this. Ive searched and searched the last 3 days and im about to
give up. Please help

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

1 cell average across multiple worksheets
 

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

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

Domenic Wrote:
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!

In article ,
curtll wrote:

Hello,
I have 14 worksheets in my current workbook, 13 of which are
different employees. Each day I am entering data that is then
automatically averaged into a mtd column. Sometimes there will be
0's
in those mtd columns and I dont want those counted when i take the
number from the single cell on all of the worksheets to produce a
correct average on sheet 14 Please help i've been working on this for
3
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",sh eet9,"0",sheet10,"0",
sheet11,"0",sheet12,"0",sheet13,"0")

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

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


--
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????

1 cell average across multiple worksheets
 

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";"She et3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"; "Sheet9";"Sheet10";"Sheet11";"Sheet12";"Sheet13"}& "'!B2"))0,N(INDIRECT("'"&{"Sheet1";"Sheet2";"Shee t3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8";" Sheet9";"Sheet10";"Sheet11";"Sheet12";"Sheet13"}&" '!B2"))))

array entered


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"curtll" wrote in message
...

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

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

Domenic Wrote:
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!

In article ,
curtll wrote:

Hello,
I have 14 worksheets in my current workbook, 13 of which are
different employees. Each day I am entering data that is then
automatically averaged into a mtd column. Sometimes there will be
0's
in those mtd columns and I dont want those counted when i take the
number from the single cell on all of the worksheets to produce a
correct average on sheet 14 Please help i've been working on this for
3
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",sh eet9,"0",sheet10,"0",
sheet11,"0",sheet12,"0",sheet13,"0")

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

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


--
curtll


--
curtll

1 cell average across multiple worksheets
 

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!

In article ,
curtll wrote:

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?