Scoring Matrix Formula
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.
Best Answers

You would use something like this:
=IF(Condition@row = "NOT MET", 1, 0)  IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

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

You would use something like this:
=IF(Condition@row = "NOT MET", 1, 0)  IF([Column2]@row = "Red", 5, IF([Column2]@row = "Yellow", 3, 1))

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

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

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

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!