# New score from two columns

Topic Labels: Formulas
Solved
1502 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi! I’m trying to create a new field on my table that is a combination of two different Single Select fields. One column is the Likelihood of an event happen (1- Rare, 2- Unlikely, 3- Possible, 4-Likely, 5- Almost certain) and the other column is the Impact of that event happening (1-Insignificant, 2-Minor, 3-Moderate, 4-Major, 5-Catastrophic). The new column that I want to create will give me a “score” from the combination of the Likelihood and the Impact (1- Low Risk, 2-Moderate Risk, 3-High Risk, 4- Critical Risk). So for example, if the event is Almost Certain (5) and but the consequences are 1- Insignificant that will mean 1-Low Risk. Could someone give me guidance on automatizing this score? Thanks!

1 Solution

Accepted Solutions
7 - App Architect

Interesting question - I’m working on a base for work with a similar scenario for determining risks associated with our tree inventory.

I think I’d figure out all the possible combinations, assign them a final risk, and use switch() to display the final score.

Concatenate(Likelihood,Impact) = Rare-Low Risk, Rare-Moderate Risk…

Then use switch() to display the final value:

switch(“Rare-Low Risk”,1, “Rare-Moderate-Risk”,2, etc.

Then I’d wrap it all up into one formula so you don’t need any helper fields:

SWITCH(CONCATENATE({Likelihood},{Impact}),“RareMedium”,2)

similar to what I did in the “Final Risk” column in this base:

6 Replies 6
10 - Mercury

Hi Elena. What popped into my mind quickly is this. I would make two tables, one that defines likelihood - name and “value” (probably between 0-1), and the same for impact (name and score). Then change your two columns to linked fields, the likelihood and impact tables. Then, add a lookup field using the linked column to get the numerical value associated with the primary key you selected in the linked field.

Once you have those values, you can multiply or whatever to create a combined score. There are benefits to doing it this way along with the downsides of added tables and columns (tho you can hide some of these).

Hope this helps.

7 - App Architect

Interesting question - I’m working on a base for work with a similar scenario for determining risks associated with our tree inventory.

I think I’d figure out all the possible combinations, assign them a final risk, and use switch() to display the final score.

Concatenate(Likelihood,Impact) = Rare-Low Risk, Rare-Moderate Risk…

Then use switch() to display the final value:

switch(“Rare-Low Risk”,1, “Rare-Moderate-Risk”,2, etc.

Then I’d wrap it all up into one formula so you don’t need any helper fields:

SWITCH(CONCATENATE({Likelihood},{Impact}),“RareMedium”,2)

similar to what I did in the “Final Risk” column in this base:

5 - Automation Enthusiast

Thanks Tim! It worked out!!! Another tricky question. Once we have the score, could we somehow add a color to each score? Imagine, 1-Green, 2-Yellow, 3-Orange, 4-Red? That would be Superb!!!

5 - Automation Enthusiast

Thanks Augmented! I used Tim’s tip and it worked out in the same table. Much appreciated!

7 - App Architect

If you have a pro version you can color code the rows. Unfortunately, you cannot color code the actual result. (The green word PLOW you see in my image is a Single Select field where I used colors.)

5 - Automation Enthusiast

You are a Pro!!! :star_struck: I was a Free user for some time and now that I’ve upgraded to Pro I feel like I have a lot to learn!! Many Thanks!!!