# Formula to drop three lowest scores from an average

#### Luly

##### New Member
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?

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
ABCD
11Ave.
227
33
44
55
66
77
88
99
1010
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

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?

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.

1,221,409
Messages
6,159,711
Members
451,587
Latest member
srice33

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back