CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Ryan Morin
Ryan Morin

Reputation: 3

Largest value with conditions using Google Sheets

I'm currently using google sheets as a publicly viewable, stats tracking document for an esports league. We have a proprietary formula that calculates something called a "role rating" which gives a hard number that allows players in a specific role to compare their level of play to another player. I wanted to display the top 5 role rating scores for each role and then use the index function to spit out the name so that whenever I upload new stats the list would automatically update with the top 5 players. So that's the backstory and here is the issue:

Formula used and displayed error

Tab with stats and columns I'm referencing

Now if I'm not mistaken, the formula has to be an array formula and it correctly just says "Find the largest value in D:D if the value in B:B says "top"." Is there something simple I am missing?

Any assistance would be greatly appreciated!

Upvotes: 0

Views: 371

Answers (2)

Harun24hr
Harun24hr

Reputation: 36760

Query() function will give you best result in this case. Try below query formula. You may need to adjust formula for other columns for your sheet.

=QUERY(B2:D,"Select D where B='Top' Order by D Desc Limit 5")

FILTER() function will also work like-

=SORTN(FILTER(D2:D20,B2:B20="Top"),5,,1,FALSE)

enter image description here

Upvotes: 1

Kin Siang
Kin Siang

Reputation: 2699

Suppose your data starting on row 2, and you would like to display the result starting from row 2 also, your calculation display error because the order of the Large function, then you might use the following formula to correct it, accept if helping :)

=large(ARRAYFORMULA(IF(B:B="Top",D:D,"")),row(F2)-1)

enter image description here

Upvotes: 0

Related Questions