Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging cells, excluding 0s (Excel 2000)

    I run a small call center and am trying to run average calls for the days that my staff is here in the office. I do not want to include those days that they are not here however and I do not know a forumla to say "total all the days and divide by X (representing the number of days they were here, excluding those that say "0"). Any suggestions? Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging cells, excluding 0s (Excel 2000)

    You can use a formula like the following:

    =SUM(A1:A100)/COUNTIF(A1:A100,">0")

    where A1:A100 is the range you want to calculate the average of.

    Note: if you leave the number of calls is empty for days on which a person is absent, you can use the AVERAGE function - it doesn't count empty and text cells.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Averaging cells, excluding 0s (Excel 2000)

    Or, technically more correct:
    =SUM(A1:A100)/COUNTIF(A1:A100,"<>0")
    though this shouldn't make a difference for the example given.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging cells, excluding 0s (Excel 2000)

    Not if A1:A100 is of mixed type...and/or have blanks/empty cells...

    =SUM(A1:A100)/MAX(1,COUNT(A1:A100)-COUNTIF(A1:A100,0))

    is I suppose what you're after.
    Microsoft MVP - Excel

Posting Permissions

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