# 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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi L40
Welcome to the board

Can there be negative numbers in B63?

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)

No the number will always be a positive number.

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

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

I understand now, Thanks once again for your help!

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

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

Replies
6
Views
121
Replies
8
Views
73
Replies
6
Views
413
Replies
3
Views
304
Replies
1
Views
522

1,203,046
Messages
6,053,192
Members
444,644
Latest member
keepontruckinc4

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

### Which adblocker are you using?

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

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