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
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!