Survey Arithmetic and Conditional Questions Guide

Overview

Arithmetic and Conditional question types let Tupaia surveys compute and derive answers automatically, rather than relying solely on manual input. Configured through Excel import files, they allow you to:

Arithmetic questions: Automatically calculate values based on formulas using answers from previous questions
Conditional questions: Display different values based on logical conditions applied to previous answers

Excel File Structure

Basic Requirements

Your Excel file must have exactly ONE tab containing all survey questions.

Required Columns

Column	Description	Example
code	Unique identifier for the question (no periods allowed)	`SchFF01`
type	Question type	`Arithmetic`, `Condition`, `Number`, `Radio`, etc.
name	Display name (max 230 characters)	`Total Score`
text	Question text shown to user	`What is your total score?`
detail	Additional hints or details	`This will be calculated automatically`
options	For Radio/Binary questions	`Yes,No` or on separate lines
newScreen	Start a new screen?	`Yes` or `No`
visibilityCriteria	Conditions for showing the question	`previousQuestion:Yes`
validationCriteria	Validation rules	`mandatory: true`
config	Special configuration for Arithmetic/Condition types	See below

Important Notes

Questions must be ordered sequentially - you can only reference questions that appear BEFORE the current question
Each question code must be unique within the survey
The config column is where you define the logic for Arithmetic and Conditional questions

Arithmetic Questions

Arithmetic questions automatically calculate numeric values based on formulas using answers from previous questions.

Basic Structure

In the config column, enter your arithmetic configuration using this format:

formula: $question_code_1 + $question_code_2

Config Components

1. formula (Required)

The mathematical expression to calculate the result.

Format:

formula: $question_code_1 + $question_code_2 * 10

Rules:

Question codes must be prefixed with $
Supported operations: +, -, *, /, ()
Can use comparison operators: >, <, >=, <=, =, !=
Referenced questions must appear BEFORE this question in the survey

Example:

formula: $score_math + $score_english + $score_science

2. defaultValues (Optional, but required for optional questions)

Provides default numeric values for questions that might not be answered.

Format:

defaultValues: question_code_1:0,question_code_2:5

When to use:

Required if any referenced questions are optional (not mandatory)
Must provide numeric values
Separate multiple defaults with commas

Example:

defaultValues: score_math:0,score_english:0,score_science:0

3. valueTranslation (Optional, but required for non-numeric questions)

Translates non-numeric answer values (like "Yes"/"No") to numeric values for calculations.

Format:

valueTranslation: question_code.optionValue:numericValue,question_code.optionValue:numericValue

When to use:

Required if your formula references Binary, Radio, or other non-numeric question types
Each possible answer value must be mapped to a number

Example:

valueTranslation: has_electricity.Yes:1,has_electricity.No:0,has_water.Yes:1,has_water.No:0

4. answerDisplayText (Optional)

Customizes how the calculated result is displayed to the user.

Format:

answerDisplayText: Modified $result equals $result

Rules:

Use $result to reference the calculated value
Question codes do NOT use the $ prefix here
Plain text combined with question codes and result

Example:

answerDisplayText: Total score for student_name is $result out of max_possible

Complete Arithmetic Example

Here's a complete example in your Excel config column:

formula: $section1_score + $section2_score + $section3_score
defaultValues: section1_score:0,section2_score:0,section3_score:0
answerDisplayText: Total combined score is $result

Multi-line Format in Excel

You can enter the config on multiple lines in the Excel cell for readability:

formula: $section1_score + $section2_score + $section3_score
defaultValues: section1_score:0,section2_score:0,section3_score:0
answerDisplayText: Total combined score is $result

Example Survey with Arithmetic Question

code	type	name	text	config	newScreen	validationCriteria
math_score	Number	Math Score	Enter the math test score		Yes	mandatory: true
english_score	Number	English Score	Enter the English test score		No	mandatory: true
science_score	Number	Science Score	Enter the science test score		No	mandatory: true
total_score	Arithmetic	Total Score	Total score across all subjects	formula: $math_score + $english_score + $science_score defaultValues: math_score:0,english_score:0,science_score:0	No

Conditional Questions

Conditional questions display different values based on logical conditions applied to previous answers. They're useful for branching logic and dynamic survey flows.

Basic Structure

