SharePoint Document Issues

How to Create a SharePoint Calculated Column Using a Choice Field

Khadija Khan Oct 12, 2026 868 views

Question details

The user needs to calculate a final cost in SharePoint by multiplying a manually entered currency column by a multiplier determined by a choice column.

How to Create a SharePoint Calculated Column Using a Choice Field

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Setting up a custom list or library view with financial calculations that adapt based on a selected VAT category.
Observed behavior: Requires a valid calculated column formula that conditionally applies a 20%, 5%, or 0% VAT multiplier based on the text value of the choice field.

Before you start

Ensure you have permission to edit list or library settings in SharePoint, and verify the exact spelling of your choice field options before writing the formula.

Solution 1Recommended

Use a Nested IF Formula in the Calculated Column

Applying a nested IF function allows SharePoint to evaluate the choice field's text and multiply the base cost by the corresponding VAT rate.

SharePoint calculated columns do not support complex lookup functions directly referencing choice fields in a dynamic way. However, you can achieve this by using nested IF statements to evaluate the text of the choice selection.

Navigate to List Settings

Open 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'. Give your column a name (e.g., 'Total Cost Including VAT') and select 'Calculated (calculation based on other columns)' as the data type.

Input the Nested IF Formula

In the Formula box, enter the following exact string: =IF([VAT Rate]="20% VAT",[Nett Cost]*1.2,IF([VAT Rate]="5% VAT",[Nett Cost]*1.05,IF([VAT Rate]="0% VAT",[Nett Cost]*1,""))). Ensure the column names perfectly match your list's columns.

Set the Data Type and Save

Under 'The data type returned from this formula is', select 'Number' or 'Currency'. Adjust the decimal places as needed, then click 'OK' to save.

Use a Nested IF Formula in the Calculated Column

Syntax Error Troubleshooting: If you receive a syntax error upon saving, it may be due to your region settings. Try replacing all commas (,) in the formula with semicolons (;).

Free Microsoft Office alternative

Manage and Calculate Your Financial Data Offline with WPS Office

While SharePoint is excellent for online list management, WPS Office provides a powerful, free alternative to Microsoft Excel for building complex spreadsheets, managing financial formulas, and tracking costs offline.

1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
2. Create Your VAT Calculation Table: Open WPS Spreadsheet, set up your Nett Cost and VAT Rate columns, and easily apply nested IF formulas or VLOOKUPs to calculate totals.
3. Save and Export: Save your document in .xlsx format, which can be easily shared or imported back into SharePoint lists when necessary.

Fully compatible with Microsoft Excel (.xlsx) and CSV formats for easy SharePoint imports.Advanced spreadsheet features with built-in IF, VLOOKUP, and complex financial functions.Lightweight, fast, and completely free to use.Familiar, intuitive interface that requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP in a SharePoint calculated column?

No, SharePoint calculated columns do not support the VLOOKUP function or referencing data from other lists. You must use nested IF statements, or use Power Automate to fetch and update values dynamically.

Why am I getting a syntax error in my SharePoint formula?

Syntax errors in SharePoint formulas are frequently caused by regional formatting settings. If your region uses a comma as a decimal separator, you must use semicolons (;) instead of commas (,) to separate the arguments in your IF statement.

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

SharePoint generally supports up to 7 nested IF statements in a single calculated column formula. If you have more than 7 choices, you should consider using Power Apps or a Power Automate flow to calculate and update the field.

Document & File Issues