Averaging blank or cells with Zeros in them.

[Glenn]

I am trying to average ten cells. Some of the cells have numbers that were
totals of two cells. "Sample" =E4+F5 total that will go to a separate cell
H5. There will be ten cells H5:Q5 that will collect my input. Some will be
blank or will have zeros in them.My problem is getting a correct average. I
have tried several formulas see below.

=SUM(D315)/COUNTIF(D315,">0")
=IF(COUNT(B1:B10)>0,AVERAGE(B1:B10),"")
=AVERAGE(IF(A2:A5,A2:A5,A2:A5))
=SUMPRODUCT(--(B55),(B22))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBLANK(D5),0,D2))
=SUM(D33,G33,J33,M33,P33,S33,V33,Y33)/COUNT(D33,G33,J33,M33,P33,S33,V33,Y33)
=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6))

=IF(COUNT(D33,G33,J33,M33,P33,S33,V33,Y33),AVERAGE(D33,G33,J33,M33,P33,S33,V33,Y33),"")

I have been working on this for several days. To no avail. If anyone has a
suggestions I would be excited to try it. If there is a way for me to up load
my practice worksheet to show you I would be more than happy to upload it or
send it to you. Some of my verbiage may not be what you are used to reading.

 

[T. Valko]

*Exactly* what cells do you want to average?

Hard to tell from all your sample formulas but I'm guessing you want to
exclude cells with 0?

 

[Shane Devenshire]

Hi,

You have all manner of cells referenced in your examples. Suppose your data
is in cells A1:A10 here are a couple of approaches

In 2007
=AVERAGEIF(A1:A10,"<>0")

In all versions if there are blanks or zeros:
=SUM(A1:A10)/SUMPRODUCT(--(A1:A10<>0))

 

[Glenn]

Thank you for responding to me.

Sorry for my description.

I am using the formula =SUM(D:515)/COUNT(D315,">0")
This seems to be working well.


Thank You Again
Glenn

*Exactly* what cells do you want to average?

Hard to tell from all your sample formulas but I'm guessing you want to
exclude cells with 0?

 

[Glenn]

Thank you for responding to me.

I am using Office 2003.
I used this formula =SUM(D315)/COUNTIF(D315,">0")
This worked well. I also used your formula and it also worked well.