Mathematical functions

The following mathematical functions are provided by PipeDream.

ABS

Syntax:

ABS(number)

Returns the absolute positive value of number.

CEILING

Syntax:

CEILING(number {, multiple:number})

Rounds number to the given multiple towards +infinity (or away from zero, if both are negative). If multiple is omitted, 1 is used.

EXP

Syntax:

EXP(number)

Returns the constant e (2.71828184..) raised to the specified power.

FACT

Syntax:

FACT(n)

This function yields the factorial of n (most commonly shortened to n!).

n must be an integer greater than or equal to zero.

The formula used is:

n! = Πi=n i

i.e.

n! = n × (n - 1) ... × 2 × 1

For very large n this is an approximation achieved using logarithms.

FLOOR

Syntax:

FLOOR(number {, multiple:number})

Rounds number to the given multiple towards -infinity (or towards zero, if both are negative). If multiple is omitted, 1 is used.

INT

Syntax:

INT(number)

Returns the integer part of number.

The result is rounded towards zero. This behaviour is consistent with earlier versions of PipeDream, Lotus 1-2-3, Resultz and Fireworkz.

If number is negative, PipeDream does not give the result as LibreOffice and Microsoft Excel, which round towards -infinity, like the FLOOR() spreadsheet function.

LN

Syntax:

LN(number)

Returns the natural logarithm of number. The number must be positive.

LOG

Syntax:

LOG(number)

Returns the logarithm to base 10 of number. The number must be positive.

Syntax:

LOG(number, b:number)

Returns the logarithm to base b of number. The number must be positive.

MOD

Syntax:

MOD(dividend, divisor)

Gives the remainder after division of dividend by divisor.

The returned value has the same sign as dividend and is less than divisor in magnitude. PipeDream computes the quotient, and hence the remainder, using truncated division. This behaviour is consistent with earlier versions of PipeDream, Lotus 1-2-3, Resultz and Fireworkz.

If divisor is negative, PipeDream does not give the result as LibreOffice and Microsoft Excel.

The modulo relation is satisfied:

n = d × q + r

where n is the dividend, d is the divisor, q is the quotient given by INT(n/d) and r is the remainder given by MOD(n,d).

ROUND

Syntax:

ROUND(number, x)

Rounds number to x places.

Example:

ROUND(12345.678,2)

returns 12345.68.

ROUND(12345.678,-2)

returns 12300.

ROUND can be used to help the inaccuracy problems often encountered in accountancy.

Example:

If you have 1.6 in cells A1 and A2, adding them together gives the result 3.2, but if all the numbers are being displayed without decimal places, it will look as though adding 2+2=3. This is because all calculations are performed to the greatest accuracy, but the display of numbers is rounded. If you use ROUND(A1,0) and ROUND(A2,0), PipeDream will return 2 from both of these; therefore, what appears on the screen will look consistent even though it may not match the original data.

The Snapshot command is useful with ROUND. If you have a column of numbers and you want to have them rounded to a particular degree of precision:

  1. Insert a new column using Cmd-EIC.
  2. Use ROUND and the Replicate Down command (Cmd-BRD) to create a new column of data.
  3. Mark the block and use the Snapshot command (Cmd-BSS)to fix the numbers permanently in place.
  4. Use the new column in your calculations.

SERIESSUM

Syntax:

SERIESSUM(x:number, n:number, m:number, coefficients:array)

Returns the sum of the given power series for x.

n is the initial power to which x is raised, this being multiplied by the first coefficient in the array. For each subsequent coefficient, m is added to the power.

= a1·xn + a2·x(n + m) + a3·x(n + 2m) + …

Revisions:

Added in 4.56.

SGN

Syntax:

SGN(number)

Returns -1, 0, or 1 depending on whether number is negative, zero, or positive, non-zero respectively.

SQR

Syntax:

SQR(number)

Returns the positive square root of number. The number cannot be less than zero.

SUM

Syntax:

SUM(list)

Sum of the values in list.