Formula needed please

[Jimbow]

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.

 

[Iridium]

Check out the LARGE function in the xl help

HTH

 

[Yogi Anand]

Hi Jim:

Welcome to MrExcelBoard:

Building on sugestion by iridium, here is an illustration ...

Book2
	A	B	C	D	E
1	5	3	7	6	4
2
3		DiscardLargest
4		From	To
5		1	2
6		13
7
8		SumOfRemainingRaces
9		12
10
Sheet1

 

[Brian from Maui]

Or using SMALL sum the nth number of races,

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

 

[Iridium]

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!

 

[Aladin Akyurek]

SumIf ExactlyLargestN jimbow.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1							Discard
2	20	25	32	17	20		2	3	20	77	77
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.

 

[Jimbow]

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.