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

@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

Highlighted

@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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies