[SOLVED] averaging problem

**ditchy** · 02-13-2010, 04:25 AM

Hello
would someone be kind enough to help me with this average question.
The formula belwo works perfectly but I would like to change it so that it will average if there is only 1 or up to 15 numbers. At present it will only average and work if there are 4 or more numbers

=AVERAGE(SMALL(IF('previous race results'!$A$1:$A$861=B36,'previous race
results'!$B$1:$B$861),{1,2,3,4}))

regards
Ditchy

**DonkeyOte** · 02-13-2010, 04:57 AM

Ditchy, I would be inclined to store the COUNTIF in an adjacent cell to the AVERAGE, eg:

Code:

C36: =IF(B36="",0,MIN(15,COUNTIF('previous race results'!$A$1:$A$861,B36)))

Then adapt the Array accordingly

Code:

=IF($C36=0,"",AVERAGE(SMALL(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861),ROW(A$1:INDEX(A:A,$C36)))))
confirmed with CTRL + SHIFT + ENTER

That said the above will still be a poor performer in terms of calculation time.

**ditchy** · 02-13-2010, 05:25 AM

Hi DonketOte
thanks for the quick reply,
Is there a way to modify so that it only averages up to 4 of the numbers (in column "B"), regardless of the 1 or up to 15 numbers in column "B"
thanks
Ditchy

**DonkeyOte** · 02-13-2010, 05:33 AM

Right, sorry, I misinterpreted your request as wanting to average the lesser of 15 and count of instances.

It seems then that the intention is to average lesser of 4 and count of all instances where count of all instances is >= 1 and <=15 ...

Any item (Col B) with a frequency either side of those boundaries (ie non existent or appears >15 times) is to be ignored in the Average calc.

So

Code:

C36: =IF(B36="",0,COUNTIF('previous race results'!$A$1:$A$861,B36))

then with the above revision in place:

Code:

=IF(CEILING($C36,15)<>15,"",AVERAGE(SMALL(IF('previous race results'!$A$1:$A$861=B36,'previous race results'!$B$1:$B$861),ROW(A$1:INDEX(A:A,MIN(4,$C36))))))
confirmed with CTRL + SHIFT + ENTER