Averaging blank or cells with Zeros in them.
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.
--
Thank y very much for your time.
Glenn
"Shane Devenshire" wrote:
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))
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"Glenn" wrote:
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(ISBL ANK(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.
--
Thank You in advance.
Glenn
|