|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). 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:
=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