# 2 part question on averaging

1. ## 2 part question on averaging

Hi,
I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
job averaging the data as long as there is a numeric value in one of the
cells. However, if there are no numbers I get a div/o error. Is there a fix
to that?
Question 2; Is there a way to average say the 5 highest values in the
G34:V34 row?
--
Geo

2. ## Re: 2 part question on averaging

=IF(SUM(G34:V34)=0,"",SUM(G34:V34)/COUNTIF(G34:V34,"<>0"))

and

=AVERAGE(LARGE(G34:V34,{1,2,3,4,5}))

--
HTH

-------

Bob Phillips
"Geo" <Geo@discussions.microsoft.com> wrote in message
news:B2C406C4-229A-47AC-A3EA-300AEC3CF381@microsoft.com...
> Hi,
> I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
> job averaging the data as long as there is a numeric value in one of the
> cells. However, if there are no numbers I get a div/o error. Is there a

fix
> to that?
> Question 2; Is there a way to average say the 5 highest values in the
> G34:V34 row?
> --
> Geo

3. ## Re: 2 part question on averaging

On Sun, 2 Jan 2005 08:47:02 -0800, Geo <Geo@discussions.microsoft.com> wrote:

>Hi,
>I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
>job averaging the data as long as there is a numeric value in one of the
>cells. However, if there are no numbers I get a div/o error. Is there a fix
>to that?

=IF(COUNT(rng)=0,"",AVERAGE(rng))

where rng is replaced with G34:V34

>Question 2; Is there a way to average say the 5 highest values in the
>G34:V34 row?

The array-entered formula:

=IF(COUNT(rng)=0,"",AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&MIN(5,COUNT(rng)))))))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.

--ron

4. ## Re: 2 part question on averaging

1]

=SUM(G34:V34)/MAX(1,COUNT(G34:V34)-COUNTIF(G34:V34,0))

2]

=IF(COUNT(G34:V34)>5,AVERAGE(LARGE(G34:V34,{1,2,3,4,5})),AVERAGE(G34:V34))

Geo wrote:
> Hi,
> I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
> job averaging the data as long as there is a numeric value in one of the
> cells. However, if there are no numbers I get a div/o error. Is there a fix
> to that?
> Question 2; Is there a way to average say the 5 highest values in the
> G34:V34 row?

5. ## Re: 2 part question on averaging

>1]
>
>=SUM(G34:V34)/MAX(1,COUNT(G34:V34)-COUNTIF(G34:V34,0))

Fine if values in the range would only be nonnegative. If values could be
negative as well as positive, then they almost certainly could be zero as
well. In other words, it should be sufficient to average only the positive
values. Also, if there were no numbers at all in the range, should the
result be 0?

An alternative.

=IF(COUNTIF(A1:A10,">0"),AVERAGE(IF(A1:A10>0,A1:A10)),"")

>2]
>
>=IF(COUNT(G34:V34)>5,AVERAGE(LARGE(G34:V34,{1,2,3,4,5})),AVERAGE(G34:V34))

Returns #DIV/0! if there are no numbers in range. An array formula
alternative.

=IF(COUNT(A1:A10),AVERAGE(LARGE(A1:A10,
ROW(INDIRECT("1:"&MIN(COUNT(A1:A10),5))))),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1