Averaging blank or cells with Zeros in them.


G

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(D3:D15)/COUNTIF(D3:D15,">0")
=IF(COUNT(B1:B10)>0,AVERAGE(B1:B10),"")
=AVERAGE(IF(A2:A5,A2:A5,A2:A5))
=SUMPRODUCT(--(B5:D5),(B2:D2))/(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.
 
Ad

Advertisements

T

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?
 
S

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))
 
G

Glenn

Thank you for responding to me.

Sorry for my description.

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


Thank You Again
Glenn


T. Valko said:
*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?
 
Ad

Advertisements

G

Glenn

Thank you for responding to me.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top