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

Jwood

New Member
Joined
Dec 17, 2003
Messages
35
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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).
 
Upvote 0
Re: How do I exclude cells that have 0 in them from my avera

Cbrine said:
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.
 
Upvote 0
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.
 
Upvote 0
Something like this!

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

excludes blanks and zeros
 
Upvote 0
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.
 
Upvote 0
Re: How do I exclude cells that have 0 in them from my avera

Aladin Akyurek said:
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.

:pray:

Wow. Both worked.

On the latter, how does that formula work and how does putting brackets around it do anything?
 
Upvote 0
Re: How do I exclude cells that have 0 in them from my avera

Aladin Akyurek said:
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top