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


Andrea Zenner

Program Manager | Infrastructure & Operations

Apogee Enterprises

EAP | Mobilizer | Core Product Certified | Superstar

azenner@apog.com

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Andrea Zenner ✭✭✭✭✭

    @Genevieve P. That worked perfectly! Thank you!

    Andrea Zenner

    Program Manager | Infrastructure & Operations

    Apogee Enterprises

    EAP | Mobilizer | Core Product Certified | Superstar

    azenner@apog.com

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!