|Related Online Training modules|
|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:
=IF(Q1="M", 1, 2)
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
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).