Average with text exclude 0 or Null

[L40]

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!!

 

[pgc01]

Hi L40
Welcome to the board

Can there be negative numbers in B63?

 

[T. Valko]

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]

No the number will always be a positive number.

 

[L40]

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]

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]

I understand now, Thanks once again for your help!

 

[T. Valko]

I understand now, Thanks once again for your help!

You're welcome. Thanks for the feedback!

 

[scottylad2]

=SUM(E3:E12)/COUNTIF(E3:E12,">"&0) if you replace E3:E12 range with your range, this will work as well