Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excluding cells from average on Excel workbook | New Users to Excel | |||
Average Cells in Multiple Worksheets | Excel Worksheet Functions | |||
Update Links between cells of two worksheets in the same workbook | Excel Discussion (Misc queries) | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) | |||
Can't link cells in worksheets in same workbook | New Users to Excel |