Help creating formula for project prioratisation

Options

Hi there

Im fairly new to Smartsheet and was looking for some help creating a formula to help populate the score for each project within the team, to then help prioratise each project.

Have found a useful Smartsheet excel template and corresponding formula but cant seem to apply that to Smartsheet.

Below is the criteria weight/score we're using - would be really great if anyone can tell me what the formula should be to identify value score automatically?

thanks!


Tags:

Best Answer

  • Khasim
    Khasim ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sid25

    The formula below will help you in calculating the Values Score for all projects automatically.


    Sorry for the incorrect sequence; you can change it in the formula from o to High.

    Formula: =IF([Column8]@row = "Med", 0.6 * 20, IF([Column8]@row = "High", 1 * 20, IF([Column8]@row = "Low", 0.1 * 20, IF([Column8]@row = 0, 0 * 20)))) + IF([Column9]@row = "Med", 0.6 * 20, IF([Column9]@row = "High", 1 * 20, IF([Column9]@row = "Low", 0.1 * 20, IF([Column9]@row = 0, 0 * 20)))) + IF([Column10]@row = "Med", 0.6 * 20, IF([Column10]@row = "High", 1 * 20, IF([Column10]@row = "Low", 0.1 * 20, IF([Column10]@row = 0, 0 * 20)))) + IF([Column11]@row = "Med", 0.6 * 40, IF([Column11]@row = "High", 1 * 40, IF([Column11]@row = "Low", 0.1 * 40, IF([Column11]@row = 0, 0 * 40))))


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: info@sspmconsultants.com

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

Answers

  • Khasim
    Khasim ✭✭✭✭✭
    Options

    Hi @Sid25

    We can use the If condition to accomplish this, but we need more clarity on the problem statement. Can you provide an example?


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: info@sspmconsultants.com

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Sid25
    Sid25 ✭✭
    Options

    Hi @Khasim thanks for the response

    I've added the project column for more clarity - what I need is to automatically calculate the relevant value each criteria and then the sum total to pick which project gets the highest value score.

    Example of manual calculation:

    Project 1 Value Score: [(Customer impact = 60% x 20) + (Commercial impact = 100% x 20) + (Operational impact = 0% x 20) + (Compliance impact = 10%x40)]


  • Khasim
    Khasim ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sid25

    The formula below will help you in calculating the Values Score for all projects automatically.


    Sorry for the incorrect sequence; you can change it in the formula from o to High.

    Formula: =IF([Column8]@row = "Med", 0.6 * 20, IF([Column8]@row = "High", 1 * 20, IF([Column8]@row = "Low", 0.1 * 20, IF([Column8]@row = 0, 0 * 20)))) + IF([Column9]@row = "Med", 0.6 * 20, IF([Column9]@row = "High", 1 * 20, IF([Column9]@row = "Low", 0.1 * 20, IF([Column9]@row = 0, 0 * 20)))) + IF([Column10]@row = "Med", 0.6 * 20, IF([Column10]@row = "High", 1 * 20, IF([Column10]@row = "Low", 0.1 * 20, IF([Column10]@row = 0, 0 * 20)))) + IF([Column11]@row = "Med", 0.6 * 40, IF([Column11]@row = "High", 1 * 40, IF([Column11]@row = "Low", 0.1 * 40, IF([Column11]@row = 0, 0 * 40))))


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: info@sspmconsultants.com

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • Sid25
    Sid25 ✭✭
    Options

    really appreciate the guidance @Khasim !

    I've changed the sequence a little bit but was able to get it to work! much appreciated

    cheers

  • Khasim
    Khasim ✭✭✭✭✭
    Options

    Happy to help you @Sid25 😊


    Thanks & Regards

    Khasim

    SSPM Consultants

    Email ID: info@sspmconsultants.com

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!