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

[outrigger]

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?

 

[Gary''s Student]

Instead of something like:
=Sheet1!A1
use:
=if(Sheet1!A1="","",Sheet1!A1)

 

[Jacob Skaria]

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:B20>0,B7:B20))


If this post helps click Yes