How to Create Excel-Style Formulas

Introduction

If you're familiar with Microsoft Excel's logic, you can use Q to create new variables using formulas that are based on the formulas used by Excel. 

That said, Excel-Style variables can be slower and less simple to work with. Often a better route is to create a new variable using JavaScript. To learn more, see: How to Use JavaScript in Q.

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.

Requirements

Familiarity with Excel's formulas

A data file loaded into a Q project

Method

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.
  In this example we calculate the average among educ, paeduc, maeduc, speduc
  
  
  
• Click the green check box
• Select the Variables and Questions tab and specify a Name and Label for the new variable
  
  The results are as follows:
  
  

Examples

Refer to specific variables names in your Excel-style formulas as in the examples below (Q2, Q7, etc.): 

=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

If any input to SUM or a similar function is a missing value, then the result will also be a missing value. 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 refererence the cell with missing data (e.g.,SUM).

Next

How to Work with Conditional R Formulas

How to Work with Conditional JavaScript Formulas

How to Use JavaScript in Q