Page 1 of 4 123 ... LastLast
Results 1 to 10 of 39

How do I exclude cells that have 0 in them from my average?

This is a discussion on How do I exclude cells that have 0 in them from my average? within the Excel Questions forums, part of the Question Forums category; I have a list of weeks 1-4 for the fiscal month of January. I have a total column. Each week ...

  1. #1
    New Member
    Join Date
    Dec 2003
    Posts
    30

    Default How do I exclude cells that have 0 in them from my average?

    I have a list of weeks 1-4 for the fiscal month of January. I have a total column. Each week the appropriate week is updated and the total is updated via formula. The total column is just formulas adding Weeks 1-4 up. I also have an average column with the AVERAGE formula beside the total it that should give me the weekly average for January. However, it's trying to average all the weeks instead of just the weeks that I am on.

    For example, Week 1 is 1,000,000. Week 2 is 500,000. Week 3 & 4 are 0 because there is no data in there yet.
    The AVERAGE formula keeps showing 375,000 instead of 750,000. It's averaging all the weeks and I just want it to average Weeks 1 & 2 right now, but automatically average Weeks 3 & 4 when they are populated.

  2. #2
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: How do I exclude cells that have 0 in them from my avera

    If you delete the 0 values from the cells, your average will work correctly. Excel will average numbers(ie. 0), but not null value cells(ie. blank).
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  3. #3
    New Member
    Join Date
    Dec 2003
    Posts
    30

    Default Re: How do I exclude cells that have 0 in them from my avera

    Quote Originally Posted by Cbrine
    If you delete the 0 values from the cells, your average will work correctly. Excel will average numbers(ie. 0), but not null value cells(ie. blank).
    I can't delete the 0 values because they are formulas for the coming weeks. I don't want the user to have to copy the formulas every week. They just need to key in the data and the formulas do the rest.

  4. #4
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: How do I exclude cells that have 0 in them from my avera

    You could encapsulate your formula with =If (YourFormula=0,"",YourFormula). This will give you blanks instead of zeros. That's if the 0 doesn't matter.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

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

    Default

    Something like this!

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

    excludes blanks and zeros

  6. #6
    New Member
    Join Date
    Dec 2003
    Posts
    30

    Default

    Quote Originally Posted by 2rrs
    Something like this!

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

    excludes blanks and zeros
    I tried that, but I got #VALUE! errors.

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

    Default Re: How do I exclude cells that have 0 in them from my avera

    Two options

    =SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

    or

    AVERAGE(IF(Range,Range))

    The latter must be confirmed with control+shift+enter instead of just enter.

  8. #8
    New Member
    Join Date
    Dec 2003
    Posts
    30

    Default Re: How do I exclude cells that have 0 in them from my avera

    Quote Originally Posted by Aladin Akyurek
    Two options

    =SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

    or

    AVERAGE(IF(Range,Range))

    The latter must be confirmed with control+shift+enter instead of just enter.


    Wow. Both worked.

    On the latter, how does that formula work and how does putting brackets around it do anything?

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

    Default

    Quote Originally Posted by Jwood
    Quote Originally Posted by 2rrs
    Something like this!

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

    excludes blanks and zeros
    I tried that, but I got #VALUE! errors.
    Confirm with ctrl, shift, enter

  10. #10
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: How do I exclude cells that have 0 in them from my avera

    Quote Originally Posted by Aladin Akyurek
    Two options

    =SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

    or

    AVERAGE(IF(Range,Range))

    The latter must be confirmed with control+shift+enter instead of just enter.
    The former,

    =SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

    is more robust. The AVERAGE(IF(Range,Range)) will return #VALUE! errors if there is text in the range (or formula blanks), and #DIV/0! if all entries are zero.
    Bye,
    Jay

Page 1 of 4 123 ... 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