Formula to drop three lowest scores from an average

[Luly]

I would like to calculate an average (homework grades) but one that would drop the three lowest scores. I have done formulas in the past to drop the lowest score from an average (using MIN) but how to do that for more than one value to be excluded?

Thanks in advance for your help!

 

[PaddyD]

welcome to the board!

This is the wrong forum for excel questions - this forum is for questions about the board. The right forum for excel questions is here:

http://www.mrexcel.com/board/viewforum.php?forum=2&89791

That said, here's one way:

Book6
	A	B	C	D
1	1	Ave.
2	2	7
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11
Sheet3

which get the average of a1:a10 only for tose numbers that are greater than the third smallest number:

=AVERAGE(IF(A1:A10>SMALL(A1:A10,3),A1:A10))

This formula needs to be array entered - using control + shift + enter rather than just enter. If done right, excel will add 'curly' brackets around the formula (which you can see in the formula bar)

For more on array formulas, see:

http://www.mrexcel.com/tip011.shtml

Paddy

 

[Luly]

The array formula is very elegant. The only problem is that if you have several values equal to the third lowest value you will end up dropping more than just three low scores. My inelegant formula

=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)

drops only 3 scores. I use divide using the count function so I can keep a running average during the semester that will update as scores are added. Any ideas on how to tweak your array formula so that it would only discard a preset number of values?

 

[Aladin Akyurek]

On 2002-08-31 08:14, Luly wrote:
The array formula is very elegant. The only problem is that if you have several values equal to the third lowest value you will end up dropping more than just three low scores. My inelegant formula

=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)

drops only 3 scores. I use divide using the count function so I can keep a running average during the semester that will update as scores are added. Any ideas on how to tweak your array formula so that it would only discard a preset number of values?

=(SUM(B2:H2)-SUM(SMALL(B2:H2,{1,2,3})))/(COUNT(B2:H2)-3)

This is not an array-formula.