+ Reply to Thread
Results 1 to 4 of 4

Ignore Blank Cells

  1. #1
    Registered User
    Join Date
    01-27-2008
    Posts
    15

    Ignore Blank Cells

    Hi all,

    I'm creating a spreadsheet for work and it will basically consist of a list of percentages followed by an average.

    I tried using the =AVERAGE function BUT it returned DIV/0! errors. To combat that I used the formula +IF((COUNT(B6:B13)>0),AVERAGE(B6:B13),"") so that the average is blank if the cells are empty.

    Only problem with that is that the COUNT function is returning a "Refers to empty cells error" on every formula cell. I could just turn off the error checking but because this is used on loads of computers and potentially ones that I don't have access to I can't do that for all of them.

    Anyone got any other suggestions?

    Thanks,

    Kevin

  2. #2
    Registered User
    Join Date
    01-27-2008
    Location
    Dorset, UK
    Posts
    63
    How about =SUM(B6:B13)/COUNTIF(B6:B13,">0") or =IF(COUNTIF(B6:B13,">0")=0,0,SUM(B6:B13)/COUNTIF(B6:B13,">0")

    Bozo

  3. #3
    Registered User
    Join Date
    01-27-2008
    Posts
    15
    Unfortunately, they both create the same error.

    Thanks anyway,

    Kevin.

  4. #4
    Registered User
    Join Date
    01-27-2008
    Posts
    4

    what about this:

    if(sum(b6:b13)>0,average(b6:b13),"")

    meaning:
    "if the sum of the range is bigger than 0, then give the average, else give a blank."

+ 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