Weighted Score in XLSForm: Totals, Means and Indexes

To calculate a weighted score in XLSForm, add a calculate row that multiplies each answer by its weight and adds them up: (${staples_days} * 2) + (${pulses_days} * 3) + ${veg_days}. For select_one questions, make the choice code the score (1 to 5), so the answer can go straight into the sum. The examples below cover totals, weights, Likert means that skip "Don't know", reverse-scored items, select_multiple scores and 0-100 indexes, and each one compiles with pyxform and ODK Validate, the same check ODK Central runs when you upload a form.

Download example form (.xlsx)All seven scoring examples in one XLSForm, one group each. Tested with pyxform and ODK Validate; free to reuse under CC BY 4.0.Download

How do I add up the scores of select_one questions?

Give each choice the score as its name in the choices sheet, then add the answers:

type	name	label	calculation	required
select_one sat	sat_waiting	How satisfied were you with the waiting time at the clinic?		yes
select_one sat	sat_staff	How satisfied were you with how the staff treated you?		yes
select_one sat	sat_medicine	How satisfied were you with the availability of medicines?		yes
calculate	sat_total		${sat_waiting} + ${sat_staff} + ${sat_medicine}
note	show_sat_total	Satisfaction score: ${sat_total} out of 15

◀ ▶surveychoicessettings+

list_name	name	label
sat	1	Very dissatisfied
sat	2	Dissatisfied
sat	3	Neither satisfied nor dissatisfied
sat	4	Satisfied
sat	5	Very satisfied

◀ ▶surveychoicessettings+

A select_one answer is stored as text, '4', but +, -, * and div turn it into a number, so no conversion is needed. This only works when the choice names are numbers. If your list uses names such as satisfied, the sum is not a number and nothing warns you, which is why numeric codes are the safer habit for any list you might score.

How do I give questions different weights?

Multiply each answer by its weight. The WFP Food Consumption Score is the standard example: days each food group was eaten in the past 7 days, times a fixed weight per group:

type	name	label	calculation	constraint	constraint_message
integer	fcs_staples	In the past 7 days, on how many days did the household eat cereals, roots or tubers?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_pulses	...pulses, beans or nuts?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_veg	...vegetables or leaves?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_fruit	...fruit?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_meat	...meat, fish or eggs?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_milk	...milk or dairy products?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_sugar	...sugar or sweets?		. >= 0 and . <= 7	Enter 0 to 7 days
integer	fcs_oil	...oil or fat?		. >= 0 and . <= 7	Enter 0 to 7 days
calculate	fcs_score		(${fcs_staples} * 2) + (${fcs_pulses} * 3) + ${fcs_veg} + ${fcs_fruit} + (${fcs_meat} * 4) + (${fcs_milk} * 4) + (${fcs_sugar} * 0.5) + (${fcs_oil} * 0.5)
calculate	fcs_group		if(${fcs_score} <= 21, 1, if(${fcs_score} <= 35, 2, 3))
note	show_fcs	Food Consumption Score: ${fcs_score} (group ${fcs_group}: 1 = poor; 2 = borderline; 3 = acceptable)

◀ ▶surveychoicessettings+

The brackets are not required, because * runs before +, but they make each weight easy to check against the indicator's guidance. Write decimal weights with a dot (0.5), whatever your computer's regional settings are. The constraint keeps every input between 0 and 7, so the score cannot go past its maximum of 112. The full module with WFP's notes is in standardized survey modules.

How do I calculate a Likert mean that skips "Don't know"?

Code "Don't know" as -99, then count only the answers above 0 and divide by that count:

type	name	label	calculation	required
select_one agree	trust_leaders	Community leaders act in the interest of everyone.		yes
select_one agree	trust_police	The police treat people fairly.		yes
select_one agree	trust_health	The health centre gives good care.		yes
calculate	trust_sum		if(${trust_leaders} > 0, ${trust_leaders}, 0) + if(${trust_police} > 0, ${trust_police}, 0) + if(${trust_health} > 0, ${trust_health}, 0)
calculate	trust_answered		if(${trust_leaders} > 0, 1, 0) + if(${trust_police} > 0, 1, 0) + if(${trust_health} > 0, 1, 0)
calculate	trust_mean		if(${trust_answered} > 0, round(${trust_sum} div ${trust_answered}, 2), '')
note	show_trust	Mean trust score: ${trust_mean} (from ${trust_answered} of 3 statements)

