Excel Formulas - highest number 1

[luke99]

Hi, i am just trying to work out a formula that gives me 3 highest values from a range of data/values. It is probably simple but i can only think of MAX but that only gets me the highest. Anybody more advance? thanks for help.

 

[DaleWatson123321]

Hi Luke,

What you're after is:

=LARGE(range,1)
=LARGE(range,2)
=LARGE(range,3)

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[luke99]

thanks that is the thing. i have just tried it, one more thing. how do i link say the name of the scorer with the appropriate values. e.g let's say i want to have 3 winners out of 10 with the highest scored points, how do i keep the names of the winners with the 3 highest values? Thanks for this, you are just doing the work for me. reall appreciated. cheers

 

[SkipVought]

Hi luke,

Dale hasn't responded, so I'm gonna jump in.

Your Number Values need to be in the FIRST column of your data and then I am assuming that the NAME is in the second column (A1:B999)

Furthermore, I am assuming that your 3 =LARGE formulas are in D13

So this formula will be entered in E1 and be copied down to E3...

=VLOOKUP(D1,$A$1:$B$999,2,FALSE)

It also assumed that you have 999 rows in your table. Modify the formula accordingly.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Just to throw in some tweaks.

To get the 3 largest, and make it harder to screw up any of the formulas by accidentally overwriting one, you can select say the range D13, type =LARGE(B1:B30,{1;2;3}) and array enter using CTRL+SHIFT+ENTER, which will put in the top 3 values.

As for the names, if you would rather not have them be on the right hand side of the numbers, then you can leave them on the left but use the INDEX function instead. So, with your 3 large numbers in D13, Names in A1:A30 and list of numbers in B1:B30, in E1 say put:-

=INDEX($A$1:$A$30,MATCH(D1,$B$1:$B$30,0))

and copy down.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[luke99]

Well done guys!!! A*

that is great response, i love this site. I don't use it that often, but if i get stuck. i know where the help is. So much for the great Microsoft Help. ;o))

Thanks again. All done for now.

Lukas