Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Averaging Cells With Zeros (2000)

    I have a very large range of cells (>100) that I want to compute an average of all non-zero cells. Many of the cells (>20) contain zeroes, and are scattered throughout the range, but I want to exclude them from my average calculation.
    Any help?
    Thanks,
    Jeff

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Cells With Zeros (2000)

    Jeff, just use the SUMIF function and divide by the COUNTIF function. For example, if your data is in A1:A20,<pre>=SUMIF(A1:A20,"<>0")/COUNTIF(A1:A20,"<>0")</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Averaging Cells With Zeros (2000)

    SammyB: This also solves a problem that just cropped up in one of my Excel 97 applications last Friday. Your answer has saved me a lot of researching time! Many thanks. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

Posting Permissions

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