Help creating formula for project prioratisation

Sid25

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!

Khasim

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))))

Khasim

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?

Sid25

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

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))))

Sid25

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

Happy to help you @Sid25 😊