Formula to drop three lowest scores from an average

Luly

New Member
Joined
Aug 28, 2002
Messages
2
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Paddy
 

Luly

New Member
Joined
Aug 28, 2002
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,148,528
Messages
5,747,221
Members
424,069
Latest member
kamkwok1hh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top