Mathematical functions

The following mathematical functions are provided by PipeDream.

ABS

ABS(number)

Returns the absolute positive value of number.

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

EXP(number)

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

FACT

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! = n × (n - 1) ... × 2 × 1, i.e. n! = Πi=n i

If the result is too big to fit in an integer it becomes floating point.

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

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

INT(number)

Returns the integer part of number, rounding is towards zero. This behaviour is consistent with earlier versions of PipeDream, Resultz and Fireworkz and Lotus 1-2-3. If number is negative, it does not give the result as Microsoft Excel, which rounds towards -infinity, like the FLOOR() spreadsheet function.

LN

LN(number)

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

LOG

LOG(number)

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

LOG(number, b:number)

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

MOD

MOD(a, b)

Gives the remainder after division of a by b.

ROUND

ROUND(number, x)

Rounds number to x places.

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

For 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 Ctrl-EIC.

2. Use ROUND and the Replicate Down command (Ctrl-BRD) to create a new column of data.

3. Mark the block and use the Snapshot command (Ctrl-BSS)to fix the numbers permanently in place.

4. Use the new column in your calculations.

SGN

SGN(number)

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

SQR

SQR(number)

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

SUM

SUM(list)

Sum of the values in list.