Average with text exclude 0 or Null

L40

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

pgc01

MrExcel MVP
Hi L40
Welcome to the board

Can there be negative numbers in B63?

T. Valko

Well-known Member
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
No the number will always be a positive number.

L40

Board Regular

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

I understand now, Thanks once again for your help!

T. Valko

Well-known Member
I understand now, Thanks once again for your help!
You're welcome. Thanks for the feedback!

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

Replies
4
Views
130
Replies
3
Views
81
Replies
2
Views
55
Replies
1
Views
54
Replies
1
Views
41