Calculating average of percentages

[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.

 

[Mike]

'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

 

[Bob Phillips]

=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)

 

[Dallman Ross]

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=

===================

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Jimmy O]

Many thanks Bob. This works nicely. jo

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)

 

[Dallman Ross]

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

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

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=