In the config column, enter your conditional configuration:

conditions: Yes:$question_1 >= 3,No:$question_1 < 3

Config Components

1. conditions (Required)

Maps target values to conditional expressions that determine what value the question returns.

Format:

conditions: targetValue:expression,targetValue:expression

Rules:

Each condition maps a return value to a logical expression
Question codes must be prefixed with $
Supported operators: >, <, >=, <=, =, !=
Referenced questions must appear BEFORE this question
When a condition evaluates to true, the question returns the corresponding targetValue

Example:

conditions: Pass:$total_score >= 50,Fail:$total_score < 50

2. defaultValues (Optional, but required for optional questions)

Provides default values for optional questions used in conditions.

Format:

defaultValues: targetValue.question_code:value,targetValue.question_code:value

When to use:

Required if any referenced questions are optional
Must specify default per condition (targetValue)
Separate multiple defaults with commas

Example:

defaultValues: Pass.total_score:0,Fail.total_score:0

Complete Conditional Example

Here's a complete example in your Excel config column:

conditions: High_Risk:$risk_score >= 5,Medium_Risk:$risk_score >= 3,Low_Risk:$risk_score < 3
defaultValues: High_Risk.risk_score:0,Medium_Risk.risk_score:0,Low_Risk.risk_score:0

Example Survey with Conditional Question

code	type	name	text	config	newScreen	validationCriteria
num_symptoms	Number	Number of Symptoms	How many symptoms does the patient have?		Yes	mandatory: true
severity_level	Condition	Severity Level	Patient severity classification	conditions: Severe:$num_symptoms >= 5,Moderate:$num_symptoms >= 3,Mild:$num_symptoms < 3 defaultValues: Severe.num_symptoms:0,Moderate.num_symptoms:0,Mild.num_symptoms:0	No
treatment_plan	Radio	Treatment Plan	Recommended treatment	Hospitalization,Outpatient Care,Home Rest	No	mandatory: true

In this example:

If num_symptoms is 5 or more, severity_level returns "Severe"
If num_symptoms is 3 or 4, severity_level returns "Moderate"
If num_symptoms is less than 3, severity_level returns "Mild"

Common Rules and Best Practices

Question Ordering

Question codes must reference questions that appear BEFORE the current question in the Excel sheet.

Correct:

Row 1: question_a (Number)
Row 2: question_b (Number)
Row 3: total (Arithmetic) - references $question_a and $question_b ✓

Incorrect:

Row 1: total (Arithmetic) - references $question_a and $question_b ✗
Row 2: question_a (Number)
Row 3: question_b (Number)

Using defaultValues

Always provide defaultValues when:

Referenced questions are optional (not mandatory)
You want calculations to work even if some questions aren't answered

Example:

formula: $optional_score_1 + $optional_score_2
defaultValues: optional_score_1:0,optional_score_2:0

Using valueTranslation

Always provide valueTranslation when:

Your formula references Binary questions (Yes/No)
Your formula references Radio or Checkbox questions
Any non-numeric question type is used in calculations

Example with Binary questions:

formula: $has_electricity + $has_water + $has_sanitation
valueTranslation: has_electricity.Yes:1,has_electricity.No:0,has_water.Yes:1,has_water.No:0,has_sanitation.Yes:1,has_sanitation.No:0

Config Formatting Tips

Single line (works but harder to read):

formula: $a + $b,defaultValues: a:0,b:0

Multi-line (recommended for readability):

formula: $a + $b
defaultValues: a:0,b:0
answerDisplayText: Total is $result

Complex formulas can use parentheses:

formula: ($section1_total / $section1_max) * 100
defaultValues: section1_total:0,section1_max:1

Complete Examples

Example 1: School Assessment Score Calculator

This example calculates a total score and determines pass/fail status.

