Best Three Averages 2

[ntweisen]

In sheet one in column A I have a list of 2000 names (some of them repeated) and column B lists each person's score percentage. In sheet 2, in column A I list each person's name exactly once and in columns b, c, d I want to list their first, second, and third best percentage. I know I can use the LARGE function to get a best score from a list but I am not sure how to make it so the function only appilies to the individual listed in sheet 2 column A. Does that make sense?

Thanks for your help!

http://excelspreadsheetshelp.blogspot.com

-

http://scripting4v5.com

 

[MintJulep]

Look into how to use Pivot Tables.

 

[LiteYear]

You could also use array values to return you an array of matches to a lookup, then perform a LARGE on that.

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

 

[IDS]

The formula below is adapted from an article on Chop Pearson's site:

http://www.cpearson.com/excel/ArrayFormulas.aspx

and is a bit neater than given in LiteYear's link.

If the names are in B6:B105 the scores are in C6:C105 and a selected name is in E6 then:
(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE)
will return an array of 100 items containing either a score for the selected name or FALSE.
You can then wrap the Large() function around that:
=LARGE(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE),F$5)
where F5 contains the rank you want.

Note that because this is an array formula it must be entered by pressing "Ctrl-Shift-Enter". If you just press "enter" it returns 0.

A sample spreadsheet is attached.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

More details at

http://newtonexcelbach.wordpress.com/2012/06/16/extracting-selected-data-with-array-functions/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[ntweisen]

Thanks for the help and the excellent write up!

 

[IDS]

ntweisen - Glad to help. I've added a bit more to the blog post in response to a comment, that may be of interest.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/