Fix a SharePoint Calculated Column Formula for Yes/No Values
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.
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.
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.
Open the SharePoint list, select Settings > List settings, and select the affected calculated column under Columns.
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.
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.
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 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.

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.




