Thanks:  0
Likes:  0

1. 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!

2. 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 ]

3. 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")

4. 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.

5. Doh! Thanks Tom!

6. 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.

7. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•