# Average without zeros

#### goosteroo

##### New Member
Hi. How do I calculate averages without including zeros?

i.e.

A B C D
0 6 0 2

The AVERAGE() formula gives "2" as the answer, but without including "0," it's 4.

What would the formula be?

Thanks.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,

try this
A   B  C
1 txt    4
2 2
3 0
4 6

list

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
C1    {=AVERAGE(IF((A1:A4)<>0,A1:A4))}

enter formula without {}
confirm with Control-Shift-Enter

[Table-It] version 06 by Erik Van Geit``````

kind regards,
Erik

Either

=AVERAGE(IF(A1:D1<>0,A1:D1))

confirmed with CTRL+SHIFT+ENTER

or, assuming all data is numeric

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

which only requires ENTER

edited typo in first formula

Try this: =SUM(A1:D1)/COUNTIF(A1:D1,"<>0")
/s/ Larry

Over here you will find similar responses, plus a UDF as well.

Gene, "The Mortgage Man", Klein

Replies
1
Views
128
Replies
2
Views
125
Replies
4
Views
220
Replies
7
Views
349
Replies
6
Views
365

Threads
1,207,012
Messages
6,076,147
Members
446,187
Latest member
LMill

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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