I am working on a simple grade spreadsheet, and would like a way to drop the lowest 2 or 3 scores from a range. I have not been able to find such a function in the documentation.
I can drop the lowest 1 score by taking the sum and subtracting the minimum, but I have not been able to find a function which returns the lowest n numbers for n > 1.
There is a function to get the k-smallest value from a list in the STATISTICS group of functions. Its name is simply SMALL.
Let the range be $A$11:$A$100 and let’s assume you want to exclude the lowest three values from the sum over that range.
=SUM($A$11:$A$110)-SMALL($A$11:$A$110;1)-SMALL($A$11:$A$110;2)-SMALL($A$11:$A$110;3)
or
=SUM($A$11:$A$110)-SUMPRODUCT(SMALL($A$11:$A$110;ROW($A$1:$A$3))
will do so. Using SUMPRODUCT for the second part will evaluate its parameter as an array expression. The range $A1:$A3 as parameter of ROW will therefore generate the sequence {1, 2, 3} over which the second parameter of SMALL will then iterate. SUMPRODUCT will add the three results.
To be more flexible you might want to parametrise some constituents of the expression. You may ask for further advice if interested.
(I attach this example.)
Great answer! @Lupp A shorter form for the =SUM($A$11:$A$110)-SMALL($A$11:$A$110;1)-SMALL($A$11:$A$110;2)-SMALL($A$11:$A$110;3) of the newer version excel is =SUM(A11:A110)-SUM(SMALL(A11:A110,{1,2,3}))
Great answer! @Lupp A shorter form for the =SUM($A$11:$A$110)-SMALL($A$11:$A$110;1)-SMALL($A$11:$A$110;2)-SMALL($A$11:$A$110;3) of the newer version excel is =SUM(A11:A110)-SUM(SMALL(A11:A110,{1,2,3}))
Thanks. … A thread from the vault …
Using the constant inline-array {1, 2, 3} would also work in Calc for you if your locale doesn’t use the comma as the decimal separator - and there is no special “row-delimiter” defined.
The silly localistion of symbols needed for special use spoils a lot.
This and the fact that similar tasks might require to create sequences of 50 or more natural numbers makes me prefer the construct I used.
On the other hand there is an issue with the term I suggested if rows are inserted or deleted.
=ROW(INDIRECT("a1:a3")) would avoid this problem too.
(Please use the semicolon as the parameter separator in formulae you post in international forums. Many locales don’t accept the comma in this role.)