Help creating formula for project prioratisation
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!
Best Answer

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
Email ID: [email protected]
Did I answer to your question or fix the problem? Please
help
theSmartsheet 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

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
Email ID: [email protected]
Did I answer to your question or fix the problem? Please
help
theSmartsheet 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! 
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)]

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
Email ID: [email protected]
Did I answer to your question or fix the problem? Please
help
theSmartsheet 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! 
really appreciate the guidance @Khasim !
I've changed the sequence a little bit but was able to get it to work! much appreciated
cheers

Happy to help you @Sid25 😊
Thanks & Regards
Email ID: [email protected]
Did I answer to your question or fix the problem? Please
help
theSmartsheet 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
Categories
Check out the Formula Handbook template!