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.

 

[Aladin Akyurek]

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!

You are welcome.

 

[Debkat]

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

I have tried all the formulas suggested in this thread and none seem to work, the subtle difference being is that the range im working with are not say A1:A15. The range I have to calculate the average from, omitting zeros are all in different cells eg A1, F1, M1, T1 so I keep getting an error.

Ive managed to find a formula that does work on another thread =SUMPRODUCT(SUMIF(INDIRECT({"F19","M19","T19","AA19"}),"<>0",INDIRECT({"F19","M19","T19","AA19"})))/SUMPRODUCT(COUNTIF(INDIRECT({"F19","M19","T19","AA19"}),"<>0")) works a treat, however will not allow me to drag the formula to the rows of data I have under, it just copies the same formula.

Im at a bit of a brick wall, so any help appreciated.

TIA,

 

[Aladin Akyurek]

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

I have tried all the formulas suggested in this thread and none seem to work, the subtle difference being is that the range im working with are not say A1:A15. The range I have to calculate the average from, omitting zeros are all in different cells eg A1, F1, M1, T1 so I keep getting an error.

Ive managed to find a formula that does work on another thread =SUMPRODUCT(SUMIF(INDIRECT({"F19","M19","T19","AA19"}),"<>0",INDIRECT({"F19","M19","T19","AA19"})))/SUMPRODUCT(COUNTIF(INDIRECT({"F19","M19","T19","AA19"}),"<>0")) works a treat, however will not allow me to drag the formula to the rows of data I have under, it just copies the same formula.

Im at a bit of a brick wall, so any help appreciated.

TIA,

Try...

=SUM(F19,M19,T19,AA19)/MAX(1,INDEX(FREQUENCY((F19,M19,T19,AA19),{0}),2))

 

[Debkat]

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

OMG you are an absolute super star, thank you so much, been stuck on this for days.

Much appreciated!

Try...

=SUM(F19,M19,T19,AA19)/MAX(1,INDEX(FREQUENCY((F19,M19,T19,AA19),{0}),2))

 

[Aladin Akyurek]

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

OMG you are an absolute super star, thank you so much, been stuck on this for days.

Much appreciated!

You're welcome.

 

[tgallant06]

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.

I use a really basic formula to figure out my Tax Rate average. My husband and I track our pay stubs, then combine them to create our household income. I like my spreadsheets to look pretty, and I hate to see empty cells. The best way I have found to get an average without adding in 0, ignore the AVERAGE function all together:

=(SUM(A1:A20))/(COUNTIF(A1:A20,">0"))

It's how you learned to find averages in fourth grade. sum all the numbers together, then use the COUNTIF function to count the number of instances where the number is greater than zero.

Hope this helps!

 

[Aladin Akyurek]

I use a really basic formula to figure out my Tax Rate average. My husband and I track our pay stubs, then combine them to create our household income. I like my spreadsheets to look pretty, and I hate to see empty cells. The best way I have found to get an average without adding in 0, ignore the AVERAGE function all together:

=(SUM(A1:A20))/(COUNTIF(A1:A20,">0"))

It's how you learned to find averages in fourth grade. sum all the numbers together, then use the COUNTIF function to count the number of instances where the number is greater than zero.

Hope this helps!

That won't work on a collection of cells like A1, B2, D10, which are not contiguous. The original poster's query is of this nature.

 

[antoanand1981]

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

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.

Actually this formula works
=AVERAGEIF(G6:G10,"<>0",G6:G10)

 

[Aladin Akyurek]

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

Actually this formula works
=AVERAGEIF(G6:G10,"<>0",G6:G10)

Is it also mathematically appropriate, for example when:
-3
-8
0
4
3
2
0