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: info@sspmconsultants.com
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: info@sspmconsultants.com
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: info@sspmconsultants.com
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: info@sspmconsultants.com
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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!