#### Jimbow

##### New Member
I work out the results at my club for yacht race series where competitors are allowed to discard their worst 1,2 or 3 results depending on the number of races in the series. Normally, I do this by hand but would like to use Excel to do it automatically. I know how to discard 1 worst result using the MAX function, eg. SUM(A1:E1)-MAX(A1:E1), but I would be grateful if someone could suggest a formula for discarding the highest 2 or 3 results.
Thank you.
Jim.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Check out the LARGE function in the xl help

HTH

Hi Jim:

Welcome to MrExcelBoard:

Building on sugestion by iridium, here is an illustration ...
Book2
ABCDE
153764
2
4FromTo
512
613
7
8SumOfRemainingRaces
912
10
Sheet1

Or using SMALL sum the nth number of races,

=SUM(SMALL(RANGE,{1,2,3,4,5}))

Brian from Maui said:
Or using SMALL sum the nth number of races,

=SUM(SMALL(RANGE,{1,2,3,4,5}))

I was holding that array answer back just in case you cheeky sod! :wink: Beat me to it in a way!

SumIf ExactlyLargestN jimbow.xls
ABCDEFGHIJKL
2202532172023207777
3
4
5
Sheet1

Formulas...

G2 records the number of worse scores to be discarded.

H2:

=COUNT(A2:E2)-G2

I2:

=LARGE(A2:E2,H2)

J2:

=SUMIF(A2:E2,">"&I2)+(H2-COUNTIF(A2:E2,">"&I2))*I2

which uses all of G2:I2, although, if so desired, H2 and I2 can be eliminated by incorporating them directly in the formula in J2.

K2 (an often used formula)

=SUMPRODUCT(LARGE(A2:E2,ROW(INDIRECT("1:"&H2))))

Created somewhat by accident, the formula in J2 is twice faster as the regular formula in K2. The difference in performance is largely due to INDIRECT in the latter formula.

Thank you Iridium for pointing me in the right direction, I got it to work using LARGE, and thank you everyone else who took the trouble to reply, there is obviously more than one way of solving this problem, some of them will require a little studying on the part of this novice.
Best regards to all.
Jimbow.

Replies
7
Views
240
Replies
3
Views
117
Replies
1
Views
392
Replies
5
Views
697
Replies
3
Views
555

1,203,171
Messages
6,053,885
Members
444,692
Latest member
Queendom

### 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