◀ ▶surveychoicessettings+

list_name	name	label
agree	1	Strongly disagree
agree	2	Disagree
agree	3	Neither agree nor disagree
agree	4	Agree
agree	5	Strongly agree
agree	-99	Don't know

◀ ▶surveychoicessettings+

Someone who answers 5, 4 and "Don't know" gets a mean of 4.5 from 2 statements, not 3 from 3. The if(${trust_answered} > 0, ...) guard leaves the mean empty when every answer is "Don't know", instead of dividing by zero. There is no average() or mean() function in XLSForm: a mean is always a sum div a count.

How do I reverse-score an item?

For a 1 to 5 scale, a reversed item scores 6 - ${q}, so 1 becomes 5 and 5 becomes 1. For a 1 to 4 scale, use 5 - ${q}:

type	name	label	calculation	required
select_one agree	cope_manage	I can handle the problems that come up in my work.		yes
select_one agree	cope_overwhelmed	I often feel overwhelmed at work.		yes
select_one agree	cope_support	I can ask a colleague for help when I need it.		yes
select_one agree	cope_give_up	I feel like giving up on difficult tasks.		yes
calculate	cope_total		if(${cope_manage} > 0, ${cope_manage}, 0) + if(${cope_overwhelmed} > 0, 6 - ${cope_overwhelmed}, 0) + if(${cope_support} > 0, ${cope_support}, 0) + if(${cope_give_up} > 0, 6 - ${cope_give_up}, 0)
note	show_cope	Coping score: ${cope_total} out of 20 (higher is better)

◀ ▶surveychoicessettings+

The two negative statements are reversed so that a higher score always means better coping. Keep the choice list the same for every item and reverse in the calculation, not in the choices sheet: two lists with the same labels and opposite codes are hard to spot in the data and easy to analyse the wrong way round.

How do I score a select_multiple question?

Use selected() for each option that carries weight, or count-selected() when every option scores 1:

type	name	label	calculation
select_multiple assets	hh_assets	Which of these does the household own?
calculate	asset_score		if(selected(${hh_assets}, '1'), 1, 0) + if(selected(${hh_assets}, '2'), 2, 0) + if(selected(${hh_assets}, '3'), 3, 0) + if(selected(${hh_assets}, '4'), 5, 0)
calculate	asset_count		if(selected(${hh_assets}, '-96'), 0, count-selected(${hh_assets}))
note	show_assets	Asset score: ${asset_score} (${asset_count} assets)

◀ ▶surveychoicessettings+

list_name	name	label
assets	1	Radio
assets	2	Mobile phone
assets	3	Bicycle
assets	4	Motorbike
assets	-96	None of these

◀ ▶surveychoicessettings+

You cannot add a select_multiple answer directly: its value is a list such as 1 3 4, not a number. selected() checks one option at a time, so each option can have its own weight. For the "None of these" option and its constraint, see skip logic on select_multiple.

How do I keep the score when some questions are skipped?

A skipped question is empty, and anything plus empty is not a number, so the whole score disappears. Wrap every question that can be skipped in coalesce(${q}, 0). This livestock example converts animal counts into Tropical Livestock Units (TLU), with cattle at 0.7, goats and sheep at 0.1 and chickens at 0.01:

type	name	label	calculation	relevant	constraint
select_one yes_no	owns_livestock	Does the household own any livestock?
integer	n_cattle	How many cattle?		${owns_livestock} = '1'	. >= 0
integer	n_goats	How many goats or sheep?		${owns_livestock} = '1'	. >= 0
integer	n_chickens	How many chickens?		${owns_livestock} = '1'	. >= 0
calculate	tlu		round((coalesce(${n_cattle}, 0) * 0.7) + (coalesce(${n_goats}, 0) * 0.1) + (coalesce(${n_chickens}, 0) * 0.01), 2)
note	show_tlu	Livestock holding: ${tlu} TLU

