|
![]() |
|
ABS | FRAC | MOD | SIGN |
AVERAGE | INT | MIN | SQRT |
COMBIN | LN | PRODUCT | STDEV |
COUNT | LOG | QUOTIENT | SUM |
EXP | LOG10 | RAND | TRUNC |
FACT | MAX | ROUND | VAR |
COUNTIF | SUMIF |
Math functions perform simple and complex mathematical calculations.
![]() |
Reminder: The arguments in virtually all functions can be either an actual value or a reference to a cell with a value. For example, ABS (C4) returns the absolute value of the number in cell C4. Cell C4 can be a formula as well thus building an interrelationship between many cells in the table. However, be careful to avoid circular references (formulas that refer to cells that ultimately refer to the original cell). |
Negative numbers will be made positive; positive numbers will be returned unchanged.
Syntax
ABS( number )
Arguments
number - any number.
Example
ABS( -4 ) ABS( C4+C5 )
Averages the given expressions (adds up all the expressions and divides the outcome bu the number of expressions added). If an expression evaluates to a range of cells all the values in the range are averaged. Text in ranges are ignored. If an error condition is encountered in a range of cells, the average function errors as well.
Syntax
AVERAGE( expression, {expression}, ... )
Arguments
expression - numbers or references to average.
Example
AVERAGE( 3.14, -2, 2 ) = 1.047 AVERAGE( A2..B5, 200 )
Computes the total number of unique combinations for a defined collection.. Fractional parts are ignored. Negative numbers in cause an error.
Syntax
COMBIN( objects, objectset )
Arguments
objects - the total number of objects in the collection.
objectset - the number of objects that comprise a set.
Example:
COMBIN( 4, 2 ) is 6.
In other words, 4 objects named A, B, C, D could exist in the following two object sets: AB, AC, AD, BC, BD, CD. Thus there are six unique possibilities in the collection.
Counts the number of given expressions. If an expression evaluates to a range of cells all the values in the range are counted. Text in ranges are ignored.
Syntax
COUNT( expression, {expression}, ... )
Arguments
expression - numbers or references to count.
Example
COUNT( 3.14, -2, 2 ) = 3 COUNT( A2..B5, 200 )
Returns an approximate value for e raised to the given power. Where e is the base of the natural logarithms.
Syntax
EXP( number )
Arguments
number - any number.
Example
EXP( 1 ) is 2.71828183. EXP( 2 ) is 7.38905610
Returns the factorial value for the given integer number. The factorial value is given by multiplying the integers between 1 and the given number to each other. FACT(0) is defined as 1.
Syntax
FACT( number )
Arguments
number - any integer (non-fractional) number between 0 and 150.
Example
FACT( 5 ) is 120. That is 1*2*3*4*5 = 120.
Returns the fractional part of a given number.
Syntax
FRAC( number )
Arguments
number - any number.
Example
FRAC( 3.14159 ) is .14159
Returns the integer value closest to the given value. The resulting integer is equal to or less than, but not greater than the given value. If value is positive the fractional portion of the number is truncated. If the value is negative it is rounded down to the next lowest whole number.
Syntax
INT( number )
Arguments
number - any number.
Example
INT( 3.333 ) is 3 INT( -3.333 ) is -4
Tthe natural logarithm for a value.
Syntax
LN( number )
Arguments
number - a positive non-zero number.
Example
LN( 2.71828183 ) is 1.
The natural log of the natural log base e is 1.
Returns the base-x logarithm of the given number where x is base.
Syntax
LOG( number, {base} )
Arguments
number - any positive non-zero number.
base - positive non-zero number for the base of the logarithm. If this value is ommitted, the function assumes a base of 10.
Example
LOG( 100 ) = 2 LOG( 8, 2 ) = 3
Returns the base-10 logarithm of the given number. This function may be slightly more accurate for some values than the general LOG function.
Syntax
LOG10( number )
Arguments
number - any positive non-zero number.
Example
LOG10( 1000 ) = 3
Returns the largest number in a given list of arguments. If an expression references a range of cells, all the cells in the range are compared.
Syntax
MAX( expression, {expression}, ... )
Arguments
expression - numbers or references to compare.
Example
MIN( 3.14, -2, 2 ) = 3.14 MIN( A2..B5, 200 )
Returns the remainder of a fractional division.
Syntax
MOD( number, divisor )
Arguments
number - any number.
divisor - any non-zero number.
Example
MOD( 5, 3 ) = 2
The value 3 goes into 5 one time with a remainder of 2.
Returns the smallest number in a given list of arguments. If an expression references a range of cells, all the cells in the range are compared.
Syntax
MIN( expression, {expression}, ... )
Arguments
expression - numbers or references to compare.
Example
MIN( 3.14, -2, 2 ) = -2 MIN( A2..B5, 200 )
Multiplies the given expressions. If an expression evaluates to a range of cells all the values in the range are multiplied. Text in ranges are ignored. If an error condition is encountered in a range of cells, the product function errors as well.
Syntax
PRODUCT( expression, {expression}, ... )
Arguments
expression - numbers or references to multiply.
Example
PRODUCT( 3.14, -2, 2 ) = -12.56 PRODUCT( A2..B5, 200 )
Returns the quotient of fractional divison.
Syntax
MOD( number, divisor )
Arguments
number - any number.
divisor - any non-zero number.
Example
QUOTIENT( 5, 3 ) = 1
The value 3 goes into 5 one time.
Returns the a random number. The type of random number is determined by the modifier:
Syntax
RAND(modifier)
Arguments
modifier - optional argument to qualify the generation of a random number. By default the function uses 0.
- If modifier < -1 returns integer value between modifier value and -1.
- If modifier >= -1 and modifier < 0 returns negative fractional value between modifier and 0.
- If modifier >= 0 and modifier <= 1 returns fractional value between 0 and modifier.
- If modifier > 1 returns integer values between 1 and modifier.
Example
RAND( -2 ) could be -1 or -2. RAND() could be any value between 0.0 and 1.0.
Returns the quotient of fractional divison.
Syntax
ROUND( number, decimal-places )
Arguments
number - number to round.
decimal-places - how many decimal places to round. Must be an integer.
Example
ROUND( 123.123, 2 ) = 123.12 ROUND( 123.123, -1 ) = 120
Returns -1 if number is negative, 0 if number is 0, and 1 if number is greater than 0.
Syntax
SIGN( number )
Arguments
number - any number.
Example
SIGN( -24.34 ) = -1 SIGN( 0 ) = 0 SIGN( 3.14 ) = 1
Returns the square root of the given number.
Syntax
SQRT( number )
Arguments
number - any positive number.
Example
SQRT( 25 ) = 5
Returns the standard deviation of all the non-empty values in a given list of arguments. If an expression evaluates a range of cells all the non-empty values in the range are examined. The standard deviation is the square root of the variance.
Syntax
STDEV( expression, {expression}, ... )
Arguments
expression - numbers or references to examine.
Example
STDEV( 10, 20, 20 ) is 5.774. STDEV( 10, 20, 20 ) is 5.774.( A2..B5, 200 )
Adds (sums) the given expressions. If an expression evaluates to a range of cells all the values in the range are summed. Text cells in ranges are ignored. If an error condition is encountered in a range of cells, the sum function errors as well.
Syntax
SUM( expression, {expression}, ... )
Arguments
expression - numbers or references to sum.
Example
SUM( 3.14, -2, 2 ) is 3.14 SUM( A2..B5, 200 )
Returns the integer portion of the given number dropping any fractional portion.
Syntax
TRUNC( number )
Arguments
number - any number.
Example
TRUNC( 9.99 ) = 9 TRUNC( -9.99 ) = -9
Computes the variance of all the non-empty values in a given list of arguments. If an expression evaluates a range of cells all the non-empty values in the range are examined.
Syntax
VAR( expression, {expression}, ... )
Arguments
expression - numbers or references to examine.
Example
VAR( 3.14, -2, 2 ) is 33.333. VAR( A2..B5, 200 )
Counts the number of cells that match the specified criteria.
Syntax
COUNIF(range, criteria)
Arguments
range - Range of cells to be counted. criteria - Expression that defines which cells will be added.
Example
COUNTIF(A3:A6, "oranges")
Adds all the specified cells that match the specified criteria.
Syntax
SUMIF(range, criteria, sum_range)
Arguments
range - Range of cells to be evaluated. criteria - Expression that defines which cells will be added. sum_range - The actual cells to sum.
Example
SUMIF(A1:A4, ">250000", B1:B4)
Copyright © 1999, Gobe Software, Inc |