Mathematical functions

The following mathematical functions are provided by PipeDream.

ABS

Syntax:

ABS(number)

Returns the absolute positive value of number.

BASE

Syntax:

BASE(number, radix:Number {, minimum_length:Number})

Converts a number using the given radix (base).

If radix is greater than ten, letters in the range A to Z are used to represent ten upwards.

If minimum_length is specified, zeros are used to pad the conversion at the left.

Revisions:

This function was added in 4.57.

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.

Revisions:

This function was added in 4.53.

DECIMAL

Syntax:

DECIMAL(number, radix:Number)

Converts a number in a given base to its corresponding decimal value.

number is a text string of digits in the given radix. Alternatively, a number may be supplied which is similarly treated as a string of digits - these must be in the given radix.

radix must be greater than or equal to two and less than or equal to thirty six.

If radix is greater than ten, letters in the range A to Z (or a to z) are used to represent ten upwards.

If radix is two (binary), an optional suffix B (or b) may be supplied.

If radix is sixteen (hexadecimal), an optional prefix 0x or an optional suffix H (or h) may be supplied.

Examples:

DECIMAL("00001000", 16)

returns the number 4096.

DECIMAL("1010B", 2)

returns the number 10.

DECIMAL(377, 8)

returns the number 255.

Revisions:

This function was added in 4.57.

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.

Revisions:

This function was added in 4.53.

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.