# 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).
• 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.

## 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).