AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK
 

I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE CELL I NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK" THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE BASED ONLY ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK
 

You need help to get your caps lock key repaired, so that you can stop
SHOUTING.
--
David Biddulph

SHANE wrote:
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL
DISPLAY DATA GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING
ANY ISSUE WITH "SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD
AVERAGE. THE CELL I NEED TO AVERAGE CONTAINS A FORMULA AND THEREFORE
NON OF THEM ARE "BLANK" THEY CONATIN "ZEROES". I NEED THE AVERAGE ON
THE SUMMARY SHEET TO BE BASED ONLY ON THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF
CELLS GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK
 

Please turn off your caps lock key!

Are there any negative numbers involved?

--
Biff
Microsoft Excel MVP


"SHANE" wrote in message
...
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE CELL I
NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK" THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE BASED ONLY
ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK
 

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
You need help to get your caps lock key repaired, so that you can stop
SHOUTING.
--
David Biddulph

SHANE wrote:
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL
DISPLAY DATA GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING
ANY ISSUE WITH "SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD
AVERAGE. THE CELL I NEED TO AVERAGE CONTAINS A FORMULA AND THEREFORE
NON OF THEM ARE "BLANK" THEY CONATIN "ZEROES". I NEED THE AVERAGE ON
THE SUMMARY SHEET TO BE BASED ONLY ON THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF
CELLS GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK
 

"David Biddulph" <groups [at] biddulph.org.uk wrote:
You need help to get your caps lock key repaired, so
that you can stop SHOUTING.

Maybe he is just trying to get across the deafening silence in the MSDG
system. :-)

(See my thread "FYI: MSDG not displaying posts;...".)

Besides, you're all the way across "the pond". Don't we have to shout in
order for you to hear us? :-) :-)

PS: Sorry about that contentless posting. I finally figured out my
problem -- the reason why I seem to be doing so much of that lasely. In OE,
the Send button is normally in the relative position as the Reply Group
button when I am reading a posting; and I have a tendency to double-click
the Reply Group button because my "click" mechanisms (button and touchpad)
are often non-responsive :-(. Aha! I can fix that: just customized the
toolbar. Klunk!


----- original message -----

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
You need help to get your caps lock key repaired, so that you can stop
SHOUTING.
--
David Biddulph

SHANE wrote:
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL
DISPLAY DATA GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING
ANY ISSUE WITH "SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD
AVERAGE. THE CELL I NEED TO AVERAGE CONTAINS A FORMULA AND THEREFORE
NON OF THEM ARE "BLANK" THEY CONATIN "ZEROES". I NEED THE AVERAGE ON
THE SUMMARY SHEET TO BE BASED ONLY ON THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF
CELLS GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKB
 

Sorry about the caps lock key, I didn't mean to "Shout".

No, their are no negative numbers in any of the cells. just zero's, or
higher.
--
Thanks,

Shane


"T. Valko" wrote:

Please turn off your caps lock key!

Are there any negative numbers involved?

--
Biff
Microsoft Excel MVP


"SHANE" wrote in message
...
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE CELL I
NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK" THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE BASED ONLY
ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!


.

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKB
 

{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54 '!K3))}

Try this...

=SUM(Sheet1:Sheet54!K3)/INDEX(FREQUENCY(Sheet1:Sheet54!K3,0),2)

--
Biff
Microsoft Excel MVP


"Shane" wrote in message
...
Sorry about the caps lock key, I didn't mean to "Shout".

No, their are no negative numbers in any of the cells. just zero's, or
higher.
--
Thanks,

Shane


"T. Valko" wrote:

Please turn off your caps lock key!

Are there any negative numbers involved?

--
Biff
Microsoft Excel MVP


"SHANE" wrote in message
...
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE CELL
I
NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK"
THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE BASED
ONLY
ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!


.

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKB
 

Your formula worked Perfectly! Thank you so much!!
--
Thanks,

Shane


"T. Valko" wrote:

{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54 '!K3))}

Try this...

=SUM(Sheet1:Sheet54!K3)/INDEX(FREQUENCY(Sheet1:Sheet54!K3,0),2)

--
Biff
Microsoft Excel MVP


"Shane" wrote in message
...
Sorry about the caps lock key, I didn't mean to "Shout".

No, their are no negative numbers in any of the cells. just zero's, or
higher.
--
Thanks,

Shane


"T. Valko" wrote:

Please turn off your caps lock key!

Are there any negative numbers involved?

--
Biff
Microsoft Excel MVP


"SHANE" wrote in message
...
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE CELL
I
NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK"
THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE BASED
ONLY
ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!


.



.

AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKB
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Shane" wrote in message
...
Your formula worked Perfectly! Thank you so much!!
--
Thanks,

Shane


"T. Valko" wrote:

{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54 '!K3))}

Try this...

=SUM(Sheet1:Sheet54!K3)/INDEX(FREQUENCY(Sheet1:Sheet54!K3,0),2)

--
Biff
Microsoft Excel MVP


"Shane" wrote in message
...
Sorry about the caps lock key, I didn't mean to "Shout".

No, their are no negative numbers in any of the cells. just zero's, or
higher.
--
Thanks,

Shane


"T. Valko" wrote:

Please turn off your caps lock key!

Are there any negative numbers involved?

--
Biff
Microsoft Excel MVP


"SHANE" wrote in message
...
I HAVE A WOOKBOOK WITH 54 WORKSHEETS, THEY ARE ALL IDENTICAL, BUT
REPRESENT
DIFFERENT PEOPLE. I AM CREATING A SUMMARY SHEET THAT WILL DISPLAY
DATA
GATHERED FROM ALL OF THE OTHER SHEETS. I AM NOT HAVING ANY ISSUE
WITH
"SUMMING" THE DATA, BUT I CANNOT SEEM TO GET A GOOD AVERAGE. THE
CELL
I
NEED
TO AVERAGE CONTAINS A FORMULA AND THEREFORE NON OF THEM ARE "BLANK"
THEY
CONATIN "ZEROES". I NEED THE AVERAGE ON THE SUMMARY SHEET TO BE
BASED
ONLY
ON
THE NON-ZERO CELLS.

I HAVE THIS, ENTERED AS AN ARRAY, BUT GET A "REF" ERROR.
{=AVERAGE(IF('SHEET1:SHEET54'!K30,SHEET1:SHEET54' !K3))}

I HAVE ALSO TRIED TAKING THE SUM AND DIVIDING IT BY THE NUMBER OF
CELLS
GREATER THAN ZERO CELLS USING THE "COUNTIF" FUNCTION AS FOLLOWS:

=SUM('SHEET 1:SHEET 20'!K3)/COUNTIF('SHEET 1:SHEET 20'!K3,"0")

THIS GIVES ME A "VALUE" ERROR.

I NEED HELP!!


.



.