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>

 

[dyanblak]

Thanks JoeMo - it will be something I am doing wrong this end, for sure! Thanks anyway - as you may have seen I have adapted the sample spreadsheet that Aladin linked me to and it has done the trick. Thanks for all your help. I am really impressed with your knowledge