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


.