2 part question on averaging

[Guest]

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?

 

[Bob Phillips]

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

and

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

 

[Ron Rosenfeld]

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

 

[Aladin Akyurek]

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

 

[Harlan Grove]

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