Math Functions

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).

ABS

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 )

AVERAGE

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 )

COMBIN

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.

COUNT

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 )

EXP

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

FACT

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.

FRAC

Returns the fractional part of a given number.

Syntax

FRAC( number )

Arguments

number - any number.

Example

FRAC( 3.14159 ) is .14159

INT

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

LN

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.

LOG

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

LOG10

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

MAX

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 )

MOD

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.

MIN

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 )

PRODUCT

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 )

QUOTIENT

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.

RAND

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.

ROUND

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

SIGN

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

SQRT

Returns the square root of the given number.

Syntax

SQRT( number )

Arguments

number -  any positive number.

Example

SQRT( 25 ) = 5

STDEV

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 )

SUM

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 )

TRUNC

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

VAR

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 )

COUTIF

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")

SUMIF

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