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 using the Data Table export format, 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.
- Click Tools on the right side.
- Select Create New 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.
Using the Correct Syntax
- ( 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.
|Addition||+||x + y|
|Subtraction||–||x – y|
|Multiplication||*||x * y|
|Division||/||x / y|
|Powers (x^2)||^||x ^ y|
|Absolute Values||abs( )||abs(x)|
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