Others

How to Create a SharePoint Risk Score Calculated Column Formula

Khadija Khan Oct 20, 2026 868 views

Question details

The user needs to calculate a risk score in SharePoint using Choice (Service Criticality) and Yes/No (Internal/Confidential Data) fields, but encounters an unsupported data type error.

How to Create a SharePoint Risk Score Calculated Column Formula

Product: SharePoint
Device & OS: not provided
Scenario: Creating a calculated column to compute a risk score based on multiple specific field values.
Observed behavior: SharePoint rejects the formula with an error stating that one or more column references are not allowed due to unsupported data types.

Before you start

Ensure you have the necessary permissions to edit the SharePoint list and add or modify list columns.

Solution 1Recommended

Use Nested IF Statements in a Calculated Column

Create a formula that assigns specific point values to your Choice and Yes/No fields using the SUM and IF functions.

This method calculates a total risk score by translating text values into integers. Criticality values contribute 2 to 10 points, and each 'Yes' response adds 1 point.

Access List Settings

Navigate to your SharePoint list, click the Gear icon in the top right corner, and select 'List settings'.

Create a New Column

Scroll down to the Columns section and click 'Create column'. Name it 'Risk Score' and choose 'Calculated (calculation based on other columns)' as the type.

Insert the Formula

In the formula box, paste the following: =SUM(IF([Service Criticality]="Very Low",2,IF([Service Criticality]="Low",4,IF([Service Criticality]="Average",6,IF([Service Criticality]="High",8,IF([Service Criticality]="Critical",10,0))))),IF([Internal Data]="Yes",1,0),IF([Confidential Data]="Yes",1,0))

Set the Data Type

Choose 'Number' for the data type returned from this formula, set the decimal places to 0, and click 'OK' to save your column.

Use Nested IF Statements in a Calculated Column

Syntax Check: Ensure your column names in the formula exactly match the columns created in your SharePoint list, including brackets.

Solution 2

Troubleshoot Unsupported Column References

Fix the 'column references are not allowed' error by utilizing boolean values, numeric helper columns, or Power Automate.

Free Microsoft Office alternative

Manage Risk Scoring Offline with WPS Spreadsheet

If SharePoint list formulas become too restrictive or throw data type errors, managing complex risk scoring is often easier in a dedicated spreadsheet. WPS Office provides a free, highly compatible alternative to Microsoft Office for all your data calculation needs.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet for your risk tracking.
2. Set Up Your Columns: Create headers for Service Criticality, Internal Data, and Confidential Data, then fill in your raw data.
3. Apply the Formula: Paste your nested IF and SUM formula into the Risk Score column to calculate results instantly without data type errors.

Fully compatible with Microsoft Excel (.xlsx, .xls) formatsFree, lightweight, and fast-loading office suiteSupports advanced nested IF formulas without SharePoint data restrictionsFamiliar user interface ensures a seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an 'unsupported data type' error in SharePoint calculated columns?

SharePoint sometimes restricts direct references to certain column types, like multiple-selection Choice fields or specific Yes/No configurations, in calculated formulas. You may need to use text string conversions, numeric helper columns, or Power Automate workflows to bypass this limitation.

Can I use Yes/No fields in SharePoint IF statements?

Yes, but they are often treated as boolean values (TRUE/FALSE) in the backend rather than the text string 'Yes' or 'No'. If a direct text reference fails, try checking for TRUE/FALSE instead, such as IF([ColumnName]=TRUE, 1, 0).

How many nested IF statements can I use in a SharePoint formula?

SharePoint supports up to 7 nested IF statements in a calculated column. For more complex logic, consider evaluating the logic in Power Automate or calculating the value in Excel or WPS Spreadsheet before uploading the data.

Office Quick Solutions