How to Calculate Minimum Values with Pending Text in SharePoint Columns
Question details
The user needs a SharePoint calculated column formula to return 'Pending' if two columns are both 'Pending', or return the minimum numeric value if one or both columns contain numbers.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column in a SharePoint list that processes two separate columns containing either a numeric value of days or the text string 'Pending'.
- Observed behavior
- The formula needs to dynamically output the word 'Pending' or evaluate the numeric values to return the lower number based on the column contents.
Verify the exact internal names of your SharePoint columns, as spaces or special characters in column names require specific bracket formatting to prevent syntax errors.
Use a Nested IF and AND Formula to Compare Values
Apply a formula combining IF and AND functions to evaluate the text conditions first, then calculate the minimum numeric value.
This solution uses logical checks to see if both columns contain the exact text 'Pending'. If they do, it outputs 'Pending'. If not, it assumes numeric values are present and compares them to find the lower number.
Navigate to your SharePoint list, go to List Settings, and select 'Create column' or edit your existing calculated column.
In the formula box, enter: =IF(AND([Column1]="Pending",[Column2]="Pending"),"Pending",IF([Column1]<[Column2],[Column1],[Column2])). Replace [Column1] and [Column2] with your exact column names, such as [TAT from Approval Date].
Choose the data type returned by this formula. Since the result could be text ('Pending') or a number, select 'Single line of text' to prevent data type mismatch errors.
Click OK to save the column. Add test items to your list (e.g., both Pending, Pending and 2, 10 and 2) to ensure it correctly returns Pending, 2, and 2 respectively.
Manage Your Data with WPS Spreadsheet
While SharePoint handles list calculations online, prototyping your data and formulas is often easier offline. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, allowing you to test complex IF and AND formulas locally before deploying them to your online lists.
Frequently Asked Questions
Why does my SharePoint calculated column return a syntax error?
Syntax errors in SharePoint calculated columns are often caused by missing square brackets around column names with spaces (e.g., [Column Name]), incorrect use of commas or semicolons depending on regional settings, or missing quotation marks around text strings like "Pending".
Can I return a text string in a number-type calculated column?
No, if your formula might return a text string (like 'Pending'), you must set the 'The data type returned from this formula is' option to 'Single line of text'. Selecting 'Number' will result in an error or blank value when the text condition is met.
How does the IF function handle comparing a number to text in SharePoint?
In SharePoint formulas, text strings are generally evaluated as greater than numbers. If your IF function compares a number to 'Pending', the number will be treated as the lower value. However, explicitly handling the 'Pending' text with an AND/IF statement prevents unexpected logical errors.
Can I use this formula in Excel or WPS Spreadsheet?
Yes, the exact same logical formula using IF and AND functions works in both Microsoft Excel and WPS Spreadsheet. You simply replace the SharePoint column references (like [Column1]) with standard cell references (like A2 and B2).
