Smartsheet formula for counting different numbers in different columns and "scoring" it
So I have a bit of a convoluted one …
We are autoscoring our risks on a monthly basis to report in to Directors and Sponsors. I have managed to write the formula to count the number of red/amber/risks. I am now hoping to take it one step further and score it "1", "2", or "3" depending whether there's more red/amber/red risk. Example below:
Project # | No. of red risks | No. of amber risks | No. of green risks | Risk score |
---|---|---|---|---|
Project1 | 2 | 1 | 7 | |
Project2 | 0 | 2 | 0 | |
Project3 | 5 | 1 | 1 | |
So the formula would have to look at each column and, for Project 1, score "3" as there's mainly green, for Project 2, score "2" as there is mainly amber and for Project 3, score it "1" as there's mainly red.
Is there an easy way to do this?
Many thanks
Mel
Answers
-
Hello @Mel_Barnes - Not exactly what you asked for but could you consider weighting red risks with a value of 5, amber risks with a value of 3 and green risks with a value of 1. You could then multiple the number of risks by their weighting to give an overall risk rating with the formula below to show which projects have an overall higher risk score.
=([No. of Red Risks]@row * 5) + ([No. of Amber Risks]@row * 3) + ([No. of Green Risks]@row * 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!