Best Three Averages
Best Three Averages
(OP)
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!
Thanks for your help!
http://excelspreadsheetshelp.blogspot.com - http://scripting4v5.com
RE: Best Three Averages
RE: Best Three Averages
http://www.get-digital-help.com/2009/10/25/how-to-...
RE: Best Three Averages
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/
RE: Best Three Averages
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Best Three Averages
RE: Best Three Averages
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/