average numbers that are greater than zero

singlgl1

Board Regular
Joined
Jan 13, 2007
Messages
127
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
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Joined
Jun 30, 2005
Messages
2,015
=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")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
=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
Joined
Jan 13, 2007
Messages
127
Still getting the formula error using the above suggestions.maybe I'm overlooking something..
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Joined
Jun 30, 2005
Messages
2,015
=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? :oops:
 

singlgl1

Board Regular
Joined
Jan 13, 2007
Messages
127
Downloaded the free morefunc.xll and it works great. Thanks for all the help.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top