How do I solve the problem with Autosum-average ?

[Larry.58]

Here's the problem:
1 a column of NON-contiguous rows (sort/hide the rest).
2 Autosum and subtotal(109 or 9, work as expected.
2 Average doesn't work and neither does subtotal(101 or 1,...

The problem is that an average is the sum divided by the count.
But the count may NOT be augmented by any row/cell that contains a
zero !

For example: I have a column of 20 numbers whose total = 1,000
If all cells contained a positive value, the average would equal 50.

If 10 of the cells contain a ZERO ("0") then the average is now 100:
1,000 / 10 = 100.

Unfortunately, excel's functions (average or subtotal(109,...) still produce a result of 50 !

How do I get excel to produce the correct average of 100 for 20 cells whose sum of their contents = 1,000 but 10 of the cells have a zero value?

This is a productivity issue wherein cells that contain a zero means the employee was absent for any number of legit reasons. Thus the "average" I'm seeking is the average quantity of work performed on the days the employee was actually working.

BTW: the cells are in NON-contiguous rows.

 

[BrandyExcel]

You can use a CountIf

CountIf(rangename,">0")

will count any numbers above zero, then use that count to do the average.

 

[Larry.58]

Thanks, but I'm not writing a script. this is an occasional / ad-hoc.
so an extra 2 minutes to count'em up by keypress after a
custom sort was good enough.

It just seems that, as usual MS.crapware fails again.
Even the simple and obvious seems to elude those idiots.
I have learned to expect this from Gates & Co, after all,
He is only a very successful thief, who, despite his marketing lies, has
never, ever innovated anything at all.
When I need it, which is seldom, I use Lotus.
I've seen this before with another MS.crapware . . . access.
For data entry screens I still use the old MS/PC DOS Foxpro.
Nothing MS has can beat it for speed and ease of error/limit/range checking.
Nothing at all.

Gotta see if Ubuntu/wine can run it too ! ))
Just moved up to v9.04 and most of what I do now is graphics and wp (gimp,inkscape,photoshop,wordperfect,scribus).

Thanks for the help, and now to end this request...
Thank you all...