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