Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Non-zero Average

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,609
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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