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!!
.
.
|