add and average numbers, excluding zeros

max · May 9th 2003

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

**royUK** · May 9th 2003

Can you use this formula to place the number of cells not containing zero into a cell

=COUNTIF(A1:A6,"<>0")

Then use that result in your average formula

2rrs · May 10th 2003

Hi,

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

adjust range to suit.

2rrs

Dave Hawley · May 10th 2003

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.

add and average numbers, excluding zeros

Participate now!

Share