Calculating average of percentages

  • Thread starter Thread starter Jimmy O
  • Start date Start date
J

Jimmy O

I have a column of percentage values some of which are zeros. I wish to
calculate an average of the percentage values but also wish to exclude all
zero values from the average formula but I'm not sure how to write an
argument to do this. Any help is appreciated.
 
'Loops thru A1 to A10
Sub test()
Dim sumOfAverages As Variant
Dim iCounter As Integer
sumOfAverages = 0
For i = 1 To 10
If Cells(i, 1).Value > 0 Then
sumOfAverages = sumOfAverages + Cells(i, 1).Value
iCounter = iCounter + 1
End If
Next
If iCounter > 0 Then
sumOfAverages = Format(sumOfAverages / iCounter, "0%")
Range("C1").Value = sumOfAverages
End If
End Sub
 
=AVERAGE(IF(rng<>0,rng))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hey, Bob -- that's very nice. I did not know AVERAGE could work
with an IF included inside! Neat-o. I've been doing this sort
of thing for years manually by adding up non-zero numbers in the range
and dividing by a COUNTIF of non-zero numbers in the range.

Handy!

=dman=

===================
 
Others work just as well, MAX, MIN, MEDIAN, etc.

In 2007 they have added a few, I think AVERAGEIF is one, but not all. Go
figure!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Also, even without the embedded if you could have done it without all that
hard work

=SUM(A1:A100)/COUNTIF(A1:A100,"<>0")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Many thanks Bob. This works nicely. jo

Bob Phillips said:
Also, even without the embedded if you could have done it without all that
hard work

=SUM(A1:A100)/COUNTIF(A1:A100,"<>0")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Bob Phillips said:
Also, even without the embedded if you could have done it without all that
hard work

=SUM(A1:A100)/COUNTIF(A1:A100,"<>0")
Click to expand...

True. Thanks for the reminder. Looking at my sheets, I see
that's what I've been doing. :-) I'll try the AVERAGE(IF...)
thing sometime soon enough, though. Thanks again.

=dman=
 
Back
Top