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).
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 ...
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.
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.Originally Posted by Cbrine
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.
Something like this!
=AVERAGE(IF(N22:N28<>0,N22:N28))
excludes blanks and zeros
I tried that, but I got #VALUE! errors.Originally Posted by 2rrs
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.
Originally Posted by Aladin Akyurek
Wow. Both worked.
On the latter, how does that formula work and how does putting brackets around it do anything?
Confirm with ctrl, shift, enterOriginally Posted by Jwood
The former,Originally Posted by Aladin Akyurek
=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.
Bye,
Jay
Like this thread? Share it with others