=AVERAGE(b7:b20) is counting fiels that are zero
 

I brought cell information from worksheet B into worksheet A, by typing = in
a cell and then clicking on cell in worksheet B and hitting enter. Data in
that cell is empty. So excell placed a "0" in the final cell. When I average
the column it of course adds the zero's. I don't want it to do that, only
the numbers gtreater that '0". How do I get this accomplished?

=AVERAGE(b7:b20) is counting fiels that are zero
 

Instead of something like:
=Sheet1!A1
use:
=if(Sheet1!A1="","",Sheet1!A1)
--
Gary''s Student - gsnu200908


"outrigger" wrote:

I brought cell information from worksheet B into worksheet A, by typing = in
a cell and then clicking on cell in worksheet B and hitting enter. Data in
that cell is empty. So excell placed a "0" in the final cell. When I average
the column it of course adds the zero's. I don't want it to do that, only
the numbers gtreater that '0". How do I get this accomplished?

=AVERAGE(b7:b20) is counting fiels that are zero
 

either modify the orginal formulas to
=IF(Sheet1!A1="","",Sheet1!A1)

OR average to be modified to as below
=SUMIF(b7:b20,"0")/COUNTIF(b7:b20,"0")

or
'array entered (ctrl+Shift+Enter)
=AVERAGE(IF(b7:B200,B7:B20))


If this post helps click Yes
---------------
Jacob Skaria


"outrigger" wrote:

I brought cell information from worksheet B into worksheet A, by typing = in
a cell and then clicking on cell in worksheet B and hitting enter. Data in
that cell is empty. So excell placed a "0" in the final cell. When I average
the column it of course adds the zero's. I don't want it to do that, only
the numbers gtreater that '0". How do I get this accomplished?