Formula Variable Creation
About Formula Variable Creation
It’s easier to ask your respondents for their height and weight than to ask them to guess their BMIs (Body Mass Indexes). But if you’re at the end of your data collection and realize you forgot to add a math operation for BMI to your embedded data, is there still a way Qualtrics can calculate this variable for you?
Formulas are a type of custom variable that allow you to create formulas with your survey’s variables. Values assigned with a formula variable will be applied to both collected and future data, making it an alternative to embedded data in situations where you need a variable to be added retroactively. Once created, formula variables can be used in the Data and Weighting sections of the Data & Analysis tab and in the Results section of the Reports tab. They can also be exported with your dataset or be retrieved by API if you use Create Response Export, Get Response Export Progress, and Get Response Export File calls.
Need to make changes to an existing formula? See our Editing Custom Variables page.
Creating a Formula
- Go to the Data & Analysis tab.
- Select Field Editor.
- Select Create Field.
- Select Formula at the top.
- Name your formula variable.
- Type out your equation in the box. The green checkmark to the left of the box will become a red warning sign if your equation doesn’t make sense.
Qtip: Every formula starts off with x and y, but you can add new variables by typing new letters into the box, and remove variables by deleting the corresponding letter from the formula box.
- Set x and y equal to a question, survey metadata, or embedded data.
- When finished, click Create Variable.
- Click Review and Publish to save your changes.
- Click Publish your Changes.
Qtip: Click Data Table to return to your data table.
Using the Correct Syntax
A formula has to make sense before it can be added to your data. Here are some guidelines to follow to ensure your formulas aren’t plagued by red triangles:
- ( x + y ) / 2 specifies that x and y must be added together before they are then divided by two.
- Always remember to close parentheses. ( x + y ) will work, but x + y ) won’t.
- Numbers, single-letter variables, and the symbols listed in the table below are compatible with formulas. However, combined letters, like xy, are not. Instead, use x * y.
- Spacing has no impact on the effectiveness of a formula. x/(y^2) and x / ( y ^ 2 ) will work the same way.
Available Operations
Math Operation | Symbol | Example |
Addition | + | x + y |
Subtraction | – | x – y |
Multiplication | * | x * y |
Division | / | x / y |
Powers (x^2) | ^ | x ^ y |
Absolute Values | abs( ) | abs(x) |
Ceiling | ceil( ) | ceil(x) |
Floor | floor( ) | floor(x) |
Compatible Variables
Not all variables can be used in a formula. Compatible items must be set to accept numeric values (or be finite in nature) by using recode values to determine the numeric value assigned to each answer choice. This includes:
- Any question with a multiple choice option
- Questions set to numeric-only answers, such as sliders and constant sum questions
- Text entry questions set to numeric validation
- Embedded data set as number or number set types
- Survey metadata for response type, progress, duration (in seconds), and finished status
Projects Where You Can Create These Custom Fields
Data & Analysis is available in many different types of projects. The options described on this page are available in:
- Survey projects
- XM Solutions
- Imported data projects
- 360 projects
Qtip: See Creating New Fields (360) for more details.
- Conjoint
- MaxDiff
While the functionality is technically available, we do not recommend using these custom fields with other types of Employee Experience projects. See Creating New Fields (EX) for more details.