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

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!