How to convert Yes/No (or TRUE/FALSE) to 1 and 0
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Turn text answers into numbers you can sum, average, or score.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =IF(A2="Yes",1,0) | For real TRUE/FALSE values, just multiply by 1: =A2*1, or use --A2. |
| Google Sheets | =IF(A2="Yes",1,0) | Identical. |
| LibreOffice Calc | =IF(A2="Yes",1,0) | Identical. |
How it works
IF maps the text to numbers: "Yes" becomes 1, anything else 0 — now the column sums (a count of Yeses) and averages (a proportion). The comparison is case-insensitive, so "yes" and "YES" both match. If your cells hold actual boolean TRUE/FALSE rather than text, skip the IF: TRUE already equals 1 in arithmetic, so =A2*1 or the double-negative =--A2 converts them directly. To score more than two answers, nest IF or use a lookup table (Yes=2, Maybe=1, No=0).
Verified, not just documented
We ran =IF(A2="Yes",1,0) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 1 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
IF — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to compare two columns row by row
- How to concatenate cells that meet a condition (TEXTJOIN IF)
- How to fill blank cells with the value above
- How to find duplicate values in a column
- How to flag overdue dates