• 461K Members
• 18.6K Online
• 558K Conversations

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

# 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:

Hope that helps

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, Third, Fourth...etc Highest Value ina list. We'll be highlighting records using conditional formatting and then add a magical spin button that

# 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.

# 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?

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies