To calculate person with 5 best marks out of possible 7

[dyanblak]

Hi there,
I wonder whether anybody can help me with this? I need to calculate a winner for a trophy and it is the person who has the best score for 5 out of up to 8 marks. I have sorted the table by competitor name for a start and then sorted the marks from highest to lowest, but now I am stuck and will have to delete the remaining lower marks for each competitor to find the winner. Is there a formula that I can use to save lots of time? Fingers crossed

eg the table starts like this:

Class Competitor Marks Greek Abbie Harrop 80 Character Abbie Harrop 79 Modern Abbie Harrop 77 Modern Adam Abraham 87 Song and Dance Adam Abraham 86 Ballet Adam Abraham 80 Tap Adam Abraham 77 Character Amy Abbott 85 National Amy Abbott 84 Greek Amy Abbott 83 Tap Amy Abbott 83 Modern Amy Abbott 83 Song and Dance Amy Abbott 83 Ballet Amy Abbott 78 gymnastic dance Aisha Stalker 88 Character Aisha Stalker 87 National Aisha Stalker 87 Modern Aisha Stalker 86 Tap Aisha Stalker 86 Song and Dance Aisha Stalker 85 Greek Aisha Stalker 84 Ballet Aisha Stalker 83 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[JoeMo]

I'm not sure what you want done with competitors who have less than 5 marks. For those I simply summed all their marks. Column D is a "helper" column that you can hide if you want to.

Excel Workbook
	A	B	C	D	E	F	G
1	Class	Competitor	Marks	TieBreak		Name	Best 5
2	Greek	Abbie Harrop	80	80		Abbie Harrop	236
3	Character	Abbie Harrop	79	79		Adam Abraham	330
4	Modern	Abbie Harrop	77	77		Amy Abbott	418
5	Modern	Adam Abraham	87	87		Aisha Stalker	434
6	Song and Dance	Adam Abraham	86	86
7	Ballet	Adam Abraham	80	80
8	Tap	Adam Abraham	77	77
9	Character	Amy Abbott	85	85
10	National	Amy Abbott	84	84
11	Greek	Amy Abbott	83	83
12	Tap	Amy Abbott	83	83
13	Modern	Amy Abbott	83	83
14	Song and Dance	Amy Abbott	83	83
15	Ballet	Amy Abbott	78	78
16	gymnastic dance	Aisha Stalker	88	88
17	Character	Aisha Stalker	87	87
18	National	Aisha Stalker	87	87
19	Modern	Aisha Stalker	86	86
20	Tap	Aisha Stalker	86	86
21	Song and Dance	Aisha Stalker	85	85
22	Greek	Aisha Stalker	84	84
23	Ballet	Aisha Stalker	83	83
Sheet6

 

[dyanblak]

Thanks JoeMo,
I am doing something wrong, I think, as I just get zeros in column G, and no names in column F. There are 526 rows in total on my spreadsheet by the way.

Competitor	Marks	Tie Break		NAME	Best 5
Abbie Harrop	80	80.00000002			0
Abbie Harrop	79	79.00000003			0
Abbie Harrop	77	77.00000004			0
Adam Abraham	87	87.00000005			0
Adam Abraham	86	86.00000006			0
Adam Abraham	80	80.00000007			0
Adam Abraham	77	77.00000008			0
Amy Abbott	85	85.00000009			0
Amy Abbott	84	84.0000001			0
Amy Abbott	83	83.00000011			0
Amy Abbott	83	83.00000012			0
Amy Abbott	83	83.00000013			0
Amy Abbott	83	83.00000014			0
Amy Abbott	78	78.00000015			0
Aisha Stalker	88	88.00000016			0
Aisha Stalker	87	87.00000017			0
Aisha Stalker	87	87.00000018			0
Aisha Stalker	86	86.00000019			0
Aisha Stalker	86	86.0000002			0
Aisha Stalker	85	85.00000021			0
Aisha Stalker	84	84.00000022			0
Aisha Stalker	83	83.00000023			0

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[JoeMo]

I put the names in column F manually. You could use advanced filter on column A to put a unique list there.

 

[dyanblak]

Thanks JoeMo, that's not a problem. Still getting '0' in column G though! Frustrating

 

[Aladin Akyurek]

A bit differently interpreted...

Class	Competitor	Marks			Competitor	The 5 Best Total	Best Competitor(s)
Greek	Abbie Harrop	80			Abbie Harrop		Amy Abbott
Character	Abbie Harrop	79			Adam Abraham
Modern	Abbie Harrop	77			Amy Abbott	501
Modern	Adam Abraham	87			Aisha Stalker	434
Song and Dance	Adam Abraham	86
Ballet	Adam Abraham	80
Tap	Adam Abraham	77
Character	Amy Abbott	85
National	Amy Abbott	84
Greek	Amy Abbott	83
Tap	Amy Abbott	83
Modern	Amy Abbott	83
Song and Dance	Amy Abbott	83
Ballet	Amy Abbott	78
gymnastic dance	Aisha Stalker	88
Character	Aisha Stalker	87
National	Aisha Stalker	87
Modern	Aisha Stalker	86
Tap	Aisha Stalker	86
Song and Dance	Aisha Stalker	85
Greek	Aisha Stalker	84
Ballet	Aisha Stalker	83

<COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4465" width=126><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4096" width=115><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1166" span=2 width=33><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6200" width=174><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6656" width=187><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4977" width=140><TBODY>
</TBODY>

F2, control+shift+enter (CSE), not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$23,SMALL(IF(FREQUENCY(IF($B$2:$B$23<>"",
  MATCH($B$2:$B$23,$B$2:$B$23,0)),Ivec),Ivec),ROWS($F$2:F2))),"")

Ivec is defined using Formulas | Name Manager as referring to:

=ROW(Sheet1!$A$2:$A$23)-ROW(Sheet1!$A$2)+1

G2, CSE and copy down:

=IFERROR(SUM(IF($B$2:$B$23=$F2,
  IF($C$2:$C$23>=LARGE(IF($B$2:$B$23=$F2,$C$2:$C$23),5),
  $C$2:$C$23))),"")

H2,CSE and copy down:

=IFERROR(INDEX($F$2:$F$23,SMALL(IF($G$2:$G$23=MAX($G$2:$G$23),
  ROW($F$2:$F$23)-ROW($F$2)+1),ROWS($H$2:H2))),"")

 

[dyanblak]

Hi Aladin,
Thanks for this. I have copied and pasted the formulae as you suggest (I have amended all the refs from 23 to 526 as this is how many rows there actually are in the real spreadsheet). I am not sure about what the IVEC code is - I haven't come across this before, so am not sure where to put it. I notice that your result gives Amy Abbot as the winner, but my manual calculations would suggest that she has a total of 418 for her best 5 marks, whereas Aisha has 434. I wonder whether I have not described the desired outcome well enough? I need to find the winner who has the highest combined marks for their best five dances, if this is possible? Hope you can help.

 

[Aladin Akyurek]

Hi Aladin,
Thanks for this. I have copied and pasted the formulae as you suggest (I have amended all the refs from 23 to 526 as this is how many rows there actually are in the real spreadsheet). I am not sure about what the IVEC code is - I haven't come across this before, so am not sure where to put it. I notice that your result gives Amy Abbot as the winner, but my manual calculations would suggest that she has a total of 418 for her best 5 marks, whereas Aisha has 434. I wonder whether I have not described the desired outcome well enough? I need to find the winner who has the highest combined marks for their best five dances, if this is possible? Hope you can help.

Nothing wrong with your description. Also, you are right about Aisha.

We need to modify the formula in G2...

Control+shift+enter, not just enter:

=SUM(LARGE(IF($B$2:$B$23=$F2,$C$2:$C$23),
  ROW(INDIRECT("1:"&MIN(5,COUNT(IF($B$2:$B$23=$F2,$C$2:$C$23)))))))

Ivec, as I described in my original post, is a defined name.
Assuming that the data is on Sheet1, activate the Formulas | Name Manager, enter Ivec as name, and enter the following in the Refers to box:

=ROW(Sheet1!$A$2:$A$23)-ROW(Sheet1!$A$2)+1

Here is the exhibit that obtains after the modification of the formula in G2 (partially shown)...

Class	Competitor	Marks			Competitor	The 5 Best Total	Best Competitor
Greek	Abbie Harrop	80			Abbie Harrop	236	Aisha Stalker
Character	Abbie Harrop	79			Adam Abraham	330
Modern	Abbie Harrop	77			Amy Abbott	418
Modern	Adam Abraham	87			Aisha Stalker	434
Song and Dance	Adam Abraham	86

<COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4465" width=126><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4096" width=115><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1166" span=2 width=33><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6200" width=174><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6656" width=187><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 4977" width=140><TBODY>
</TBODY>

Here is a workbook that shows all of it:
https://dl.dropboxusercontent.com/u/65698317/dyanblak%20Top%20Performers.xlsx

 

[JoeMo]

Thanks JoeMo, that's not a problem. Still getting '0' in column G though! Frustrating

Sorry, I don't understand why you are getting zeroes if your data layout is identical to what I posted. I'm relieved to see that Aladin and I agree on the winner after his post #8.

 

[dyanblak]

Wow, thanks Aladin. I have adapted your spreadsheet example to fit my whole big spreadsheet and it has come up with the correct answer. This will save me so much time at the dance festival that I organise. Up until now I have had to shut myself in a quiet room for half an hour to come up with the result. You are a star! Thank you so much