Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Ignore blank cells in a average

This is a discussion on Ignore blank cells in a average within the Excel Questions forums, part of the Question Forums category; All, I am doing an average of a column of cells. How do I tell excel to ignore the blank ...

  1. #1
    New Member
    Join Date
    Oct 2003
    Posts
    3

    Default Ignore blank cells in a average

    All,

    I am doing an average of a column of cells. How do I tell excel to ignore the blank cells in the column and not calculate them in the average?

    G.

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: Ignore blank cells in a average

    If the cells are truly blank the Average() formula will overlook them.
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Hi

    Welcom to the board.

    Excel does this automatically. Just use =average(A:A) or something like that and blank cells will be ignored automatically.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: Ignore blank cells in a average

    The AVERAGE function will ignore blank cells, so it shouldn't be an issue (unless your cell is not, in fact, blank, but has a zero in it).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    If the cells are not blank and you only want values greater that zero calculated use something like this

    =SUM(A:A)/COUNTIF(A:A,">0")

    modify as needed.

    Jacob

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: Ignore blank cells in a average

    Actually, if negatives are a possibility and you want to ignore zeroes, you would want to modify DRJ's response a little:

    =SUM(A:A)/COUNTIF(A:A,"<>0")

    Otherwise a negative value could hose the average since it would be included in the SUM portion, but not in the COUNTIF portion.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Dec 2002
    Posts
    346

    Default

    Or:

    =AVERAGE(IF(N22:N28<>0,N22:N28))

    Confirm using ctrl, shift, enter

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,248

    Default Re: Ignore blank cells in a average

    Quote Originally Posted by jmiskey
    Actually, if negatives are a possibility and you want to ignore zeroes, you would want to modify DRJ's response a little:

    =SUM(A:A)/COUNTIF(A:A,"<>0")

    Otherwise a negative value could hose the average since it would be included in the SUM portion, but not in the COUNTIF portion.
    Try it on the following sample

    {3;4;2;"";"x";0;0;" ";0}

    that A2:A10 houses. A4 houses the formula

    =IF($B$1,1,"")

    where B1 is a unused, empty cell. Note that A9 houses a space.

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default Re: Ignore blank cells in a average

    Your right Aladdin (of course), the COUNTIF will count blank spaces and text and hose your formula.

    I guess the $64,000 question is: what does your data look like? Do you want to include zeroes in the average?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    Jul 2014
    Posts
    2

    Default Re: Ignore blank cells in a average

    Quote Originally Posted by Greg Truby View Post
    If the cells are truly blank the Average() formula will overlook them.
    That is not true if all cells are blank in range.

    I have a range of cells used for Status averages. Initially each week all cells are blank and then filled in as tasks are completed or partially.
    I want an average of all non-blank cells at the top but using any of the formulas given here, there is an error when all cells are blank. Any input?

Page 1 of 2 12 LastLast

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
  •  


DMCA.com