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