how to average, ignoring zero

Question

Okay, so my boss wants me to average 4 months worth of percentages for our contractors.  But some contractors have 0% in some months and my boss doesn't want those counted (the contractor may not be assigned yet).

Is there a way to do this?

Thanks.

Heather

Answer 1

Hi,

Let's say that the range is A1:A10

(change as yours)

step1

in cell A11 write:

=myaverage(A1:A10)

step2

ALT+F11 to open VB editor.

Insert > module and paste the code below on the right

Close VB editor.

.........................

Function myaverage(rng As Range)

t = 0

s = 0

For Each r In rng

If r.Value > 0 Then

t = t + 1

s = s + r.Value

End If

Next r

myaverage = Round(s / t, 2)' 2 decimal places, change as yours

End Function

cell A11 Format Percentage

Answer 2

I tried that, substituting the A's for the correct letters and got a #VALUE error.

My apologies. I should have noted that you were on Office XP (aka 2002) but didn't. You cannot use the full column references. Substitute with something like,

=AVERAGE(IF(A1:A999>0,A1:A999))

... or,

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

... and enter with Ctrl+Shift+Enter.

Answer 3

I'll approach from a different angle.  Assume your numbers (and zeros, etc) are in column A, beginning at row 2 and ending at row 1052

=SUMIF(A2:A1052,">0") / COUNTIF(A2:A1052,">0")

That would calculate average of all POSITIVE, non zero values.  If you literally need average of non-zero values, positive and negative, then:

=SUMIF(A2:A1052,"<>0") / COUNTIF(A2:A1052,"<>0")

Answer 4

I tried that, substituting the A's for the correct letters and got a #VALUE error.

Can you tell me what the exact formula would be?

Answer 5

If the numbers to be averaged are in column A, try this array formula,

=AVERAGE(IF(A:A>0,A:A))

... for greater-than-zero values or,

=AVERAGE(IF(A:A<>0,A:A))

... if all non-zero values are to be considered (including less-than-zero)

Note that those formulas are array formulas and need to be entered with Ctrl+Shift+Enter↵ rather than simply Enter↵. If done correctly, Excel will wrap the formula in braces like this,

{ =AVERAGE(IF(A:A<>0,A:A)) }