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

J

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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).
Click to expand...

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
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.
Click to expand...

: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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
205
Domenic
D
S
  • Solved
Way to move values based on another cell around like chess pieces
Replies
4
Views
338
myall_blues
myall_blues
C
  • Question Question
Adding weekly totals from one cell to an overall total
3 4 5
Replies
48
Views
1K
Logit
Logit
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
141
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
922
ashleighbrown
A

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

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