Highlighted
New Contributor

# nth Largest value with conditions

Hi. I'm working with a data set from a game that contains player ability ratings, names, positions and a couple other values as well. I am able to get the nth values for each team, but would like to add a condition so that some positions don't appear too often when getting them, so it better reflects how a team would choose their team for a game. For example, the 20 largest values (rating) with a maximum of three players who play goalie. Thanks

7 Replies
Highlighted

# Re: nth Largest value with conditions

Please attach your sample file with manually entered results.
Highlighted

Highlighted

# Re: nth Largest value with conditions

Hi

Some time ago I created a tutorial about finding the Nth. Highest or lowest value and be able to change the number with a spin button on the fly.

here is the link to my tutorial:

https://www.youtube.com/watch?v=CQfWGB8z5EQ

Hope that helps

Nabil Mourad

The Power of Excel comes from combining functionality. In this tutorial we'll combine the VLookup, Large, Index and Match functions to extract the Second, Th...
Highlighted

# Re: nth Largest value with conditions

I cannot understand this part:

"For example, the 20 largest values (rating) with a maximum of three players who play goalie."

Please clarify by manually entering your desired results so that I can decide on the appropriate formula for you to use.

Highlighted

# Re: nth Largest value with conditions

@Twifoo Here's an updated version with the conditions I'd like to apply and two fake teams who violate the parameters

Highlighted

# Re: nth Largest value with conditions

I presume you want to return the results of Columns AS:AY. What's the logic for 80, 78, and 20 in AS4, AT4, and AX5, respectively?

Highlighted

# Re: nth Largest value with conditions

@Twifoo

Yes, I would like to return those values if the conditions are violated in the top 20. Those values are less than the 20th place and wouldn't show just based on a LARGE formula