average numbers that are greater than zero

[singlgl1]

I have a formula in place currently that is averaging a cell from 30 sheets, but I would like to only average those numbers that are greater than zero.How can I edit the current formula(below) to accomplish this? Thanks!

=AVERAGE('Apr. 01-07:Apr. 30-07'!B8)

 

[dave3009]

=SUM(Apr. 01-07:Apr. 30-07'!B8)/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Perhaps, It's not tested so apologies if it doesn't work

Dave

 

[Aladin Akyurek]

I have a formula in place currently that is averaging a cell from 30 sheets, but I would like to only average those numbers that are greater than zero.How can I edit the current formula(below) to accomplish this? Thanks!

=AVERAGE('Apr. 01-07:Apr. 30-07'!B8)

If you have the free morefunc.xll add-in installed...

=SUM('Apr. 01-07:Apr. 30-07'!B8)/MAX(1,COUNTIF.3D('Apr. 01-07:Apr. 30-07'!B8,">0"))

 

[mortgageman]

=SUM(Apr. 01-07:Apr. 30-07'!B8)/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Perhaps, It's not tested so apologies if it doesn't work

Dave

Only the OP knows for sure, but if some of his or hers values are LESS than zero, then the formula should be:

 =SUMIF(Apr. 01-07:Apr. 30-07'!B8,">0")/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

 

[Aladin Akyurek]

=SUM(Apr. 01-07:Apr. 30-07'!B8)/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Perhaps, It's not tested so apologies if it doesn't work

Dave

Only the OP knows for sure, but if some of his or hers values are LESS than zero, then the formula should be:

 =SUMIF(Apr. 01-07:Apr. 30-07'!B8,">0")/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Excel's CountIf is not a 3D function...

 

[singlgl1]

Still getting the formula error using the above suggestions.maybe I'm overlooking something..

 

[Aladin Akyurek]

Still getting the formula error using the above suggestions.maybe I'm overlooking something..

Try the one with COUNTIF.3D, which requires the free morefunc.xll add-in...

 

[mortgageman]

=SUM(Apr. 01-07:Apr. 30-07'!B8)/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Perhaps, It's not tested so apologies if it doesn't work

Dave

Only the OP knows for sure, but if some of his or hers values are LESS than zero, then the formula should be:

 =SUMIF(Apr. 01-07:Apr. 30-07'!B8,">0")/COUNTIF(Apr. 01-07:Apr. 30-07'!B8, ">0")

Excel's CountIf is not a 3D function...

I didn't realize that the range being used was more than one dimension. I guess the . should have been the clue?

 

[singlgl1]

Downloaded the free morefunc.xll and it works great. Thanks for all the help.