HELP ME PLEASE!!!!! average while ignoring zeros!

[tink]

Please let me know how to do an average of a group of numbers while ignoring any zeros! the help thing in excel did not work at all. HOW DO YOU DO THIS PLEASE HELP ME!!! I NEED TO KNOW!

 

[Tom Urtis]

See if these choices are any help.

Thanks to Aladin and Mark W for answering this question many times on this board.

Assuming a range of A1:A10,

Non array formula
=SUM(A1:A10)/MAX(1,SUMPRODUCT((A1:A10<>0)*(ISNUMBER(A1:A10))))

Array (Ctrl+Shift+Enter)
=AVERAGE(IF(A1:A10,A1:A10))

Tom Urtis


Sorry, forgot a shorter non-array from Aladin as well:
=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

T.U.
This message was edited by Tom Urtis on 2002-03-01 20:53

 

[Anne Troy]

I bow to MrExcel MVP, but would like to ask:

Why do you need the *max* part?

This worked for me:

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

 

[Tom Urtis]

As noted, these were previous suggestions from others (much more formula-savvy than me), and the first ones I remembered, though both individuals have also posted shorter formula solutions for this question that were not reflected in my reply. Thanks for the tip.

Tom U.

 

[Anne Troy]

Doh! Thanks Tom!

 

[tink]

Ok thanks for that, im impressed at the speed of the replies. However now i have another problem. I still want to ignore zeros but now the numbers are still in the same colum, but not all in order like A1:A10 more like A1, A3, A14 etc... How do you do that, i tried seperating them by commas but that didnt seem to work. Thank you.

 

[Aladin Akyurek]

On 2002-03-01 21:05, Dreamboat wrote:
I bow to MrExcel MVP, but would like to ask:

Why do you need the *max* part?

This worked for me:

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

Consider

{0.00,0.00,"",0.00}

in A1:A4.

Applied to the above set of values

[1]

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

will result in #DIV/0!, while

[2]

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

will compute 0.

The formula in [2] assumes the benign condition that the range of interest does not house negative numbers. To average positive non-zero values, it needs to be rewritten as:

[3]

=SUMIF(A1:A4,">0")/MAX(1,COUNTIF(A1:A4,">0"))

In order to average both neg and pos values excluding zeroes, [2] must be expanded to:

[4]

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0")+COUNTIF(A1:A4,"<0"))

which is exactly eq to the array-formula

[5]

{=AVERAGE(IF(A1:A4,A1:A4))}

By the way, the formula in [5] cannot cope (without modification) with the formula returned blanks in the range of interest, while [4] would.

Aladin