Formulas in Microsoft Lists

Hello @Ashleigh Milburn, 

Good day! I hope you are doing well. 

You can use Calculated Columns in Microsoft Lists to get the result you're looking for. 

A Calculated Column lets the list automatically work out a value based on other columns, for example, adding the numbers from two dropdowns and then showing a status like GREEN, AMBER, or RED. 

Step 1: Create your Choice columns 

1.Create the Impact column 

• At the top of your list, click Add column. 
• Select Choice. 
• Name the column Impact. 
• In the choices box, add the following options (one per line): 
  • 1- Insignificant 
  • 2- Minor 
  • 3- Moderate 
  • 4- Significant 
  • 5- Major 
• Click Save. 

2.Create the Likelihood column 

• Follow the steps above to add another choice column. In the choices box, add the following options (one per line):  
  • 1- Very Unlikely 
  • 2- Unlikely 
  • 3- Possible 
  • 4- Likely 
  • 5- Almost Certain 
• Click Save. 

Tip: Keeping the leading numbers in the choice text (as you’ve done) makes it easy to extract the numeric score. 

Step 2 : Create a Calculated Column to add the scores 

• At the top, click Add column > Scroll down and choose See all column types > Next. 
• In the new window: 
  • Column name: Score.  
  • Choose Column type: Calculated (calculation based on other columns). 
  • In the formula box, enter:  =VALUE(LEFT([Impact],1)) + VALUE(LEFT([Likelihood],1)) 
  • Set Data type returned to Number and Number of decimal places to "0". 
  • Click OK. 

This will automatically calculate a score between 2 and 10. 

Step 3 : Create a second Calculated Column for RAG status 

• At the top, click Add column > Scroll down and choose See all column types > Next.  
• In the new window:  
  • Name this one RAG Status or RAG. 
  • Column type: Calculated (calculation based on other columns).  
  • Use this formula: =IF([Score]<=4,"GREEN",IF([Score]<=7,"AMBER","RED")) 
  • Set Data type returned to Single line of text. 
• Click Save. 

Now the list will automatically show: 

• GREEN for scores 2-4 
• AMBER for scores 5-7 
• RED for scores 8-10 

Step 4: (Optional) Add color badges to RAG status using column formatting 

• Click on RAG status column name > Open Column settings > Format this column 
• Choose Advance mode and paste this JSON: 

{ 
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", 
  "elmType": "span", 
  "attributes": { 
    "class": "=if([$Score] <= 4, 'sp-field-severity--good', if([$Score] <= 7, 'sp-field-severity--warning', 'sp-field-severity--severeWarning'))" 
  }, 
  "style": { "font-weight": "600" }, 
  "txtContent": "=@currentField" 
} 

• Click Save and test the behavior 

I hope this method will work for your environment. Please let me know if you need any further assistance, I'm here and happy to assist! 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

This is possible by using a calculated column in the list and an IF-based formula.

1. Add a calculated column:
   • Open the list.
   • Select Settings → List settings.
   • Under Columns, select Create column.
   • Name it (for example) RAG Status.
   • For The type of information in this column is, choose Calculated (calculation based on other columns).
2. In Formula, use a structure like this, assuming the two choice columns are named Impact and Likelihood and each stores the numeric value (1–5):

=IF(([Impact]+[Likelihood])<=4,
    "GREEN",
    IF(([Impact]+[Likelihood])<=7,
       "AMBER",
       "RED"))

3. Set The data type returned from this formula to Single line of text and select OK.

This evaluates the sum of the two columns and returns GREEN for totals 2–4, AMBER for 5–7, and RED for 8–10.

If the choice columns currently store text like "1- Insignificant" instead of just the number, create them (or new columns) so that the stored value is numeric 1–5; calculated columns in SharePoint work best when adding numeric values directly.

References:

• Calculate data in lists or libraries
• Examples of common formulas in lists