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

"Jimmy O" wrote:

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.

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

"Jimmy O" <Jimmy wrote in message
...
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.

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=

===================
In , Bob Phillips
spake thusly:

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

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)

"Dallman Ross" <dman@localhost. wrote in message
...
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=

===================
In , Bob Phillips
spake thusly:

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

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" <dman@localhost. wrote in message
...
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=

===================
In , Bob Phillips
spake thusly:

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

Many thanks Bob. This works nicely. jo

"Bob Phillips" wrote:

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" <dman@localhost. wrote in message
...
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=

===================
In , Bob Phillips
spake thusly:

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

In , Bob Phillips
spake thusly:

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=