Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Best Three Averages 2

Status
Not open for further replies.
ntweisen

ntweisen

Mechanical
Joined
Jul 12, 2010
Messages
94
Location
US
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!


-
 
Sort by date Sort by votes
The formula below is adapted from an article on Chop Pearson's site: 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
 
Upvote 0 Downvote
Thanks for the help and the excellent write up!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

McDermott1711
  • Locked
  • Question Question
Best Problems Solved Using Excel Solver
Replies
7
Views
569
Denial
Denial
conveyorbud
  • Locked
  • Question Question
excel use three values to return one
Replies
6
Views
273
conveyorbud
conveyorbud
sfenili
  • Locked
  • Question Question
For the best only
Replies
6
Views
256
sfenili
sfenili
CWEngineer
  • Locked
  • Question Question
Determining the Minimum of Three Numbers
Replies
9
Views
237
CWEngineer
CWEngineer
mohtogh
  • Locked
  • Question Question
Three worksheet tied together while copying
Replies
2
Views
155
bltseattle
bltseattle

Part and Inventory Search

Sponsor

Back
Top