# Non-zero Average

This is a discussion on Non-zero Average within the Excel Questions forums, part of the Question Forums category; Does anyone know how to calculate the average of a range of numbers, excluding zeros?...

1. Does anyone know how to calculate the average of a range of numbers, excluding zeros?

2. On 2002-03-19 14:18, Markscozz wrote:
Does anyone know how to calculate the average of a range of numbers, excluding zeros?
{=AVERAGE(IF(A1:A5,A1:A5))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

3. On 2002-03-19 14:18, Markscozz wrote:
Does anyone know how to calculate the average of a range of numbers, excluding zeros?
There are a few formulas for computing the average of non-zero values in a range:

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

to propose my preferred formula.

4. On 2002-03-19 14:24, Aladin Akyurek wrote:
On 2002-03-19 14:18, Markscozz wrote:
Does anyone know how to calculate the average of a range of numbers, excluding zeros?
There are a few formulas for computing the average of non-zero values in a range:

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

to propose my preferred formula.

You might be able to shorten this just a little and still preserve the dreaded text problem with

=SUM(A1:A9)/MAX(1,COUNT(A1:A9)-COUNTIF(A1:A9,0))

Over this course of a lifetime, this could save you say, 30 seconds.

#### Posting Permissions

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