# Array Average

#### tlynn

##### Board Regular
I have been using this formula to average a range without averaging in the zeros:

Code:
``{=AVERAGE(IF(A1:A10,A1:A10))}``

to do some averaging..obviously..and have recently ran into a problem. When there is nothing yet in the range to average, (all zero's for a month that has not happened yet), then I get a #DIV/0!. Normally this would not be a huge problem using:

Code:
``=IF(ISERROR(BLA, BLA),"",(BLA, BLA))``

but...this isnt working now either and it states that there is an error with the double quote area of the formula. Is there a different way to do this?

Any help would be appreciated and thanks in advance.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

##### MrExcel MVP
Try...
Code:
``````=LOOKUP(9.99999999999999E+307,
CHOOSE({1,2},
0,
AVERAGE(IF(A1:A10 > 0, A1:A10))))``````

#### tlynn

##### Board Regular
Bump...anyone got any ideas?

#### Scott Huish

##### MrExcel MVP
If all your values are greater than 0, here's another one:

=SUMIF(A1:A10,">0")/MAX(1,COUNTIF(A1:A10,">0"))

Replies
1
Views
174
Replies
1
Views
459
Replies
2
Views
345
Replies
4
Views
269
Replies
1
Views
174

1,191,685
Messages
5,988,058
Members
440,125
Latest member
vincentchu2369

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