Home |
Search |
Today's Posts |
#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
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
"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))))),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interest Accrual Question | Excel Discussion (Misc queries) | |||
Checkbox not part of cell. (???) Cells can move "underneath". (!!!) | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) | |||
Question on VBA | Excel Discussion (Misc queries) |