'%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 Microsoft Lists Calculated Column from Choice Values
Question details
The user wants to convert text-based choice values (such as Urgent, Important, Medium, and Low) into numerical values using a calculated column in Microsoft Lists or SharePoint.
- Product
- Microsoft Lists / SharePoint
- Device & OS
- not provided
- Scenario
- Setting up a calculated column for due-date calculations based on priority levels assigned in a choice column.
- Observed behavior
- The user needs the correct nested IF formula and configuration steps to return a specific number based on a selected text choice.
Ensure you have 'Full Control' or 'Design' permissions for the Microsoft List to create and manage columns. Also, verify that your target Choice column is set to single-select, as multi-select choice columns are not supported in calculations.
Use a Nested IF Formula in List Settings
Create a new Calculated column and apply a nested IF function to map text choices to their corresponding numeric values.
Calculated columns in Microsoft Lists and SharePoint allow you to perform calculations based on data in other columns. To convert text strings to numbers, a nested IF statement evaluates the choice and outputs the assigned number.
Open your Microsoft List, click on the Settings gear icon in the top right corner, and select 'List settings'.
Scroll down to the 'Columns' section and click on the 'Create column' link.
Enter a name for your new column and select 'Calculated (calculation based on other columns)' as the data type.
In the Formula text box, enter the nested IF formula: =IF([Choice Column]="Urgent",1,IF([Choice Column]="Important",2,IF([Choice Column]="Medium",5,IF([Choice Column]="Low",10,"")))). Be sure to replace 'Choice Column' with the exact name of your existing choice column.
Under 'The data type returned from this formula is', choose 'Number'. Click 'OK' at the bottom of the page to save your new calculated column.
Need a Powerful Spreadsheet Tool? Try WPS Office
While Microsoft Lists handles basic list management, WPS Spreadsheet is a robust alternative for managing complex datasets, performing advanced IF calculations, and tracking due dates offline or online. It offers seamless compatibility with Microsoft Excel formats.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the Software: Run the installer and follow the on-screen instructions to complete the setup.
- 3. Open WPS Spreadsheet: Launch WPS Office and open Spreadsheet to start organizing your lists and calculating dates with powerful formulas.
Frequently Asked Questions
Why am I getting a syntax error in my calculated column formula?
Syntax errors usually occur if the column name in the formula doesn't exactly match the actual column name. Additionally, if your regional settings use commas as decimal separators, you may need to use semicolons (;) instead of commas (,) in your IF formula.
Can I use a multi-select choice column in a calculated column?
No, SharePoint and Microsoft Lists do not support multi-select choice columns in calculated formulas. You must restrict the choice column to a single selection for the formula to work.
How do I calculate a due date using this numeric value?
Once you have the numeric value in your calculated column (e.g., named 'DaysToAdd'), you can create another calculated column with the formula =[Created] + [DaysToAdd] and set the return type to 'Date and Time'.
