Adding calculations in XLSForm

Last updated: 25 Nov 2025

Calculations can be used inside your form to derive new variables, build advanced form logic, and display results to respondents during data collection.

Calculations are processed within the form, helping save time during data analysis. The results are stored as new columns in the final dataset and can be used throughout the form to apply skip logic, set constraints, or display dynamic content in question labels and notes.

This article explains how to add calculations in XLSForm, covering both basic arithmetic and more advanced expressions.

Note: This article focuses on adding calculations in XLSForm. To learn about adding calculations in the KoboToolbox Formbuilder, see Calculate question type.

For hands-on practice with calculations in XLSForm, see KoboToolbox Academy’s XLSForm Fundamentals Course.

Adding calculations in XLSForm

Calculation expressions are constructed using a combination of question references, mathematical operators, functions, and constants.

To add a calculation in your XLSForm:

1. In the type column of the survey worksheet, enter calculate to add a calculate question type.

2. Enter a name for the calculate question.

   • Because the calculation is not displayed in the form, the calculate question does not require a label.

3. Add a calculation column in the survey worksheet.

4. In the calculation column, enter the calculation expression.

   • Calculations can range from basic arithmetic calculations to advanced calculations using functions and regular expressions.

To refer to the calculation output in the rest of your form (e.g., inside a note question, question label, or form logic), use the question referencing format ${question_name}, where question_name is the name of the calculate question.

survey worksheet

type	name	label	calculation
integer	bags	Total number of bags sold
decimal	price	Price per bag
calculate	total_amount		${bags} * ${price}
note	display_total	The total is ${total_amount}
survey

Arithmetic calculations

Calculations in XLSForm can range from simple arithmetic calculations to advanced derivation of variables.

Arithmetic calculations allow you to perform basic calculations using the following operators:

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
div	Division
mod	Modulo (calculates the remainder of a division)

Calculations in XLSForm follow the BODMAS rule for the order of mathematical operations: Brackets, Order of powers, Division, Multiplication, Addition, and Subtraction. This means that calculations within brackets (or parentheses) are performed first, followed by powers, then divisions, multiplications, and so on. Using brackets correctly ensures that your calculations function as expected.

Advanced calculations

Advanced calculations in XLSForm often rely on functions and regular expressions to make calculations more efficient.

• Functions are predefined operations used to automatically perform complex tasks like rounding values, calculating powers, or extracting the current date.

• Regular expressions (regex) are search patterns used to match specific characters within a string of text.

For a comprehensive list of functions available in XLSForm, see Using functions in XLSForm. To learn more about regular expressions, see Restricting text responses with regular expressions.

Examples of more advanced calculations include:

Calculation	Description
int((today()-${DOB}) div 365.25)	Calculate age from date of birth.
int(today()-${date})	Calculate days since a date.
format-date(${date}, '%b')	Return just the month from a date.
concat(${first}, " ", ${middle}, " ", ${last})	Create a single string with a respondent’s full name.
jr:choice-name(${question1}, '${question1}')	Return a choice’s label, in the current language, from the choice list.
translate(${full_name}, "ABCDEFGHIJKLMNOPQRSTUVWXYZ ", "abcdefghijklmnopqrstuvwxyz_")	Convert uppercase letters to lowercase and spaces to underscores.
substr(${question}, 1, 2)	Keep only the 1st letter or number in a string.
int(random()*10)	Generate a random number between 0 and 10.
selected-at(${gps}, 0)	Isolate latitude from GPS coordinates.
selected-at(${gps}, 1)	Isolate longitude from GPS coordinates.
if(regex(${id}, '^ML-'), 'yes', 'no')	Create a binary variable that takes yes if the respondent ID starts with “ML-”.

Setting dynamic default responses

The calculation field can also be used to set dynamic default responses. Dynamic default responses allow you to display a default response inside a question based on a previous response.

To set a dynamic default response:

1. In the calculation column, enter the reference to the question that will dynamically populate the default response.

2. In the trigger column, enter the question that will activate the calculation.

   • Typically, this would be the same question referenced in the calculation column, so that any change to the trigger question will also update the default response.

survey worksheet

type	name	label	calculation	trigger
text	hh_name	Name of the head of household
text	phone	Household phone number
text	phone_name	Name of the phone owner	${hh_name}	${hh_name}
survey

Note: If you want to prevent users from editing the field, set the read_only column to TRUE.

Troubleshooting

Troubleshooting recommendations

To facilitate troubleshooting, display calculation outputs in a note while developing your form. This helps determine if the calculation is evaluating correctly and makes identifying issues easier. You can also break down long expressions into smaller ones and display the output of each to pinpoint problems.

Calculations not working properly

If your calculations are not working, check the following:

• Syntax: All opened parentheses are closed, straight quotes ' are used), and commas are included where needed.
• References: Question references correctly match the question name, no spaces or typos, no circular references (i.e., the calculation does not depend on itself).
• Data types: Numeric and string calculations are not combined within the same question, data types are used correctly.

Dealing with empty fields

Unanswered questions are treated as empty strings and will not automatically convert to zero. When an empty value is used in a calculation, it results in "Not a Number" (NaN). To convert empty values to zero for calculations, use the coalesce() or if() functions. For example:

• coalesce(${potentially_empty_value}, 0)
• if(${potentially_empty_value}="", 0, ${potentially_empty_value})

Another option is to set default values for each of the numeric variables to 0 in the default column.

Calculations keep changing in the form

Expressions are re-evaluated as an enumerator progresses through a form. This is especially important for functions not connected to fields in the form, such as random() or now(), as their values may change under these conditions.

Expressions are re-evaluated when:

• A form is opened
• The value of any question in the calculation changes
• A repeat group is added or deleted
• A form is saved or finalized

To control when an expression is evaluated, set a trigger to evaluate it only when a given question is answered, or the function once() to ensure the expression is only evaluated once (e.g., once(random()) or once(today())).