Need Formula for Top 3 (or maybe 5) of a ranked array
Is it possible to return the value in the "Rows" column below for the top 3 numbers in the array?
So I would want to return "Data Quality, Governance & Reporting", "Talent, Skills Availability" and "Configurability vs Programming" from the array below.
In a sheet I have 6 people ranking the items in the Rows column 110. I then do a pivot to count the number of times each row is ranked 110.
I would like to easily see what my top 3 (or maybe 5) are.
If there is a better way to do this, I would love to be enlightened.
Andrea Zenner
Program Manager  Infrastructure & Operations
Apogee Enterprises
EAP  Mobilizer  Core Product Certified  Superstar
azenner@apog.com
Best Answer

If I'm understanding you correctly, you may actually want to filter by what has the highest rank value across the selections, versus the highest numbers.
For example, "Talent, Skills Availability" is selected twice, but for a rank of 6, whereas "Ease of Use" is selected for both 2 and 3, which I presume would give it a higher weight than 6. Would this be closer to the values you're looking to return?
If so, I would suggest adding in a helper column to identify a weight with each of the selections. For example, if "Ranked 1" is selected, this could be multiplied by 10x.
In my sheet I used this formula to assign a descending weight to the values in each row:
=SUM([Ranked 1]@row * 10, [Ranked 2]@row * 9, [Ranked 3]@row * 8, [Ranked 4]@row * 7, [Ranked 5]@row * 6, [Ranked 6]@row * 5, [Ranked 7]@row * 4, [Ranked 8]@row * 3, [Ranked 9]@row * 2, [Ranked 10]@row)
Then I can use a RANKEQ formula to find the top 3 weighted rows, like so:
=IF(RANKEQ(WEIGHT@row, WEIGHT:WEIGHT) <= 3, 1, 0)
Then you can use the Rank column (in my sheet I formatted it as a Star, but you could also use another symbol or just return the order) and either put that in a Report or simply filter the sheet.
Let me know if this makes sense or if I've misunderstood what you're looking to do!
Cheers,
Genevieve
Answers

If I'm understanding you correctly, you may actually want to filter by what has the highest rank value across the selections, versus the highest numbers.
For example, "Talent, Skills Availability" is selected twice, but for a rank of 6, whereas "Ease of Use" is selected for both 2 and 3, which I presume would give it a higher weight than 6. Would this be closer to the values you're looking to return?
If so, I would suggest adding in a helper column to identify a weight with each of the selections. For example, if "Ranked 1" is selected, this could be multiplied by 10x.
In my sheet I used this formula to assign a descending weight to the values in each row:
=SUM([Ranked 1]@row * 10, [Ranked 2]@row * 9, [Ranked 3]@row * 8, [Ranked 4]@row * 7, [Ranked 5]@row * 6, [Ranked 6]@row * 5, [Ranked 7]@row * 4, [Ranked 8]@row * 3, [Ranked 9]@row * 2, [Ranked 10]@row)
Then I can use a RANKEQ formula to find the top 3 weighted rows, like so:
=IF(RANKEQ(WEIGHT@row, WEIGHT:WEIGHT) <= 3, 1, 0)
Then you can use the Rank column (in my sheet I formatted it as a Star, but you could also use another symbol or just return the order) and either put that in a Report or simply filter the sheet.
Let me know if this makes sense or if I've misunderstood what you're looking to do!
Cheers,
Genevieve

@Genevieve P. That worked perfectly! Thank you!
Andrea Zenner
Program Manager  Infrastructure & Operations
Apogee Enterprises
EAP  Mobilizer  Core Product Certified  Superstar
azenner@apog.com

No problem at all! 🙂
If you want to adjust the weight (for example if you want Ranked 2 and Ranked 3 to be the same weight) then you just need to adjust what you're multiplying by in the first formula.
=SUM([Ranked 1]@row * 10, [Ranked 2]@row * 9, [Ranked 3]@row * 9, ... etc
I did it from 1  10 but you can customize this as needed!
Cheers,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!