×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Best Three Averages
2

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!

http://excelspreadsheetshelp.blogspot.com - http://scripting4v5.com

RE: Best Three Averages

Look into how to use Pivot Tables.

RE: Best Three Averages

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/

RE: Best Three Averages

(OP)
Thanks for the help and the excellent write up!

RE: Best Three Averages

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources