# average numbers that are greater than zero

#### singlgl1

##### Board Regular
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)

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### dave3009

##### Well-known Member
=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

##### MrExcel MVP
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

##### Well-known Member
=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:

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

##### MrExcel MVP
=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:

Code:
`` =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

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

##### MrExcel MVP
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

##### Well-known Member
=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:

Code:
`` =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

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

Replies
6
Views
520
Replies
16
Views
608
Replies
2
Views
362
Replies
3
Views
162
Replies
3
Views
410

1,181,399
Messages
5,929,743
Members
436,687
Latest member
Glass of Gin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back