SharePoint List calculated column based on three different columns values

Question

On one side, there are four dropdown columns (Scope, Time, Resources and Budget) that can contain only one the following values: Green, Yellow and Red.

On the other side, I want to make "Project Health" a calculated column with those same options (green, yellow and red) based on the 4 columns above.

The potential cases would be the following:

1. If one of the four columns is RED, "Project Health" is RED
2. If one of the four columns is YELLOW, "Project Health" is YELLOW
3. If all four columns are GREEN, "Project Health" is GREEN.

I am using SharePoint 365.

Answer 1

Use this formula:

=IF(OR([Scope]="RED", [Time]="RED", [Resources]="RED", [Budget]="RED"), "RED", IF(OR([Scope]="YELLOW", [Time]="YELLOW", [Resources]="YELLOW", [Budget]="YELLOW"), "YELLOW", IF(OR([Scope]="GREEN", [Time]="GREEN", [Resources]="GREEN", [Budget]="GREEN"), "GREEN", "")))

Microsoft documentations:

1. Calculated Field Formulas
2. OR function
3. IF function

Note:

1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Answer 2

Please the following steps:

1.Create a Calculated column named "Project Health" and use the below formula:

=IF(OR(Scope="RED",Time="RED",Resources="RED",Budget="RED"),"RED",IF(OR(Scope="YELLOW",Time="YELLOW",Resources="YELLOW",Budget="YELLOW"),"YELLOW",IF(OR(Scope="GREEN",Time="GREEN",Resources="GREEN",Budget="GREEN"),"GREEN","")))

2.Click on the "Project Health" field and select Column settings, and then select Format this column

3.On the Format column Panel, click on Advanced mode. Paste the following JSON formatting:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "debugMode": true,
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Project_x0020_Health] == 'RED', '#FA5858',if([$Project_x0020_Health] == 'GREEN', '#82FA58','#F4FA58'))",
    "font-size": "14px",
    "color": "#000000"
  }
}

4.Save it