Scoring Matrix Formula

younge · 05/23/23

I'm looking for a formula to act as an automatic scoring matrix. The first part of the score is determined by a dropdown of 3 values (met = 0, not met = -1, and n/a = blank). The next and final part of the score is determined by a symbol color (green, red, and yellow). See image for visuals.

Paul Newcome · 05/23/23

You would use something like this:

=IF(Condition@row = "NOT MET", -1, 0) - IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

Paul Newcome · 05/23/23

So to make sure I understand...

If it is "N/A", then there should be no score at all, if it is "MET" then the score should be zero, otherwise follow the scoring chart?

=IF(Condition@row <> "N/A", IF(Condition@row = "MET", 0, -1 -IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))))

Paul Newcome · 05/23/23

You would use something like this:

=IF(Condition@row = "NOT MET", -1, 0) - IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

Paul McGuinness · 05/23/23

Hi @younge

This should do what you require

=SUM(IF(Condition@row = "Met", 0, IF(Condition@row = "N/A", "", IF(Condition@row = "Not Met", -1, "Error"))), IF([Column2]@row = "Red", -5, IF([Column2]@row = "Yellow", -3, IF([Column2]@row = "Green", -1, "Error"))))

Tested as below and working

Hope that helps

Thanks

Paul

younge · 05/23/23

@Paul Newcome This works great thanks! I need a small change to the formula, however. I realized that I articulated the question slightly incorrect. If the condition = met then i would like the score to be 0 regardless of the color, otherwise the rest of the scoring will remain as described. Also this formula returns N/A as a score instead of leaving it blank (the picture was probably misleading).

Paul Newcome · 05/23/23

So to make sure I understand...

If it is "N/A", then there should be no score at all, if it is "MET" then the score should be zero, otherwise follow the scoring chart?

=IF(Condition@row <> "N/A", IF(Condition@row = "MET", 0, -1 -IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))))

younge · 05/23/23

https://community.smartsheet.com/discussion/comment/377136#Comment_377136

That's it! You're a Smartsheet angel @Paul Newcome

Paul Newcome · 05/23/23

Happy to help. 👍️

Scoring Matrix Formula

Best Answers

Answers

Help Article Resources

Categories