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

3. ## Re: How do I exclude cells that have 0 in them from my avera

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

5. Something like this!

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

excludes blanks and zeros

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

Wow. Both worked.

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

9. Originally Posted by Jwood
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. ## 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.
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.

Page 1 of 4 123 ... Last

#### Posting Permissions

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