Find multiple maxes on single column and match them with names

[Mpho]

Hi there.

I am new member. I have difficulties with finding Max for top ten learners out of 200 learners. In column A I have names and surnames, column B gender, column C year of birth and column D - Q I have subjects and column R I have average marks. Therefore I want to use column R to find top ten learners in order match the marks with their names. I have no clue I could only find the first Max, then I need to find the next Max but exclude the first Max.

Kind regards


Mpho.

 

[jasonb75]

MAX is just the absolute largest value in the array, for second, third or fourth largest numbers you would need to use the LARGE function, for example.

=LARGE($R$2:$R$200,2)

will find the second largest number in the range specified.
Instead of changing the number at the end each time, you could use something like

=LARGE($R$2:$R$200,SEQUENCE(10))

to create a dynamic array for the top 10. As you have listed 5 different versions of excel on your profile, I have based this on the newest one, Office 365. The SEQUENCE function does not work with older versions.