What happens if one of the fields in a calculation is blank?

Is your calculate field not working like you would expect? The issue might be that one or more fields in your calculation might be blank; if that happens the calculation is invalid and the calculate field just returns a missing value. Empty fields referred to in a calculation are not automatically treated as 0.

For example, let's say:

1) You have a survey module in which you ask respondents whether they own particular assets and, if so, what the value of each asset is.

2) You've set up your survey such that, if a respondent does not own a particular asset, you do not ask about the value of that asset.

3) After the series of questions on assets, you have a calculate field with a calculation that sums up the value of all assets in the household. The calculation might look something like:

${assetvalue1} + ${assetvalue2} + ${assetvalue3} + ${assetvalue4}

In the above set up, it's possible that some of the fields referred to in the calculation are empty. In that situation, the calculation will not work; it will not treat the missing fields as equal to 0. It will just return a missing value, even if some of the other asset value fields do have answers.

The solution

To treat any missing values as 0 and make sure calculation works in this scenario, for each field being used in the calculation that might have a missing value you will need to create another calculate field that uses the if() function to either be 0, if the field is empty, or equal to the value of the field. E.g.:

if(${assetvalue1} = null, 0, ${assetvalue1})

Then in your main calculate field, which has the calculation that sums up the asset values, you should sum up these individual calculate fields rather than the original answers directly. E.g.:

${assetvalue1_calc} + ${assetvalue2_calc} + ${assetvalue3_calc} + 

More condensed expressions

Strictly, there's no reason to have separate calculate fields as above with if(). Separate calculate fields can help with troubleshooting and to help avoid overly long expressions which can be taxing on device RAM. The above could also be expressed as:

if(${assetvalue1} = null, 0, ${assetvalue1}) + 
if(${assetvalue2} = null, 0, ${assetvalue2}) +
if(${assetvalue3} = null, 0, ${assetvalue3}) + 
if(${assetvalue4} = null, 0, ${assetvalue4})

More help

If you are still unclear about how calculate fields handle blank values, or you have a calculation that is not working the way you want it to for other reasons, and you are a supported SurveyCTO user, don't worry. If you are a user on a paid SurveyCTO server with access to support, you can submit a support request from the My requests link in the top right of this page. Someone from our team will help you investigate further. Please just make sure to attach an Excel copy of your form.

If you're a community user and so don't have professional support, you can post the issue, along with your Excel form, to the community forum for assistance from other community members.

Functions and operators referenced in this article: if(), +. For a full list of SurveyCTO functions and operators, please read this help topic.

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.


Article is closed for comments.