Score Formulas

Options

I need help with a score formula. We are having a sales contest, 20 teams (Houses) to compete for 1st, 2nd and 3rd place.

I was able to use the MAX function to determine the highest score [ =MAX({total score}, {house}, [House Name]@row) ] and JOIN function for the name of the team with the highest score [ =JOIN(COLLECT([House Name]:[House Name], [Oct Team Score]:[Oct Team Score], MAX([Oct Team Score]:[Oct Team Score], " ")))) ].

I just can't get to identify the 2nd and 3rd place Houses score results.

Please help me!

Thanks!

Marilu

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Score

    I hope you're well and safe!

    Try something like this.

    • Add a so-called helper column. You can name it Rank, and add the following formula to it, and fill it down the column.
    =RANKEQ([Oct Team Score]@row, [Oct Team Score]:[Oct Team Score])
    
    • The use this formula in the Top Team and modify for 2 place and so on.
    =INDEX([House Name]:[House Name], MATCH(1, Ranking:Ranking, 0))
    
    • Use the following formula to collect the scores.
    =INDEX([Oct Team Score]:[Oct Team Score], MATCH(1, Ranking:Ranking, 0))
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Marilu
    Options

    Hi Andre,

    I am doing well and thank you for your help!

    I added the helper column, that works fine, but the function to find the Top Team does not. Index function has this error "Unparseable", I believed that the Ranking:Ranking portion of the formula was the issue. So, I changed it to look at the Oct Team Score column but it gives me a #NO MATCH error.

    Thoughts?

    Thank you!! Hope you are well.

    Marilu

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Marilu

    Excellent!

    You're more than welcome!

    How did it go? Did you manage to get something set up?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!