FormCalc SST's spreadsheet has the following built-in functions.
In this topic: |
Arithmetic Functions
A set of mathematic and trigonometric functions.
 | In this table a double value means a number which may consist of a whole number part and a fractional part, in contrast to an integer value, which consists only of a whole number. |
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
ABS |
One argument of double type |
=ABS(B6) |
Returns the absolute value. |
ACOS |
One argument of double type |
=ACOS(B16) |
Returns the arccosine. |
ACOSH |
One argument of double type |
=ACOSH(A1) |
Returns the inverse hyperbolic cosine. |
ASIN |
One argument of double type |
=ASIN(B7) |
Returns the arcsine. |
ASINH |
One argument of double type |
=ASINH(B5) |
Returns the inverse hyperbolic sine of the argument. |
ATAN |
One argument of double type |
=ATAN(C5) |
Returns the arctangent. |
ATAN2 |
One argument of double type |
=ATAN2(C3) |
Returns the arctangent using x- and y- coordinates. |
ATANH |
One argument of double type |
=ATANH(D4) |
Returns the inverse hyperbolic tangent. |
CEILING |
Two arguments of double type |
=CEILING(A6,1) |
Rounds the first argument up to the nearest multiple based on the significance specified by the second argument. |
COS |
One argument of double type |
=COS(A5) |
Returns the cosine. |
COSH |
One argument of double type |
=COSH(D4) |
Returns the hyperbolic cosine. |
COUNTIF |
Two arguments: a range of cells and a condition of type string |
=COUNTIF(B3:B7,">2") |
Counts the number of nonblank cells within a range which meet a given condition. |
DEGREES |
One argument of double type that specifies an angle in radians |
=DEGREES(PI()/2) |
Converts radians to degrees. |
EVEN |
One argument of double type |
=EVEN(-1.59) |
Rounds the argument up to the nearest even integer. |
EXP |
One argument of double type |
=EXP(C1) |
Returns the exponent value of the argument. |
FACT |
One nonnegative argument |
=FACT(5) |
Returns the factorial of the argument. |
FLOOR |
Two arguments of double type |
=FLOOR(-2.5,-2) |
Rounds the first argument down, towards zero, to the nearest multiple of the significance specified by the second argument. |
INT |
One argument of double type |
=INT(E4) |
Rounds the argument down to the nearest integer. |
LN |
One argument of double type |
=LN(C1) |
Returns the natural logarithm. |
LOG |
Two arguments: the number and the base |
=LOG(100,10) |
Returns the logarithm of a number to the specified base. |
LOG10 |
One argument of double type |
=LOG10(1000) |
Returns the base-10 logarithm of the argument. |
MOD |
Two arguments: a number and divisor |
=MOD(4,3) |
Returns the remainder after the number is divided by the divisor. |
ODD |
One argument of double type |
=ODD(3.5) |
Rounds the argument up to the nearest odd integer. |
PI |
No arguments required |
=PI() |
Returns the value of Pi. |
POWER |
Two arguments of double type: the number and the power |
=POWER(A1,4) |
Raises the number to the base. |
RADIANS |
One argument of type double which specifies angle in degrees |
=RADIANS(180) |
Converts degrees to radians. |
RAND |
No arguments required |
=RAND() |
Returns a random number between 0 and 1. |
ROUND |
Two arguments: the number of double type and the number of digits |
=ROUND(20.57,1) |
Rounds the first argument to the specified number of digits. |
ROUNDDOWN |
Two arguments of double type |
=ROUNDDOWN(2.75,0) |
Rounds the argument toward zero. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument. |
ROUNDUP |
Two arguments of double type |
=ROUNDUP(-3.42,1) |
Rounds the argument toward infinity. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument. |
SIGN |
One argument of double type |
=SIGN(A2) |
Returns the sign. |
SIN |
One argument of double type |
=SIN(A1) |
Returns the sine. |
SINH |
One argument of double type |
=SINH(1) |
Returns the hyperbolic sine. |
SQRT |
One argument of double type |
=SQRT(B5) |
Returns the square root. |
SUM |
A list of arguments |
=SUM(A1:C12) |
Sums all the values in the list. |
SUMSQ |
A list of arguments |
=SUMSQ(B1:D1) |
Sums the square of values in the list. |
TAN |
One argument of double type |
=TAN(C3) |
Returns the tangent. |
TANH |
One argument of double type |
=TANH(0) |
Returns the hyperbolic tangent. |
TRUNC |
One argument of double type |
=TRUNC(PI()) |
Returns the integer part. (Compare this with the INT function, which returns a double.) |
Statistical Functions
Basic statistical functions.
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
AVERAGE |
A list of arguments |
=AVERAGE(A1:A5) |
Calculates the average value of values within the list. |
AVERAGEA |
A list of arguments |
=AVERAGEA(A1:A5) |
Calculates the average value of the non-empty cells referenced.AVERAGEA(range) equals SUM(range)/COUNTA(range). |
COUNT |
A list of arguments |
=COUNT(A1:A4) |
Returns the count of the number of cells in a given range. |
COUNTA |
A list of arguments |
=COUNTA(A1:A4) |
Counts the number of non-empty cells in a given range. |
COUNTBLANK |
A list of arguments |
=COUNTBLANK(A1:E1) |
Counts the number of empty cells. |
MAX |
A list of arguments |
=MAX(A1:D1) |
Returns the largest argument value. |
MIN |
A list of arguments |
=MIN(A1:D1) |
Returns the smallest argument value. |
SUM |
A list of arguments |
=SUM(A1:C12) |
Sums values within the list. |
SUMSQ |
A list of arguments |
=SUMSQ(B1:D1) |
Sums squares of values within the list. |
Logical Functions
Logical functions take logical values as arguments and return a logical value as a result.
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
AND |
A list of logical arguments |
=AND(1<B4,B4<100) |
Logical AND operation. Returns TRUE if all values within the list are TRUE; returns FALSE if one or more values within the list evaluates to FALSE. |
FALSE |
No arguments required |
=FALSE() |
Returns the logical value FALSE. |
IF |
Three arguments: the logical expression, the value to return if the expression succeeds, the value to return the expression fails |
=IF(A10<=100, "Within budget", "Over budget") |
Returns the second argument if the logical expression evaluates to TRUE and the third argument otherwise. |
NOT |
One argument of logical type |
=NOT(1>D4) |
Logical NOT operation. Reverses the value of its argument. |
OR |
A list of logical arguments |
=OR(A1>=10, A1<=–10) |
Logical OR operation.Returns TRUE if any argument is TRUE; returns FALSE if all values within the list evaluate to FALSE. |
TRUE |
No arguments required |
=TRUE() |
Returns the logical value TRUE. |
Date and Time Functions
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
DATE |
Three operands defining the year, month and day |
=DATE(1900,1,1) |
Calculates the serial number that represents a specified date. |
DAY |
One argument defining the serial number of the desired date |
=DAY(TODAY()) |
Returns the day portion of a given date. |
HOUR |
One argument defining the serial number of the desired date/time value |
=HOUR(NOW()) |
Returns the hour portion of a given date/time value. |
MONTH |
One argument defining the serial number of the required date |
=MONTH(TODAY()) |
Returns the month portion of a given date. |
MINUTE |
One argument defining the serial number of the desired date/time value |
=MINUTE(NOW()) |
Returns the minutes portion of a given date/time value. |
NOW |
No arguments required |
=NOW() |
Returns the current time in general format. You can apply other formatting to display the result as a date, a time, or a mixed date/time. |
SECOND |
One argument defining the serial number of the desired date/time value |
=SECOND(NOW()) |
Returns the seconds portion of a given date/time value. |
TIME |
Three arguments defining hour, minute and second parts of a time value |
=TIME(16,48,10) |
Returns a decimal value for the specified time. |
TODAY |
No arguments required |
=TODAY() |
Returns the serial number of the current date. |
WEEKDAY |
Two arguments: the serial number of the required date, weekday base |
=WEEKDAY(TODAY())
=WEEKDAY(DATE(2002, 12, 1),1) |
Returns the day of the week corresponding to the specified date.
The weekday base identifies the first day of the week and determines the return value type:
1 or omitted: the first day of the week is Sunday. (The function returns 1 for Sunday, 2 for Monday, etc.)
2: the first day of the week is Monday. (The function returns 1 for Monday, 2 for Tuesday, etc.)
3: the first day of the week is Tuesday. (The function returns 0 for Monday, 1 for Tuesday, etc.) |
YEAR |
One argument defining the serial number of the required date |
=YEAR(TODAY()) |
Returns the year portion of a given date. |
Data Management Functions
Special FormCalc SST functions for extracting and storing data.
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
No arguments required |
=GET1() |
The GET functions return the number stored in the memory location corresponding to the numeral at the end of the word GET. Numbers are stored in those locations by a STOR function. For example, if a cell has the formula =STOR7(44.785), then another cell with the formula =GET7() would return the result 44.785 in the second cell.
The memory locations referenced by STOR and GET functions are global across all macros, which means data can be stored in them (STOR) by one macro and accessed (GET) by other macros.
There are several important limitations for using these functions: for details, see Using STOR and GET functions. |
|
One numeric argument |
=GETN(3) |
Returns the numeric value stored in the memory location identified by the argument. See the STORN and GETN functions topic. |
|
Two arguments of type string |
=GETVAL(D5,"Wt") |
Extracts the data value named by the second argument, from the text string defined by the first argument. Data values in the text string must be stored in a specific format. See the GETVAL function reference topic for examples and data storage rules. |
|
|
=STOR1() |
The STOR functions store a number in the memory location corresponding to the numeral at the end of the word STOR. Numbers stored in those locations can be retrieved by a GET function. For example, if a cell has the formula =STOR7(44.785), then another cell with the formula =GET7() would return the result 44.785 in the second cell.
The memory locations referenced by STOR and GET functions are global across all macros, which means data can be stored in them (STOR) by one macro and accessed (GET) by other macros.
There are several important limitations for using these functions: for details, see Using STOR and GET functions. |
|
Two numeric arguments |
=STORN(3,12.34) |
Stores the numeric value passed in the second argument, in the memory location identified by the first argument. See the STORN and GETN functions topic. |
Text Functions
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
& (ampersand) |
Two arguments of type string |
="Yellow "&"river |
Concatenates specified strings. |
CONCATENATE |
A list of strings |
=CONCATENATE("John"," Doe") |
Joins several text strings in one text string. (An alternative to "&".) |
DOLLAR |
Two arguments defining the value and the number of digits to the right of the decimal point in the output string |
=DOLLAR(957.344,2) |
Converts the number to text using currency format $#,##0.00_);($#,##0.00), with the decimals rounded to the specified number of places. |
FIXED |
The first argument of type double is required. The second argument of type integer is optional (the default value is 2). The third argument of type Boolean is optional. |
=FIXED(1234.567,1) |
Rounds the first argument to the number of decimals determined by the second argument and returns it as a string. The third argument (which must be TRUE or FALSE) specifies whether to omit commas in the output string. |
One numeric argument |
=GETT(2) |
Returns the text stored in the memory location identified by the argument. See the STORT and GETT functions topic. |
|
LEFT |
The first argument of type string is required. The second argument of type integer is optional. |
=LEFT(A1)
=LEFT(A1, 3) |
Returns the first character or characters in a text string.
The second argument defines the number of characters to extract. The default value is 1. |
LEN |
One argument of type string. |
=LEN("ABC") |
Returns the length of the string argument. |
LOWER |
One argument of type string. |
=LOWER(A1) |
Converts a string to lowercase. |
MID |
The first argument is of type string, the second and the third arguments are of type integer. |
=MID("ABC",3,1) |
Returns the substring of a given text string. The position of the substring is defined by the second argument. The third argument specifies the number of characters to extract. |
RIGHT |
The first argument of type string is required. The second argument of type integer is optional. |
=RIGHT(B2,3) |
Returns the last character or characters in a text string.
The second argument defines the number of characters to extract. The default value is 1. |
One argument specifying a cell or cell range, plus an (optional) argument specifying a text separator. |
=SUMTXT(D25:D27) |
Returns text values accumulated from all cells in the cell range specified by the first argument, separated by commas. If the optional second argument is provided, the text values will be separated by the second argument's text. See the SUMTXT function topic. |
|
One numeric argument and one argument of type string (text) |
=STORT(2,"John Doe") |
Stores the text passed in the second argument, in the memory location identified by the first argument. See the STORT and GETT functions topic. |
|
The first argument of type double is required. The second argument of type string is optional. |
=TEXTD(A1) =TEXTD(NOW())
=TEXTD(A1, "mm/dd/yyyy") |
Returns a text string for the date defined by the date/time value in first argument, formatted according to the current short date format. (Time is not included.)
When two arguments are supplied, the date/time value defined by the first argument is formatted according to the format defined by the second argument. (See the TEXTD formatting characters.) |
|
TRIM |
One argument of type string. |
=TRIM(" Some text ") |
Removes all spaces from text except for single spaces between words. |
UPPER |
One argument of type string. |
=UPPER(A1&A2) |
Converts a text string to uppercase. |
IS Functions
These functions test the type of a value and return a Boolean result.
Symbol |
Operand Type |
Example |
Explanation |
|---|---|---|---|
ISBLANK |
A value of any type |
=ISBLANK(A1) |
Returns TRUE if cell is empty. |
ISERR |
A value of any type |
=ISERR(H1) |
Returns TRUE if the cell contains any error value except #N/A. |
ISERROR |
A value of any type |
=ISERROR(A7) |
Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). |
ISLOGICAL |
A value of any type |
=ISLOGICAL(C3) |
Returns TRUE if the specified value refers to a logical value. |
ISNA |
A value of any type |
=ISNA(E5) |
Returns TRUE if the cell contains #N/A (value not available) error value. |
ISNONTEXT |
A value of any type |
=ISNONTEXT(D4) |
Returns TRUE if the cell does not contain text. (Returns TRUE for blank cells, also.) |
ISNUMBER |
A value of any type |
=ISNUMBER(A2) |
Returns TRUE if the cell contains a number. |
ISTEXT |
A value of any type |
=ISTEXT(D1) |
Returns TRUE if the specified cell contains text. |