'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a SharePoint Calculated Column Formula for Nested IF Talent Ratings
Question details
The user needs to create a SharePoint calculated column named 'Talent Rating' that assigns a score from 1 to 9 based on specific combinations of 'Performance' and 'Potential' text values.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Assigning numerical talent ratings based on text-based performance and potential metrics using a calculated list column.
- Observed behavior
- A functioning calculated column formula using nested IF and AND functions to output correct numerical ratings based on the defined 9-box grid criteria.
Ensure that the 'Performance' and 'Potential' columns already exist in your SharePoint list and are populated with the exact text values (e.g., Exceeds, Meets, Breadth) specified in your criteria.
Use a Nested IF and AND Formula for Talent Ratings
Apply this specific nested IF formula in your SharePoint list settings to automatically generate 1-9 ratings based on combinations of Performance and Potential.
SharePoint calculated columns use Excel-like formula syntax. By combining the IF and AND functions, you can check multiple conditions simultaneously and assign the correct talent rating for each row.
Navigate to your SharePoint list, click on the 'Settings' gear icon in the top right corner, and select 'List settings'.
Scroll down to the Columns section and click 'Create column'. Name the column 'Talent Rating' and select 'Calculated (calculation based on other columns)' as the data type.
In the Formula text box, paste the following exact code: =IF(AND(Performance="Exceeds",Potential="Breadth"),9,IF(AND(Performance="Exceeds",Potential="Balanced"),8,IF(AND(Performance="Exceeds",Potential="Depth"),7,IF(AND(Performance="Meets",Potential="Breadth"),6,IF(AND(Performance="Meets",Potential="Balanced"),5,IF(AND(Performance="Meets",Potential="Depth"),4,IF(AND(Performance="Needs Improvement",Potential="Breadth"),3,IF(AND(Performance="Needs Improvement",Potential="Balanced"),2,IF(AND(Performance="Needs Improvement",Potential="Depth"),1)))))))))
Below the formula box, choose 'Number' for the data type returned from this formula, set the number of decimal places to 0, and click 'OK' to save.
Manage Your Data Effortlessly with WPS Spreadsheet
While SharePoint handles list calculations online, you can perform these exact nested IF calculations offline and securely using WPS Spreadsheet. WPS Office is a lightweight, fully compatible suite for all your document, spreadsheet, and presentation needs.
- 1. Download and Install: Get WPS Office for free from the official website and launch WPS Spreadsheet.
- 2. Prepare Data: Create your dataset offline with the necessary Performance and Potential columns.
- 3. Apply Formula: Paste the exact same nested IF formula used for SharePoint into your rating column and drag the fill handle to apply it to all rows instantly.
Frequently Asked Questions
Why is my SharePoint calculated column formula returning a syntax error?
Syntax errors typically occur due to misplaced commas, missing parentheses, or regional settings. If your SharePoint site is configured for a region that uses a comma as a decimal separator (like many European countries), you must use semicolons (;) instead of commas (,) to separate formula arguments.
Can I use the exact same nested IF formula in Excel or WPS Spreadsheet?
Yes, the syntax for IF and AND functions in SharePoint calculated columns is virtually identical to the formula syntax used in standard spreadsheet software like Excel and WPS Spreadsheet.
Is there a limit to how many nested IF statements I can use in SharePoint?
In modern SharePoint Online, you can nest up to 64 IF statements, making a 9-condition formula perfectly valid. However, for extremely complex conditions, relying on a lookup column or Power Automate might be easier to maintain.
