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.

 

[FormR]

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?

Replace F1 in your current formula with your COUNTIF formula:

For example:
=IF(F1=5;=AVERAGE(A1:E1);"n.a.")
Becomes
=IF(COUNTIF(A1:E1,"<>0")=5;AVERAGE(A1:E1);"n.a.")

 

[Aladin Akyurek]

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?

=IF(COUNTIF(A1:E1,0),"n.a.",AVERAGE(A1:E1))

If F1 = 5 must be still taken into account...

=IF(COUNTIF(A1:E1;0),"n.a.";IF(F1=5;AVERAGE(A1:E1);"n.a."))

 

[jitendra]

Try this formula and I hope it will solve your problem

=SUM(RANGE)/COUNTIF(RANGE,">0")

<colgroup><col></colgroup><tbody>
</tbody>

 

[nmgmarques]

Replace F1 in your current formula with your COUNTIF formula:

For example:
=IF(F1=5;=AVERAGE(A1:E1);"n.a.")
Becomes
=IF(COUNTIF(A1:E1,"<>0")=5;AVERAGE(A1:E1);"n.a.")

=IF(COUNTIF(A1:E1,0),"n.a.",AVERAGE(A1:E1))

If F1 = 5 must be still taken into account...

=IF(COUNTIF(A1:E1;0),"n.a.";IF(F1=5;AVERAGE(A1:E1);"n.a."))

Try this formula and I hope it will solve your problem

=SUM(RANGE)/COUNTIF(RANGE,">0")

<tbody>
</tbody>

I didn't try all options here, but I did try FormR's and it worked. And I know what I was doing wrong. For some reason I got it in my head that the formula had to be =if(=countif... That extra = was killing the formula and I wasn't getting anywhere because of it. Stupid mistake. Thanks all who replied!

 

[Aladin Akyurek]

I didn't try all options here, but I did try FormR's and it worked. And I know what I was doing wrong. For some reason I got it in my head that the formula had to be =if(=countif... That extra = was killing the formula and I wasn't getting anywhere because of it. Stupid mistake. Thanks all who replied!

=IF(COUNTIF(A1:E1,0),"n.a.",AVERAGE(A1:E1))

does not need testing for 5 non-zero values.

 

[JulieonMI]

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

Thanks for the tips. These formulas work great unless all of your cells have a value of 0%, in which case the formula returns an average value of 100% instead of 0%. Can anyone help me with this??

 

[Aladin Akyurek]

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

Thanks for the tips. These formulas work great unless all of your cells have a value of 0%, in which case the formula returns an average value of 100% instead of 0%. Can anyone help me with this??

Which formula did you try? And what is the result that you want to see in the all 0% case?

 

[JulieonMI]

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

Which formula did you try? And what is the result that you want to see in the all 0% case?

I used =AVERAGE(INDEX(3:3,MATCH(9.99999999E+307,3:3)-15):INDEX(3:3,MATCH(9.9999999E+307,3:3)))

When all fields were 0% I got an the result I got was 100% and what I wanted was 0% for the average.

This formula is repeated 180 times throughout the spreadsheet. I would love any help you can offer.

 

[Aladin Akyurek]

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

I used =AVERAGE(INDEX(3:3,MATCH(9.99999999E+307,3:3)-15):INDEX(3:3,MATCH(9.9999999E+307,3:3)))

When all fields were 0% I got an the result I got was 100% and what I wanted was 0% for the average.

This formula is repeated 180 times throughout the spreadsheet. I would love any help you can offer.

I get 0% with the formula you have and also with the equivalent:

=AVERAGE(OFFSET(INDEX(3:3,MATCH(9.99999999999999E+307,3:3)),0,0,1,-15))

 

[JulieonMI]

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

Hmmm. I will re-look at the data. Sounds like I may have some data further out on the page that I missed. That really helps to know it is not the formula but something else!!! Thank you!