# 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:

• ✭✭✭✭✭
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!

• ✭✭✭✭✭
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!

• ✭✭
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)]

• ✭✭✭✭✭
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!

• ✭✭
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

• ✭✭✭✭✭
Options

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!