New score from two columns

[Elena_Lopez]

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!

[augmented]

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.

[Tim_Sullivan · Accepted solution]

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:

Airtable

Concat & Switch - Airtable

Explore the "Concat & Switch" base on Airtable.

[Elena_Lopez]

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!!!

[Elena_Lopez]

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

[Tim_Sullivan]

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.)

[Elena_Lopez]

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!!!