# Disregard 0 in an average calc

#### MRomanow

##### New Member
I have 14 cells that I paste in numbers from another program. I then have a formula that calculates the average of these 14 numbers. Sometimes the numbers that I copy and paste include a few zeros in them. How can I get the average calculation to disregard these zeros?

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### barry houdini

##### MrExcel MVP
use

=AVERAGE(IF(range,range))

confirmed with CTRL+SHIFT+ENTER

or with just ENTER

=SUM(range)/SUMPRODUCT(--(range<>0))

#### NBVC

##### Well-known Member
Try

=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

Confirmed with CTRL+SHIFT+ENTER, not just Enter

#### MRomanow

##### New Member
Thanks! That worked great!

#### al_b_cnu

##### Well-known Member
Hi,

an alternative without using [Ctl-Shift-enter] is
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")

#### Brian from Maui

##### MrExcel MVP
Try,

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

Replies
5
Views
132
Replies
8
Views
233
Replies
3
Views
435
Replies
4
Views
224
Replies
1
Views
541

1,196,008
Messages
6,012,836
Members
441,733
Latest member
MartijnB

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