# Excel-Style Formulas

Related Online Training modules | |
---|---|

Excel-Style Formulas | |

Generally it is best to access online training from within Q by selecting Help > Online Training |

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). Please tell us if we have missed something you need.
- 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.

## Contents

## Examples

`=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

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 refer to the cell (e.g., SUM).

## See also

A more powerful, but more complicated, approach to creating new variables involves the creation of JavaScript Variables.