XLSForm Calculation Examples That Work
XLSForm calculations use a calculate row plus a calculation column: the expression runs invisibly during the interview, and the result can be shown in labels or used in skip logic and constraints. Below are the calculations field teams actually need, in copy-paste form.
Basic arithmetic: totals and percentages
| type | name | label | calculation |
|---|---|---|---|
| integer | members | How many household members? | |
| integer | children | How many are under 18? | |
| calculate | adults | ${members} - ${children} | |
| calculate | pct_children | round((${children} div ${members}) * 100 | |
| note | summary | This household has ${adults} adults (${pct_children}% children). |
Two things to note: division is div (not /), and a calculate result appears in any label via ${name} — instant in-interview feedback for the enumerator.
For weighted scores, Likert means that leave out "Don't know" and 0-100 indexes, see weighted scores in XLSForm.
Age from date of birth
The most-searched XLSForm calculation. This returns completed years:
| type | name | label | calculation |
|---|---|---|---|
| date | dob | Date of birth | |
| calculate | age_years | int((today() - ${dob}) div 365.25) |
Use ${age_years} afterwards to drive skip logic (adult vs child modules) or constraints — one source of truth instead of asking age twice.
Dividing by 365.25 can come out one year short on a birthday. For exact completed years, age in months for under-5s, or years and months, see how to calculate age from date of birth in XLSForm.
Conditional values with if()
if(condition, value_if_true, value_if_false) is the workhorse for scoring and classification:
| type | name | label | calculation |
|---|---|---|---|
| calculate | bmi | ${weight} div (${height_m} * ${height_m}) | |
| calculate | bmi_class | if(${bmi} < 18.5, 'underweight', if(${bmi} < 25, 'normal', 'overweight')) |
Nested if() calls read inside-out, like spreadsheet formulas. Keep the quoted values as choice-style names if you will filter on them later. For AND/OR conditions, select_multiple answers, scoring and the exact errors Excel habits cause, see the full XLSForm if statement guide.
Handling skipped questions: coalesce()
An unanswered numeric question is empty, not zero — and empty breaks arithmetic. coalesce() substitutes a default:
| type | name | label | calculation |
|---|---|---|---|
| calculate | total_income | coalesce(${farm_income} |
If a calculation shows nothing where a number should be, an empty input is the cause in nine cases out of ten. Wrap every optional numeric input in coalesce().
Text calculations: concat and IDs
| type | name | label | calculation |
|---|---|---|---|
| calculate | hh_id | concat(${district}, '-', ${cluster}, '-', ${household_num}) | |
| note | confirm_id | Household ID: ${hh_id} |
concat() builds human-checkable IDs from earlier answers. Pair it with once() — once(concat(...)) — when the ID must not change even if the inputs are edited later.
For random IDs with once(uuid()), padded household IDs and member IDs inside a roster, see unique IDs in XLSForm.
To show a date as DD-MM-YYYY or put it into an ID with format-date(), see date format in XLSForm.
To fill a calculate from a CSV attached to the form, such as a household head's name from the baseline, see pulldata() in XLSForm.
Why calculations beat post-fieldwork cleaning
Every value you compute during the interview is one you don't reconstruct in Stata or R afterwards — and one the enumerator can sanity-check with the respondent still present. A summary note showing the computed total catches a mistyped income on the spot; no cleaning script ever can.
Describing a calculation in plain language — "compute age from date of birth and skip the child module for adults" — is exactly what LooprAI turns into working calculate rows, with the syntax handled for you.
Next: Common XLSForm errors and fixes →
Frequently Asked Questions
How do calculations work in XLSForm?
Add a row with type 'calculate', give it a name, and write an expression in the calculation column, such as ${price} * ${quantity}. The result is computed invisibly during the interview and can be shown in labels or used in skip logic and constraints.
How do I calculate age from date of birth in XLSForm?
Use int((today() - ${dob}) div 365.25) in a calculate row, where dob is a date question. Subtracting dates gives days, dividing by 365.25 converts to years, and int() truncates to completed years.
Why is my XLSForm calculation blank or showing NaN?
Almost always because an input question was skipped — an empty value breaks arithmetic. Wrap optional numeric inputs in coalesce(), for example coalesce(${income}, 0), so unanswered questions are treated as zero instead of empty.
Can I show a calculated value to the enumerator?
Yes. Reference the calculate's name in any label, such as a note reading 'Total: ${total}'. Showing computed totals or IDs during the interview lets the enumerator verify values with the respondent before submitting.
How do I divide numbers in XLSForm?
Use the div operator, not a slash: ${children} div ${members}. The slash character is reserved for XPath paths in ODK forms, so writing a/b is interpreted as a path and fails. Multiplication, addition, and subtraction use the normal symbols.
Can XLSForm calculations use if-else logic?
Yes, with if(condition, value_if_true, value_if_false), and conditions can nest: if(${score} > 80, 'high', if(${score} > 50, 'medium', 'low')). It works for numbers and text, making it the standard tool for scoring and classification during interviews.
No Credit Card Required
Build the form first. Add hosting when you need it.
Start free with LooprAI, then deploy a managed ODK Central server or run DataSnap checks when your project is ready.
Automation handles the mechanical work.
Researchers decide what the evidence means.