+ Reply to Thread
Results 1 to 11 of 11

Average ignoring Blanks cells

  1. #1
    Registered User
    Join Date
    11-05-2007
    MS-Off Ver
    Mac 2011
    Posts
    98

    Average ignoring Blanks cells

    Hi there,

    I am trying to Average cells C9:O9 however I need it to ignore cells with #N/A. I am using ISNA in each of the cells C9:O9 to make them blank when no data is Available.
    The average sum would be in P9.

    I have tried various posts in the forum but none of them help im my case.

    If anyone could help that would be great?

    Thanks

    Justin.
    Last edited by Justinmih; 10-02-2011 at 12:03 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Average ignoring Blanks cells

    Can you upload example workbook (make sure to remove all private data but try to leva your error cells).

  3. #3
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Average ignoring Blanks cells

    Hi Justin,

    The AVERAGE function does ignore blank cells and the SUM function (presumably to sum the averages) also do ignore blank cells.

    Can you please clarify what the problem is? Posting a sample workbook would help.

  4. #4
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Average ignoring Blanks cells

    Quote Originally Posted by Justinmih View Post
    Hi there,

    I am trying to Average cells C9:O9 however I need it to ignore cells with #N/A. I am using ISNA in each of the cells C9:O9 to make them blank when no data is Available.
    The average sum would be in P9.

    I have tried various posts in the forum but none of them help im my case.

    If anyone could help that would be great?

    Thanks

    Justin.
    In Cell P9 type =AVERAGE(c9:o9). It will take care of your blank cells too.
    thank you

  5. #5
    Registered User
    Join Date
    11-05-2007
    MS-Off Ver
    Mac 2011
    Posts
    98

    Re: Average ignoring Blanks cells

    Hi there,
    I doesn't seem average them out for some reason, I have attached the example to let you see.
    I have 12 cells to average, if only 2 cells had numbers in it then i would expect it to give me an average of the 2 numbers, the remaining 10 cells would be blank until the information was available.

    thanks

    Justin.
    Attached Files Attached Files

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average ignoring Blanks cells

    If you want to ignore all errors like #N/A in the range then try this formula

    =IF(COUNT(C9:O9),SUMIF(C9:O9,">0")/COUNT(C9:O9),"")
    Last edited by daddylonglegs; 09-30-2011 at 08:53 AM.
    Audere est facere

  7. #7
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Average ignoring Blanks cells

    Quote Originally Posted by Justinmih View Post
    Hi there,
    I doesn't seem average them out for some reason, I have attached the example to let you see.
    I have 12 cells to average, if only 2 cells had numbers in it then i would expect it to give me an average of the 2 numbers, the remaining 10 cells would be blank until the information was available.

    thanks

    Justin.
    First Use IFERROR FUNCTION to remove your #N/A. Put =IFERROR(IF(VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)=0,NA(),VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE))*100,"") from c9 thru o9 then in p9 use =AVERAGE(C9:O9), you will get the desired result.

  8. #8
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Average ignoring Blanks cells

    DDL's formula will work. I dont know if Averageif works in Excel 2002. If it does, then formula

    =AVERAGEIF(C8:O8,">0",C8:O8)

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Average ignoring Blanks cells

    If Justin is using Excel 2002 as per his profile then IFERROR won't be available to him - and anyway it doesn't make sense to use NA() in the formula to actually generate an error and then use IFERROR to eliminate the error......

    This version would work in Excel 2002 to remove any errors or zeroes and give blank cells

    =IF(ISERROR(1/VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)),"",VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)*100)

    ...but isn't it feasible for the absence to be zero?

    If you use that version then you'll get errors in row 10 so use this version in row 10 copied across

    =IF(C9="","",C9-C8)

  10. #10
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Average ignoring Blanks cells

    The AVERAGE function does ignore blank cells,
    (unless your cell is not blank, but has a zero in it)

    so.. taking into account number are OVER Zero
    =SUM(A:A)/COUNTIF(A:A,">0")

    So... If Above and Below Zero
    =SUM(A:A)/COUNTIF(A:A,"<>0")

    So if below Zero
    =SUM(A:A)/COUNTIF(A:A,"<0")

    Something to play with see how you do

    Edit the range to your requirements of cause

  11. #11
    Registered User
    Join Date
    11-05-2007
    MS-Off Ver
    Mac 2011
    Posts
    98

    Re: Average ignoring Blanks cells

    Hi Guy's
    Thanks to all who have replied.

    I see that you mentioned in my profile I am using Excel 2002. I am working between 3 different computers and have noticed that I switching between 2002,2003 and Mac2011, I take it all these versions make a difference?

    I have went with the following formulas and they seem to work however I better check on all 3 versions.

    In cells C9:O9 they have
    Please Login or Register  to view this content.
    and in P9
    Please Login or Register  to view this content.
    I have noticed though it has caused the Graph to report a graph line of zero's where it is pulling the information from C9:O9 is there a way round this?

    I have attached the up to date example to let you see.
    Last edited by Justinmih; 10-02-2011 at 12:04 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1