How do I solve the problem with Autosum-average ?


New Member
Jun 8, 2009
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.

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.


New Member
Jun 8, 2009
You can use a CountIf


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


New Member
Jun 8, 2009
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...

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...