LinkBack URL
About LinkBacks
Good evening. Please, I need some help.
How can I extract the Top 5 values from each group? I want to figure out who are the Top 5 states from each heat based on the number of points.
Good evening. Please, I need some help.
How can I extract the Top 5 values from each group? I want to figure out who are the Top 5 states from each heat based on the number of points.
Which version(s) of Excel will this need to work on? Your profile, as it stands, is not too helpful.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I am using Excel 2021, thank you.
OK - I think this will work in 2021. In J4 copied down:
=FILTER($B$2:$B$57,($E$2:$E$57=$I$2)*($G$2:$G$57=$I4))
Adjust for each heat.
There is a tie for 5th place in Heat 5. How can I fix this so it leaves with only one value? I made this formula to count how many times it occur: =COUNTIFS($E$2:$E$57,E2,$F$2:$F$57,">"&F2)+1
What do you mean by 'only one value'? Not clear (to me).
I believe the fix for this if you don't care about tie breaking criteria is to enter ALI's formula above as an array formula using CTRL + SHIFT + ENTER. If done so correctly this will add { } around your formula.
This will remove the multiple entries for 5th place as it will only show the first one.
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
Another option iswhich will return 5 rows. If your data is always sorted descending on the points column, there is no need for the sortFormula:
Please Login or Register to view this content.
How can I extract the top value for each region? I made another worksheet titled "Ranking by Region" and when I try the XLOOKUP function, it returned the #VALUE! error.
Pleae update your forum profile to include ONLY the version of Excel that you are using - thanks.
Neither of us gave you an XLOOKUP formula ...
A minor tweak of my formula above should give what you want - did you try it?
=FILTER($B$2:$B$57,($E$2:$E$57=$J3)*($G$2:$G$57=1))
You may need to enter it as an array (CSE) before copying down.
Yes, it worked.
Glad to have helped.
If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
And update your forum profile as requested, please.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks