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

[Jwood]

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.

 

[nbrcrunch]

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

Curious... I have for years used the syntax originally indicated by Cbrine (2nd in thread). What benefit does the other two formula have over the much easier IF(formula,"",formula)

 

[Jay Petrulis]

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

Curious... I have for years used the syntax originally indicated by Cbrine (2nd in thread). What benefit does the other two formula have over the much easier IF(formula,"",formula)

The IF formula construct would run into the nested IF limit on larger data ranges. You can also get into more complex formulae if you want to make the formula robust (eliminate text, blanks, zeroes, etc.).

 

[chelstars3134]

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

I realize this is an old post, but hopefully someone will see this and reply. I am doing a similar spreadsheet as the original post. I want to average my monthly spending. Each month has its own spreadsheet of daily spending with categories (gas, food, etc.). The monthly totals for each category are formulated into a monthly totals sheet. Until the data is entered into the daily spending of the, let's say, January spreadsheet, the value in the monthly totals for January is zero. Within the monthly totals sheet, I want to average each category for the current months that I actually have entered data for. I don't want to include the zero values because of incomplete data. HOWEVER, here is the catch, I DO want to average zeros that come from entered data. For instance, if I spent $0.00 in January and $50 in February, I want to average those 2 numbers, but not include the zeroes from March to December. Is there any way to do this without having to redo all the formulas (aside from the formulas for the average of each category)?

This is my first excel spreadsheet using formulas, so I think I'm doing pretty good with everything I've figured out so far, but this one has me stumped! Thanks in advance for your help!

 

[Aladin Akyurek]

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

I realize this is an old post, but hopefully someone will see this and reply. I am doing a similar spreadsheet as the original post. I want to average my monthly spending. Each month has its own spreadsheet of daily spending with categories (gas, food, etc.). The monthly totals for each category are formulated into a monthly totals sheet. Until the data is entered into the daily spending of the, let's say, January spreadsheet, the value in the monthly totals for January is zero. Within the monthly totals sheet, I want to average each category for the current months that I actually have entered data for. I don't want to include the zero values because of incomplete data. HOWEVER, here is the catch, I DO want to average zeros that come from entered data. For instance, if I spent $0.00 in January and $50 in February, I want to average those 2 numbers, but not include the zeroes from March to December. Is there any way to do this without having to redo all the formulas (aside from the formulas for the average of each category)?

This is my first excel spreadsheet using formulas, so I think I'm doing pretty good with everything I've figured out so far, but this one has me stumped! Thanks in advance for your help!

What did you try?

 

[JLGWhiz]

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

ray:

Wow. Both worked.

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

The latter is an Array formula, like a mini macro. The brackets are put in by Excel and Identiify the formula as an Array visually. If you do not use the Ctrl + Shift + Enter, you get the #Value error.

 

[chelstars3134]

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

What did you try?

Honestly haven't had much time to play around with it. Currently it is {=AVERAGE(IF(C43:N43>0,C43:N43))}, which is fine for the ones that I always spend more than $0 for (gas, food, necessities). But for the things I want to pat myself on the back for when coming in under budget, ie $0, I need the average to reflect that. So if in January I didn't buy any clothes, Day 1-31 would equal $0 in the January clothes total cell (=SUM(C9:AG9)), which is what the above formula ultimately references.

I wouldn't even know where to begin to say "if the January clothes total cell equals zero from actual entered cell data, then average it... but don't average it if the formula has not calculated actual data."

Hmph.

I think I may have talked my way to an answer. The problem isn't fundamentally the formula in the yearly averages cell.... the problem is that the formula in the daily sum totals cell treats blank cells as zero. I imagine I'll have to address the blank cells issue in the yearly average formula too.

I'm a little sleep deprived... I'll take a stab at it tomorrow when my brain is fully functioning!

 

[Aladin Akyurek]

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

Honestly haven't had much time to play around with it. Currently it is {=AVERAGE(IF(C43:N43>0,C43:N43))}, which is fine for the ones that I always spend more than $0 for (gas, food, necessities). But for the things I want to pat myself on the back for when coming in under budget, ie $0, I need the average to reflect that. So if in January I didn't buy any clothes, Day 1-31 would equal $0 in the January clothes total cell (=SUM(C9:AG9)), which is what the above formula ultimately references.

I wouldn't even know where to begin to say "if the January clothes total cell equals zero from actual entered cell data, then average it... but don't average it if the formula has not calculated actual data."

Hmph.

I think I may have talked my way to an answer. The problem isn't fundamentally the formula in the yearly averages cell.... the problem is that the formula in the daily sum totals cell treats blank cells as zero. I imagine I'll have to address the blank cells issue in the yearly average formula too.

I'm a little sleep deprived... I'll take a stab at it tomorrow when my brain is fully functioning!

Try...

=IF(COUNT(C9:AG9),SUM(C9:AG9),"")

instead of just:

=SUM(C9:AG9)


By the way, if you are on Excel 2007 or later:

=AVERAGEIF(C43:N43,">0",C43:N43)

which needs just enter.

 

[chelstars3134]

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

Try...

=IF(COUNT(C9:AG9),SUM(C9:AG9),"")

instead of just:

=SUM(C9:AG9)

That worked! Thanks!

 

[Aladin Akyurek]

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

That worked! Thanks!

\

You are welcome. Thanks for providing feedback.

 

[nmgmarques]

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

Since we are on the topic of averages, I have a related question:
What if I want to average a range only if there are no zero value cells in that range?
Say a1:e1 all non-zero, then average a1:e1, but if a1:e1 contains 1 zero value cell, don't average them? Right now I am using a countif in f1 and in g1 I have a =if(f1=5;=average(a1:e1);"n.a.").
This works, but I need an extra column (which is hidden anyway). But is there a better way to keep it all down to a single formula?