How to convert Yes/No (or TRUE/FALSE) to 1 and 0
✓ Verified in LibreOffice 25.8.7.3Turn text answers into numbers you can sum, average, or score.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =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. Every formula here is confirmed by actually executing it.