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

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

On Sun, 2 Jan 2005 08:47:02 -0800, 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?

=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

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?

"Aladin Akyurek" wrote...
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:A100,A1:A1 0)),"")


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))))),"")