code	type	name	text	config	newScreen	validationCriteria
student_name	FreeText	Student Name	Enter student name		Yes	mandatory: true
math_test	Number	Math Test	Math test score (0-100)		Yes	mandatory: true min: 0 max: 100
science_test	Number	Science Test	Science test score (0-100)		No	mandatory: true min: 0 max: 100
english_test	Number	English Test	English test score (0-100)		No	mandatory: true min: 0 max: 100
total_score	Arithmetic	Total Score	Total score across all subjects	formula: $math_test + $science_test + $english_test defaultValues: math_test:0,science_test:0,english_test:0 answerDisplayText: Total score is $result out of 300	No
average_score	Arithmetic	Average Score	Average score across subjects	formula: $total_score / 3 defaultValues: total_score:0	No
pass_fail	Condition	Result	Pass or Fail status	conditions: Pass:$average_score >= 50,Fail:$average_score < 50 defaultValues: Pass.average_score:0,Fail.average_score:0	No
grade	Condition	Grade	Letter grade	conditions: A:$average_score >= 80,B:$average_score >= 70,C:$average_score >= 60,D:$average_score >= 50,F:$average_score < 50 defaultValues: A.average_score:0,B.average_score:0,C.average_score:0,D.average_score:0,F.average_score:0	No

Example 2: Health Facility Assessment

This example uses binary questions with value translation for facility scoring.

code	type	name	text	options	config	newScreen	validationCriteria
facility_name	FreeText	Facility Name	Enter facility name			Yes	mandatory: true
has_electricity	Binary	Electricity	Does the facility have electricity?	Yes,No		Yes	mandatory: true
has_water	Binary	Running Water	Does the facility have running water?	Yes,No		No	mandatory: true
has_sanitation	Binary	Sanitation	Does the facility have proper sanitation?	Yes,No		No	mandatory: true
has_emergency	Binary	Emergency Services	Does the facility provide emergency services?	Yes,No		No	mandatory: true
facility_score	Arithmetic	Facility Score	Overall facility infrastructure score		formula: $has_electricity + $has_water + $has_sanitation + $has_emergency valueTranslation: has_electricity.Yes:1,has_electricity.No:0,has_water.Yes:1,has_water.No:0,has_sanitation.Yes:1,has_sanitation.No:0,has_emergency.Yes:1,has_emergency.No:0 answerDisplayText: Facility infrastructure score: $result out of 4	No
facility_rating	Condition	Rating	Facility rating based on score		conditions: Excellent:$facility_score >= 4,Good:$facility_score >= 3,Fair:$facility_score >= 2,Poor:$facility_score < 2 defaultValues: Excellent.facility_score:0,Good.facility_score:0,Fair.facility_score:0,Poor.facility_score:0	No

Example 3: Risk Assessment with Complex Logic

This example combines multiple factors for risk scoring.

code	type	name	text	options	config	newScreen	validationCriteria
patient_age	Number	Patient Age	Enter patient age in years			Yes	mandatory: true min: 0 max: 120
has_diabetes	Binary	Diabetes	Does the patient have diabetes?	Yes,No		No	mandatory: true
has_hypertension	Binary	Hypertension	Does the patient have hypertension?	Yes,No		No	mandatory: true
smoker	Binary	Smoking Status	Is the patient a smoker?	Yes,No		No	mandatory: true
age_risk_score	Condition	Age Risk	Risk score based on age		conditions: High:$patient_age >= 60,Medium:$patient_age >= 40,Low:$patient_age < 40 defaultValues: High.patient_age:0,Medium.patient_age:0,Low.patient_age:0	No
age_risk_numeric	Arithmetic	Age Risk Points	Numeric age risk		formula: $patient_age >= 60 ? 3 : ($patient_age >= 40 ? 2 : 1)	No
condition_risk	Arithmetic	Condition Risk	Risk from existing conditions		formula: $has_diabetes + $has_hypertension + $smoker valueTranslation: has_diabetes.Yes:1,has_diabetes.No:0,has_hypertension.Yes:1,has_hypertension.No:0,smoker.Yes:1,smoker.No:0	No
total_risk_score	Arithmetic	Total Risk Score	Overall risk score		formula: $age_risk_numeric + $condition_risk defaultValues: age_risk_numeric:0,condition_risk:0 answerDisplayText: Total risk score: $result out of 6	No
risk_category	Condition	Risk Category	Patient risk classification		conditions: Very_High:$total_risk_score >= 5,High:$total_risk_score >= 4,Moderate:$total_risk_score >= 2,Low:$total_risk_score < 2 defaultValues: Very_High.total_risk_score:0,High.total_risk_score:0,Moderate.total_risk_score:0,Low.total_risk_score:0	No

Troubleshooting

Common Errors and Solutions

Error: "Question code not found in formula"

