Need Formula for Top 3 (or maybe 5) of a ranked array

Andrea Zenner

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 1-10. I then do a pivot to count the number of times each row is ranked 1-10.

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.

Genevieve P.

Hi @Andrea Zenner

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.

Hi @Andrea Zenner

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

Andrea Zenner

@Genevieve P. That worked perfectly! Thank you!

Genevieve P.

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