Others

Fix a SharePoint Calculated Column Formula for Yes/No Values

Steven ParkerSteven Parker Nov 13, 2026 869 views

Question details

The user needs a SharePoint calculated column to return "OMG" when a Yes/No field is true and "JET" when it is false.

Product
Microsoft SharePoint
Device & OS
not provided
Scenario
Create an IF formula based on the Yes/No column named Health Plan dependency.
Observed behavior
Using Yes without quotation marks causes SharePoint to interpret it as a column reference rather than a Boolean value, resulting in a formula error.
Before you start

Open the list settings and confirm that Health Plan dependency is a Yes/No column. Check its exact displayed name and make sure you have permission to edit the calculated column.

Solution 1Recommended

Use the Boolean value in the SharePoint calculated column

Replace the unquoted Yes comparison with TRUE, or use the Yes/No column directly as the IF condition.

SharePoint Yes/No columns contain Boolean true or false values, not the text strings "Yes" and "No". An unquoted Yes is treated as a column reference, so changing it to "Yes" is not the correct fix for a Boolean field.

1
Open the calculated column settings

Open the SharePoint list, select Settings > List settings, and select the affected calculated column under Columns.

2
Replace the formula

In the Formula box, enter =IF([Health Plan dependency]=TRUE,"OMG","JET"). Alternatively, use =IF([Health Plan dependency],"OMG","JET"). Enter the column name exactly as it appears in the list, enclosed in square brackets.

3
Set the result type and save

Under The data type returned from this formula, select Single line of text because the formula returns text labels. Select OK to save the column settings.

4
Verify both outcomes

Return to the list and edit a test item. Set Health Plan dependency to Yes and save to confirm that the calculated result is "OMG". Set it to No and save to confirm that the result is "JET".

Use the Boolean value in the SharePoint calculated column
Match your regional formula separator: If SharePoint rejects the formula even after the Boolean correction, check whether your regional settings require semicolons instead of commas. In that case, use =IF([Health Plan dependency]=TRUE;"OMG";"JET").
Free Microsoft Office alternative

Use WPS Spreadsheets for Boolean IF calculations in workbooks

WPS Office is a free, lightweight alternative for everyday Office document work, with a familiar interface and Microsoft Office format compatibility for straightforward file migration. WPS Spreadsheets can perform similar Boolean IF calculations in workbooks, but it cannot edit or repair SharePoint calculated columns.

Use IF formulas with TRUE/FALSE cells for similar conditional text results in spreadsheets.Open and save Microsoft Excel .xlsx and .xls files.Work in a familiar spreadsheet interface with easy-to-use formula tools.Move compatible Office documents into a free, lightweight suite without replacing your SharePoint list workflow.
microsoft office alternative - wps office

Frequently Asked Questions

Why does using Yes in my SharePoint formula cause an error?

SharePoint interprets an unquoted Yes as a column reference. For a Yes/No column, open Settings > List settings, select the calculated column, and compare the field with TRUE instead.

Can I use the Yes/No column without comparing it with TRUE?

Yes. Enter =IF([Health Plan dependency],"OMG","JET") in the calculated column's Formula box. The Boolean field itself supplies the condition: true returns "OMG", and false returns "JET".

Should I put quotation marks around Yes?

Not for a SharePoint Yes/No field. Quotation marks turn "Yes" into a text string, but the field contains a Boolean value. Use TRUE or the field directly. A text comparison is appropriate only when the source column actually stores text.

What should I check if the corrected formula still fails?

In List settings, verify that the source column is a Yes/No field and that its displayed name matches the bracketed reference exactly. Also check your regional formula separator and select Single line of text as the calculated result type.

Can WPS Office fix the formula inside my SharePoint list?

No. Edit the calculated column in SharePoint through Settings > List settings. For a similar calculation in WPS Spreadsheets, place a Boolean TRUE or FALSE value in A2 and enter =IF(A2,"OMG","JET") in another cell.