+ Reply to Thread
Results 1 to 8 of 8

Average of separate cells, exclude zero

  1. #1
    Registered User
    Join Date
    11-05-2007
    Posts
    1

    Average of separate cells, exclude zero

    I am trying to average particular cells and exclude zeros, but I have not been able to figure it out.
    I started with this formula:
    =SUM(C5:C18)/COUNTIF(C5:C18,">0")

    but really, it needs to be more like:
    =SUM(G8,J8,M8,P8,S8,V8,AB8,AE8,AH8,AK8,AN8,AQ8,AT8,AW8,AZ8,BC8,BF8,BI8)/COUNTIF(G8,J8,M8,P8,S8,V8,AB8,AE8,AH8,AK8,AN8,AQ8,AT8,AW8,AZ8,BC8,BF8,BI8,">0")

    i need to average specific cells, not a range.
    HELP!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Hi,

    This Array formula will do what you want (presuming the data is in A1:A8)

    =AVERAGE(IF(A1:A8>0,A1:A8,""))

    Enter this formula by pasting it in, and pressing CTRL, SHIFT and ENTER to confirm.

    If done correctly Excel will display it hence:

    {=AVERAGE(IF(A1:A8>0,A1:A8,""))} in the formula bar
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    So you want to average every third cell from G8 to BI8? not including zeroes. Try

    =AVERAGE(IF(MOD(COLUMN(G8:BI8)-COLUMN(G8),3)=0,IF(G8:BI8>0,G8:BI8)))

    confirmed with CTRL+SHIFT+ENTER

    ...although you might find it easier if you can identify the cells to be averaged in another way, e.g. if row 1 contains a specific header

  4. #4
    Forum Contributor corinereyes's Avatar
    Join Date
    12-02-2003
    Location
    Philippines
    MS-Off Ver
    MS Excel 2016
    Posts
    520
    I'm using xl 2007.

    Try:

    Please Login or Register  to view this content.
    Corine

  5. #5
    Registered User
    Join Date
    06-11-2007
    Location
    Nonthaburee Province, Thailand
    MS-Off Ver
    MS Office 2003 to MS Office 2010
    Posts
    82
    Try
    Please Login or Register  to view this content.
    Remind that you must press Ctrl+Shift+Enter

    The above formula will average the range that not contain zero value and not contain blank cell (Blank cell will return 0 if in the average range contain blank cell).
    Last edited by nattasiray; 11-06-2007 at 12:09 AM.
    N. Yauvasuta
    Power User Excel.

  6. #6
    pinmaster
    Guest
    Hi,

    Try this:

    =AVERAGE(IF(((MOD(COLUMN(G8:BI8),3)=1)*(G8:BI8>0)),G8:BI8))

    Enter using Ctrl+Shift+Enter


    HTH
    Jean-Guy

  7. #7
    Registered User
    Join Date
    06-07-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Average of separate cells, exclude zero

    I need to run an average of specific cells, not a range, and exclude zeros and blank cells (no negative numbers). This is what I am coming up with, but it doesn't work.

    =SUM(C8+F8+I8+L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8)/(COUNT(C8,F8,I8,L8,O8,R8,U8,X8,AA8,AD8,AG8,AJ8,">0"))

    Also I tried instead of "count" above, "countif" and it still does not function?

    Anybody know how to do this?

    Thanks,
    Taylor

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

    Re: Average of separate cells, exclude zero

    You can use my suggestion to do that, i.e.

    Quote Originally Posted by daddylonglegs View Post
    =AVERAGE(IF(MOD(COLUMN(G8:BI8)-COLUMN(G8),3)=0,IF(G8:BI8>0,G8:BI8)))

    confirmed with CTRL+SHIFT+ENTER
    Adjust the range as required
    Audere est facere

+ 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