AVERAGE NON-ZERO CELLS ACROSS SEVERAL WORKSHEETS IN SAME WORKBOOK

[SHANE]

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'!K3>0,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!!

 

[David Biddulph]

You need help to get your caps lock key repaired, so that you can stop
SHOUTING.

 

[T. Valko]

Please turn off your caps lock key!

Are there any negative numbers involved?

 

[Joe User]

You need help to get your caps lock key repaired, so that you can stop
SHOUTING.

 

[Joe User]

You need help to get your caps lock key repaired, so
that you can stop SHOUTING.

Maybe he is just trying to get across the deafening silence in the MSDG
system.

(See my thread "FYI: MSDG not displaying posts;...".)

Besides, you're all the way across "the pond". Don't we have to shout in
order for you to hear us?

PS: Sorry about that contentless posting. I finally figured out my
problem -- the reason why I seem to be doing so much of that lasely. In OE,
the Send button is normally in the relative position as the Reply Group
button when I am reading a posting; and I have a tendency to double-click
the Reply Group button because my "click" mechanisms (button and touchpad)
are often non-responsive :-(. Aha! I can fix that: just customized the
toolbar. Klunk!


----- original message -----

 

[Shane]

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.

 

[T. Valko]

{=AVERAGE(IF('SHEET1:SHEET54'!K3>0,SHEET1:SHEET54'!K3))}

Try this...

=SUM(Sheet1:Sheet54!K3)/INDEX(FREQUENCY(Sheet1:Sheet54!K3,0),2)

 

[Shane]

Your formula worked Perfectly! Thank you so much!!

 

[T. Valko]

You're welcome. Thanks for the feedback!