[Back]  [Print Page]
Formula Commands

The following are the commands that can be used in Conditional Statements and Formulas.

Note:  For information about adding formulas to your surveys, see Formulas.

IF(expression, statement1, statement2)
Show statement1 or statement2 based on the value of expression.

Example: IF([Q1]>0, "The value is positive", "The value is negative") <-- Shows one of two text strings based on Question 1 value

substring(text, offset, len)
Extract a portion of text, starting at offset (first character is 0) and of length len.

Example: substring([Q1], 0, 2) <-- Returns the first two characters of Question 1

find(text, substring, start)
Return the offset position of substring within text, starting at offset start (if not specified defaults to 0).  If substring is not found, returns -1.

Example: find([Q1], "@", 0) <-- Returns the position of the first @ character in Question 1

len(text)
Returns the number of characters in text.

Example: len([Q1])

lower(text)
Returns text converted to all lowercase.

Example: lower([Q1])

upper(text)
Returns text converted to all uppercase.

Example: upper([Q1])

abs(x)
Returns the absolute value of x.

Example: abs([Q1])

ceil(x)
Returns the value x rounded up to the nearest integer.

Example: ceil([Q1])

floor(x)
Returns the value x rounded down to the nearest integer.

Example: floor([Q1])

round(x, n)
Returns the value x rounded to the nth decimal place.  If n is not specified it defaults to 0.

Example: round([Q1])

min(x1, x2, ...)
Returns the minimum of a set of values x1, x2, etc.

Example: min([Q1], [Q2], [Q3])

max(x1, x2, ...)
Returns the maximum of a set of values x1, x2, etc.

Example: max([Q1], [Q2], [Q3])

date(number)
Returns the current date in the style specified by number

Style Description Example
0 dddd, MMMM dd, yyyy Thursday, June 08, 2000
1 MMMM dd, yyyy June 08, 2000
2 dddd, dd MMMM, yyyy Thursday, 08 June, 2000
3 dd MMMM, yyyy 08 June, 2000
4 M/d/yy 6/8/00
5 M/d/yyyy 6/8/2000
6 MM/dd/yy 06/08/00
7 MM/dd/yyyy 06/08/2000
8 yy/MM/dd 00/06/08
9 yyyy-MM-dd 2000-06-08
10 dd-MMM-yy 08-Jun-00

Example:
date(5) = 4/8/2011

todate(text)
Converts any text string into a date and time serial number. Returns serial number of the specified date and time. If todate cannot interpert the string, then it returns the string unchanged.

Examples:  
todate(“1992,3,12”) = 211567161600
now-todate([Q1])<18yr<-- Age verification for question Q1.

tostring(x, format)
Returns value x formatted as defined by the format flags.  The following format flags can be used:
FMT_D0 - Round to 0 digits
FMT_D1 - Round to 1 digit
...
FMT_ZER - Suppress trailing zeros
FMT_PCT - Percent
FMT_SEP - 000's separator
FMT_DLR - Current symbol
FMT_PRN - Parentheses on negative numbers

Example: tostring([Q1], FMT_SEP+FMT_DLR)

tolist(text, sep)
Splits text into a list of substrings on the delimeter sep.

Example: tolist([Q1], "@")[1] <-- Returns the domain part of an email address.

count(list)
Returns the number of elements in list.

Example: count(tolist([V1],",")) <-- Returns the number of items selected in question 1.