×
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

finds 2nd largest in one column and returns from different column but uses tie breakers?

finds 2nd largest in one column and returns from different column but uses tie breakers?

finds 2nd largest in one column and returns from different column but uses tie breakers?

(OP)
I was trying to figure this out at home over the weekend. Every formula I tried to write would repeat the same name for the 2nd 3rd and 4th highest value due to the tie at 2nd highest. How do I write a formula that lists them due to tie breakers?

So in my list below, it should return...
pat
adam
mark
lucas
jake
tom

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

Hi,

You should always post an example that can you can COPY n PASTE. Otherwise, people won’t bother to reconstruct your picture.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

(OP)
I attached it as well as copied and pasted it in the body to be copied and pasted to any excel sheet.

I didnt think about doing that as I dont have the original at work and I made this one so quick just for the image example. lol


tom 10 1300 Looks up largest value in C and returns same row in column A
bob 6 1200 Looks up 2nd largest value in C and returns same row in column A
pat 4 2700 Looks up 3rd largest value in C and returns same row in column A
mark 8 1800 Looks up 4th largest value in C and returns same row in column A
adam 12 1800 Looks up 5th largest value in C and returns same row in column A
jake 3 1400
lucas 7 1800 Tie Breaker would be highest value in B

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

The simplest way would be to SORT your table...
pat	4	2700
adam	12	1800
mark	8	1800
lucas	7	1800
jake	3	1400
tom	10	1300
bob	6	1200
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

(OP)
The problem is I am looking to use the formula on another sheet to extract the top players in a category. So on a different sheet, I need it to give me the ten players who have had the biggest stats according to which category I select and if there is a tie, I need it to list them both or all three if there are three who tie.

I found something that is giving me ideas how to work it. Using INDEX, MAX, LARGE and possibly using a countif to break the ties. The countif would be an additional column I would add that each cell has an expanding range to only count those above it and not below it. Not sure exactly how to make it work yet but its forming. lol

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

Use INDEX() with RANK.EQ()

For your tiebreaker, simply add your base rank and your tiebreaker criteria and work with the sum. You might need to multiply or divide one or the other by 10 or 100.

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

Again, if your table were sorted then the first 10 are a simple INDEX(); third arg 1-10 for the 10 formulas.

=INDEX(A:A,1,1)
=INDEX(A:A,2,1)
...
=INDEX(A:A,10,1)


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

(OP)
To be honest, I was confused on the use of Rank.EQ in this case. But the idea I had seems to work out. I had to add a column "Q" that used a countif and if the return was larger than 1, it took his attempts (column E) * .00001 and subtracted it from the yards in column G. This changes the number by a minute amount to give each one a different number and then the INDEX MATCH LARGE formula did its job.

The new file is attached with a better look at it. You probably see an easier way but unfortunately sorting the stats would cause too much a hassle for each category.

RE: finds 2nd largest in one column and returns from different column but uses tie breakers?

thumbsup2

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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! Already a Member? Login


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close