Problem: You're referencing a question that doesn't exist or appears after the current question.

Solution:

Verify the question code spelling matches exactly
Ensure the referenced question appears BEFORE the current question in the Excel sheet
Check that you're using the $ prefix for question codes in formulas

Error: "Default values required for optional questions"

Problem: Your formula references questions that aren't mandatory, but you haven't provided default values.

Solution: Add defaultValues to your config:

formula: $optional_field_1 + $optional_field_2
defaultValues: optional_field_1:0,optional_field_2:0

Error: "Value translation required for non-numeric questions"

Problem: Your formula uses Binary, Radio, or other non-numeric question types without translating their values.

Solution: Add valueTranslation to map text answers to numbers:

formula: $binary_question_1 + $radio_question_2
valueTranslation: binary_question_1.Yes:1,binary_question_1.No:0,radio_question_2.Option1:1,radio_question_2.Option2:2,radio_question_2.Option3:3

Error: "Invalid formula syntax"

Problem: Your formula contains syntax errors or unsupported operations.

Solution:

Check that all parentheses are matched
Verify you're using supported operators: +, -, *, /, >, <, >=, <=, =, !=
Remove any spaces in question codes (use underscores instead)
Ensure all question codes have the $ prefix

Error: "Condition must specify target values"

Problem: Your conditional config doesn't properly map conditions to target values.

Solution: Ensure each condition has a target value before the colon:

conditions: Pass:$score >= 50,Fail:$score < 50

Testing Your Survey

Before deploying your survey:

Verify question order: Ensure all referenced questions appear before questions that reference them
Check mandatory flags: Make sure questions used in calculations are marked as mandatory, or provide default values
Test all branches: For conditional questions, verify that all possible conditions are covered
Validate formulas: Test your formulas with sample data to ensure calculations are correct
Review value translations: Ensure all possible answer values are mapped when using non-numeric questions

Getting Help

If you encounter issues not covered in this guide:

Check that your Excel file has exactly one tab
Verify all required columns are present
Review the question code format (no periods, unique codes)
Ensure config entries follow the exact format shown in examples
Contact your Tupaia administrator with the specific error message

Summary

Arithmetic Questions Quick Reference

Type: Arithmetic
Config:
  formula: $question_1 + $question_2
  defaultValues: question_1:0,question_2:0
  valueTranslation: question_1.Yes:1,question_1.No:0
  answerDisplayText: Total is $result

Conditional Questions Quick Reference

Type: Condition
Config:
  conditions: High:$score >= 5,Low:$score < 5
  defaultValues: High.score:0,Low.score:0

Key Reminders

Question codes in formulas/conditions use $ prefix
Referenced questions must appear BEFORE current question
Use defaultValues for optional questions
Use valueTranslation for non-numeric questions
Config can be multi-line for readability
Test your survey thoroughly before deployment

Survey Arithmetic and Conditional Questions Guide

Overview

Table of Contents

Excel File Structure

Basic Requirements

Required Columns

Important Notes

Arithmetic Questions

Basic Structure

Config Components

1. formula (Required)

2. defaultValues (Optional, but required for optional questions)

3. valueTranslation (Optional, but required for non-numeric questions)

4. answerDisplayText (Optional)

Complete Arithmetic Example

Multi-line Format in Excel

Example Survey with Arithmetic Question

Conditional Questions

Basic Structure

Config Components

1. conditions (Required)

2. defaultValues (Optional, but required for optional questions)

Complete Conditional Example

Example Survey with Conditional Question

Common Rules and Best Practices

Question Ordering

Using defaultValues

Using valueTranslation

Config Formatting Tips

Complete Examples

Example 1: School Assessment Score Calculator

Example 2: Health Facility Assessment

Example 3: Risk Assessment with Complex Logic

Troubleshooting

Common Errors and Solutions

Error: "Question code not found in formula"

Error: "Default values required for optional questions"

Error: "Value translation required for non-numeric questions"

Error: "Invalid formula syntax"

Error: "Condition must specify target values"

Testing Your Survey

Getting Help

Summary

Arithmetic Questions Quick Reference

Conditional Questions Quick Reference

Key Reminders

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Tupaia Wiki

User Guides

Configuration Documentation

VizBuilder - Data Visualisation

Resources

Clone this wiki locally