# add and average numbers, excluding zeros

• I am currently working on an excel 97 spreadsheet where I need to average percentages. However, not all the numbers that I am averaging are numbers; they are zeros, and I do not want to include these zeros in my average. Therefore, I want to add only the numbers entered, then find the average of those number. For example:
95%
0
0
92%
85%
Total: 91% (divided by 3 not 5)
I do need to keep the zeros in, but I cannot include them in my count.
Please advise me on how I could enter a formula that would handle this problem.

Thank you,
Max

• Hi,

try this;
=SUMIF(L41:L49,"&gt;0")/COUNTIF(L41:L49,"&gt;0")

2rrs

• Hi max

You can also use the DAVERAGE, e.g

=DAVERAGE(A1:A6,1,C1:C2)

Where A1 has a heading and A2:A6 contain the numbers. 1 indicates the Column posiotion in the table, always 1 for a single column. C1 has a copy of you heading in A1 and C2 has <>0 entered in it.

The other approach is via an array formula, eg

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

You MUST enter this by pushing Ctrl+Shigt+Enter

Arrays are ok in small amounts if they only references small ranges. Too many referencing large ranges WILL sloooow down Excel.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!