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

Some videos you may like

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
Joined
Apr 25, 2006
Messages
19,834
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top