Averaging blank or cells with Zeros in them.
 

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

Averaging blank or cells with Zeros in them.
 

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


--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
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

Averaging blank or cells with Zeros in them.
 

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

Averaging blank or cells with Zeros in them.
 

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" wrote:

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


--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
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

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