Non-zero Average

[Markscozz]

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

 

[Mark W.]

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.

 

[Aladin Akyurek]

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.

Aladin

 

[IML]

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.

Aladin

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