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 (E-Mail Removed)> wrote in message
news:496F2DA0-5CC6-4522-A453-(E-Mail Removed)...
>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 <(E-Mail Removed)>, Bob Phillips
<(E-Mail Removed)> 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
news:g3p8qe$i8v$(E-Mail Removed)...
> 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 <(E-Mail Removed)>, Bob Phillips
> <(E-Mail Removed)> 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
news:g3p8qe$i8v$(E-Mail Removed)...
> 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 <(E-Mail Removed)>, Bob Phillips
> <(E-Mail Removed)> 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
> news:g3p8qe$i8v$(E-Mail Removed)...
> > 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 <(E-Mail Removed)>, Bob Phillips
> > <(E-Mail Removed)> 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 <(E-Mail Removed)>, Bob Phillips
<(E-Mail Removed)> 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=
>