Home

nth Largest value with conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-776306%22%20slang%3D%22en-US%22%3Enth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776306%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I'm%20working%20with%20a%20data%20set%20from%20a%20game%20that%20contains%20player%20ability%20ratings%2C%20names%2C%20positions%20and%20a%20couple%20other%20values%20as%20well.%20I%20am%20able%20to%20get%20the%20nth%20values%20for%20each%20team%2C%20but%20would%20like%20to%20add%20a%20condition%20so%20that%20some%20positions%20don't%20appear%20too%20often%20when%20getting%20them%2C%20so%20it%20better%20reflects%20how%20a%20team%20would%20choose%20their%20team%20for%20a%20game.%20For%20example%2C%20the%2020%20largest%20values%20(rating)%20with%20a%20maximum%20of%20three%20players%20who%20play%20goalie.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-776306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776374%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776374%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file%20with%20manually%20entered%20results.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776475%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776501%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383208%22%20target%3D%22_blank%22%3E%40sportsdude413%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3ESome%20time%20ago%20I%20created%20a%20tutorial%20about%20finding%20the%20Nth.%20Highest%20or%20lowest%20value%20and%20be%20able%20to%20change%20the%20number%20with%20a%20spin%20button%20on%20the%20fly.%3C%2FP%3E%3CP%3Ehere%20is%20the%20link%20to%20my%20tutorial%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DCQfWGB8z5EQ%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DCQfWGB8z5EQ%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776914%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383208%22%20target%3D%22_blank%22%3E%40sportsdude413%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20understand%20this%20part%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%3CSTRONG%3EFor%20example%2C%20the%2020%20largest%20values%20(rating)%20with%20a%20maximum%20of%20three%20players%20who%20play%20goalie.%3C%2FSTRONG%3E%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20clarify%20by%20manually%20entering%20your%20desired%20results%20so%20that%20I%20can%20decide%20on%20the%20appropriate%20formula%20for%20you%20to%20use.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780061%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BHere's%20an%20updated%20version%20with%20the%20conditions%20I'd%20like%20to%20apply%20and%20two%20fake%20teams%20who%20violate%20the%20parameters%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780799%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383208%22%20target%3D%22_blank%22%3E%40sportsdude413%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20presume%20you%20want%20to%20return%20the%20results%20of%20Columns%20AS%3AAY.%20What's%20the%20logic%20for%2080%2C%2078%2C%20and%2020%20in%20AS4%2C%20AT4%2C%20and%20AX5%2C%20respectively%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782617%22%20slang%3D%22en-US%22%3ERe%3A%20nth%20Largest%20value%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3CP%3EYes%2C%20I%20would%20like%20to%20return%20those%20values%20if%20the%20conditions%20are%20violated%20in%20the%20top%2020.%20Those%20values%20are%20less%20than%20the%2020th%20place%20and%20wouldn't%20show%20just%20based%20on%20a%20LARGE%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E
sportsdude413
New Contributor

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
Please attach your sample file with manually entered results.

@sportsdude413 

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

@sportsdude413 

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.

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

@sportsdude413 

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?

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies