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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!