Average with text exclude 0 or Null

L40

Board Regular
Joined
Mar 17, 2011
Messages
100
I seem to be having issues with a formula. Please Help!!! I have 2 separate work books. One work book (book 1) would be an average of 7 days of information by week. The other work book (Book 2) contains the daily information for 365 days. on book 1 is where the formula is being entered. I need to get the average of lets say 2-27-11 through 3-5-11 for cell B63 on book 2.

The formula i'm using is =AVERAGE('[Book 2.xlsx]3-05-11:2-27-11'!$B$63).

This works great but if i have a 0 or a null in B63 it needs to be excluded so that i can get a true uptime average. I beleive this is where the IF statement come in. Everything i have tried either returns a #REF error or a #Value error. Please help!!
 

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi L40
Welcome to the board

Can there be negative numbers in B63?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I seem to be having issues with a formula. Please Help!!! I have 2 separate work books. One work book (book 1) would be an average of 7 days of information by week. The other work book (Book 2) contains the daily information for 365 days. on book 1 is where the formula is being entered. I need to get the average of lets say 2-27-11 through 3-5-11 for cell B63 on book 2.

The formula i'm using is =AVERAGE('[Book 2.xlsx]3-05-11:2-27-11'!$B$63).

This works great but if i have a 0 or a null in B63 it needs to be excluded so that i can get a true uptime average. I beleive this is where the IF statement come in. Everything i have tried either returns a #REF error or a #Value error. Please help!!
Assuming there are no negative numbers...

=SUM('[Book 2.xlsx]3-05-11:2-27-11'!B63)/INDEX(FREQUENCY('[Book 2.xlsx]3-05-11:2-27-11'!B63,0),2)
 

L40

Board Regular
Joined
Mar 17, 2011
Messages
100

ADVERTISEMENT

Thanks Biff,

The formula =SUM('[Book 2.xlsx]3-05-11:2-27-11'!B63)/INDEX(FREQUENCY('[Book 2.xlsx]3-05-11:2-27-11'!B63,0),2)

Works great! :) Question why the #2 on the end? I tried this same formula but with out the #2
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Thanks Biff,

The formula =SUM('[Book 2.xlsx]3-05-11:2-27-11'!B63)/INDEX(FREQUENCY('[Book 2.xlsx]3-05-11:2-27-11'!B63,0),2)

Works great! :) Question why the #2 on the end? I tried this same formula but with out the #2
The FREQUENCY function returns an array of "count ifs" based on the bin arguments.

In this case we have a single bin argument of 0. The "count ifs" that are generated are:

"count if" range <=0
"count if" range >0

So, FREQUENCY returns 2 counts and since we want to exclude cells that contain 0 we need the 2nd count, "count if" range >0.

We use the INDEX function to get that 2nd count.

Let's assume this is our data:

<TABLE style="WIDTH: 26pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=34 border=0 x:str><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1088" width=34><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 26pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=34 height=17>1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3 </TD></TR></TBODY></TABLE>

=FREQUENCY(A1:A10,0)

Returns the array {4;6}.

"count if" A1:A10 <=0 = 4
"count if" A1:A10 >0 = 6

=INDEX(FREQUENCY(A1:A10,0),1) = 4
=INDEX(FREQUENCY(A1:A10,0),2) = 6

=INDEX({4;6},1) = 4
=INDEX({4;6},2) = 6
 

L40

Board Regular
Joined
Mar 17, 2011
Messages
100

ADVERTISEMENT

I understand now, Thanks once again for your help!:)
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
=SUM(E3:E12)/COUNTIF(E3:E12,">"&0) if you replace E3:E12 range with your range, this will work as well
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

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