# Thread: Averaging cells, excluding 0s (Excel 2000)

1. ## 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. ## 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. ## 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.

4. ## 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.

#### Posting Permissions

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