Excel-Style Formulas

From Q
Jump to navigation Jump to search
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).

See also

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