◀ ▶surveychoicessettings+

list_name	name	label
yes_no	1	Yes
yes_no	0	No

◀ ▶surveychoicessettings+

A household without livestock gets 0 TLU instead of an empty value, and anything that reads ${tlu} later, such as a follow-up question or a classification, keeps working. The XLSForm calculations hub has more on coalesce().

How do I turn a score into a 0-100 index and a category?

Divide by the maximum possible score, multiply by 100 and round. Then classify the result with if():

type	name	label	calculation
select_one yes_no	wash_latrine	Does the household use an improved latrine?
select_one yes_no	wash_soap	Is there soap and water at the handwashing place?
select_one yes_no	wash_treat	Does the household treat its drinking water?
select_one yes_no	wash_storage	Is drinking water stored in a covered container?
calculate	wash_raw		(${wash_latrine} * 3) + (${wash_soap} * 3) + (${wash_treat} * 2) + (${wash_storage} * 2)
calculate	wash_index		round(${wash_raw} div 10 * 100, 0)
calculate	wash_level		if(${wash_index} >= 80, 'good', if(${wash_index} >= 50, 'fair', 'poor'))
note	show_wash	WASH index: ${wash_index} out of 100 (${wash_level})

◀ ▶surveychoicessettings+

Because Yes is coded 1 and No 0, each answer times its weight is the points it earns, and the maximum is the sum of the weights, here 10. An index on a 0-100 scale is easier to compare across modules with different numbers of questions, and the category can drive a follow-up question in the relevant column, for example ${wash_level} = 'poor'. For more ways to classify with if(), see the XLSForm if statement guide.

Common scoring mistakes and how to fix them

You wrote	Write instead	Error you will see
sum(${q1}, ${q2})	${q1} + ${q2}	cannot handle function 'sum'. Requires 1 arguments but 2 provided.
SUM(${q1})	${q1} + ${q2} (functions are lowercase)	cannot handle function 'SUM'
average(${q1}, ${q2})	(${q1} + ${q2}) div 2	cannot handle function 'average'
(${q1} + ${q2}) / 2	div 2	Unexpected beginning of path in expression
${d1} * 0,5	${d1} * 0.5	Invalid calculate for the bind attached to "${s}" : Bad node
if(${q1} = '5', 1)	if(${q1} = '5', 1, 0)	cannot handle function 'if'. Requires 3 arguments but 2 provided.
${q1} + ${q_2} with no q_2 row	Use the question's exact name	Could not find the name 'q_2'
${q1} + ${q2} when q2 can be skipped	coalesce(${q2}, 0)	No error, but the score is empty
${q1} + ${q2} when "Don't know" is -99	if(${q2} > 0, ${q2}, 0)	No error, but the score drops by 99

The last two upload without complaint, so preview the form and answer it once with a skipped question and once with "Don't know" before you deploy. For other messages, see common XLSForm errors, or paste your form into the free XLSForm validator.

Does this work the same in ODK, KoboToolbox and SurveyCTO?

The expressions are the same on all three. What differs:

Platform	What to know
ODK (XLSForm)	Calculate rows with +, -, *, div, if(), coalesce(), selected() and count-selected(), as above.
KoboToolbox	Same XLSForm syntax. In the Formbuilder, add a Calculate question and type the expression; to make the choice code the score, set each option's XML value to the number.
SurveyCTO	Same functions in calculate fields. Skip logic goes in a column named relevance, not relevant.

On every platform the score is saved as its own column in the export, next to the answers it came from, so you can recheck it in Excel, Stata or R.

Why calculate scores in the form

A score calculated during the interview can drive the rest of it: a poor Food Consumption Score can open a referral question, and a low satisfaction score can ask what went wrong while the respondent is still there. Each score is calculated the same way on every phone, so there is no manual tallying, and supervisors see the result in the first day's data instead of after cleaning.

Build my first form free

Next: XLSForm calculation examples →