## Introduction

If you're familiar with Microsoft Excel's logic, you can use Q to create new variables using formulas that are based on the formulas used by Excel.

That said, Excel-Style variables can be slower and less simple to work with. Often a better route is to create a new variable using JavaScript. To learn more, see: How to Use JavaScript in Q.

Excel-Style Formulas are created by selecting **Create > Variables and Questions > Excel-Style Formula(s)** and choosing either **Numeric** to create numeric variables or **Text** to create text variables.

Q lets you create new variables using formulas that are based on the formulas used by Microsoft Excel. These formulas differ from Excel-style formulas as follows:

- When you create your new formula you must decide whether it will make a variable containing text values or a variable containing numeric values.
- You enter just one formula to create a new variable. Your formula will be executed once for each case in your data file.
- Instead of referring to cells (e.g.
`A3`) or ranges (e.g.`A4:B8`), you refer to Q variables (e.g.`Q3`). - Only a subset of Excel functions are available (see below).
- If any input to SUM or a like function is a missing value then the result will also be a missing value. If you want to treat a missing value as (for example) a zero then you can use code like this:

`=SUM(IF(ISNAN(Q1),0,Q1), IF(ISNAN(Q2),0,Q2))`

Warning: If you are calculating with more than 10,000 cases then you should use JavaScript Variables because Excel-style formulas will be too slow.

## Requirements

Familiarity with Excel's formulas

A data file loaded into a Q project

## Method

Excel-Style Formulas are created by selecting

**Create > Variables and Questions > Excel-Style Formula(s)**and choosing either**Numeric**to create numeric variables or**Text**to create text variables.

In this example we calculate the average among educ, paeduc, maeduc, speduc- Click the green check box
- Select the
**Variables and Questions**tab and specify a**Name**and**Label**for the new variable

The results are as follows:

## Examples

Refer to specific variables names in your Excel-style formulas as in the examples below (Q2, Q7, etc.): `=Q2+Q7=1-AVERAGE(Q3,Q4,Q5)=IF(Q1="M", 1, 2)`

## Available Functions

You can use the following Excel-like functions: AND, AVERAGE, COUNT, DATE, DATEDIF, DATEVALUE, DATEVALUEISO, DATEVALUEUS, DAY, EXP, FIND, HOUR, IF, ISERR, ISERROR, ISNA, ISNAN, LEFT, LEN, LN, MAX, MID, MIN, MINUTE, MONTH, NA, NAN, NOT, OR, REPLACE, RIGHT, SEARCH, SECOND, STDEV, STDEVP, SUM, TIME, TIMEVALUE, TRIM, VAR, VARP, WEEKDAY, YEAR

## Missing Data

If any input to SUM or a similar function is a missing value, then the result will also be a missing value. `To detect if a value is missing, use ``ISNAN(variable name)`. To return a missing value, use the `NAN()` function, for example:

`=IF(ISNAN(Q1),NAN(),Q1*2)`.

This will return NaN when the Q1 variable has missing data, and return twice the value of Q1 otherwise.

## Warning regarding missing data

In Excel, blank cells are ignored by most formulas. In Q, a blank cell in the **Data** tab represents missing data and many formulas will return a NaN if they refererence the cell with missing data (e.g.,SUM).

Next

How to Work with Conditional R Formulas

## Comments

0 comments

Article is closed for comments.