Mathematical computed column functions¶

This section provides syntax and examples for the mathematical computed column functions you can use with the Data Prep Compute tool. For statistical functions, see Statistical computed column functions.

To use a math function, you must store the value as a numeric data type. Numeric data types can be identified by the numeric data type icon in the header row. If the value is not stored as a numeric data type, convert the value to numeric format using the VALUE function.

In addition to the mathematical functions listed in this section, the following standard mathematical operations are supported:

• Multiply, divide a column by any number.

• Add to, subtract from a column by any number

Examples using column name "Revenue":

@Revenue@ * 100

@Revenue@ / 100

@Revenue@ + 100

@Revenue@ - 100

ABS¶

Returns the absolute value (ABS) of a real number.

In mathematical notation, absolute value is indicated with a bar on either side. For example, the absolute value of x is written as |x|.

Syntax

ABS(VALUE)

VALUE is the value you want to find the absolute value of.

Example

ABS(@Column@)

Notes on use

The VALUE you provide must be a real number, a column that contains a real number, or a function that returns a real number.

ABS can be thought of as the distance from zero a given number has on a number line. For ABS, positive and negative do not matter. The number's distance from zero is the same, or absolute, regardless of whether the number is to the right of zero (positive) or to the left of zero (negative). In mathematical notation, absolute values is indicated with a bar on either side. For example, the absolute value of x would be written as |x|.

CEILING¶

Returns a given number rounded up to whole number.

Syntax

CEILING(VALUE)

VALUE is the value you want to round.

Example

CEILING(@Column A value@)

EXP¶

Returns the exponential for the specified value.

Syntax

EXP(NUMBER)

NUMBER is any real number.

Example

EXP(@Column A value@)

FACTORIAL¶

Returns the product of an integer and all the integers below it.

Syntax

FACTORIAL(NUMBER)

NUMBER is any real number.

Example

FACTORIAL(@Column A value@)

FLOOR¶

Returns a given number rounded down to whole number.

Syntax

FLOOR(VALUE)

VALUE is the value you want to round.

Example

FLOOR(@Column A value@)

INT¶

Rounds a real number down to the next integer that is less than or equal to it.

Syntax

INT(VALUE)

VALUE is the real number you want to round down.

Example

INT(@Column@)

Notes on use

The VALUE you provide must be a real number, a column that contains a real number, or a function that returns a real number.

LN¶

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). This provides the same output as Excel's LN function.

Syntax

LN(NUMBER)

NUMBER is positive real number for which you want the natural logarithm.

Example

LN(@Column A value@)

LOG¶

Returns the logarithm of a number to the base you specify. This function provides the same output as Excel's LOG function.

Syntax

LOG(NUMBER,BASE)

• NUMBER is positive real number for which you want the natural logarithm.
• BASE is the base of the logarithm.

Example

LOG(@Column A value@,2)

LOG10¶

Returns the base-10 logarithm of a number. This function provides the same output as Excel's LOG10 function.

Syntax

LOG10(NUMBER)

NUMBER is positive real number for which you want the natural logarithm.

Example

LOG10(@Column A value@)

MOD¶

Returns the remainder after number is divided by divisor. The result has the same sign as divisor. This provides the same output as Excel's MOD function.

Syntax

MOD(MUMBER,DIVISOR)

• NUMBER is any real number.
• DIVISOR is any real number.

Example

MOD(@Column A value@,3)

POWER¶

A Math/Trigonometric function that computes and returns the result of a number raised to a power. This function provides the same output as Excel's POWER function.

Syntax

POWER(NUMBER, POWER)

• NUMBER is the base number that is any real number.
• POWER is the exponent, any real number, to which the base number is raised.

Example

POWER(@Column A value@,3)

ROUND¶

Rounds a number to the number of decimal places you specify.

Syntax

ROUND(VALUE, PLACES)

• VALUE is the real number you want to round.
• PLACES is the number of decimal places to round to.

Example

ROUND(@Column@, 2)

Notes on use

The VALUE you provide must be a real number, a column that contains a real number, or a function that returns a real number.

The PLACES value must be a positive integer (not negative or a contain decimal). Values with less decimal places than what you specified won't be affected by the ROUND function.

ROUNDDOWN¶

Rounds a number down to the number of decimal places you specify.

Syntax

ROUNDDOWN(VALUE, PLACES)

• VALUE is the real number you want to round down.
• PLACES is the number of decimal places to round down to.

Example

ROUNDDOWN(@Column@, 3)

Notes on use

ROUNDDOWN is similar to ROUND except that it always rounds down.

The VALUE you provide must be a real number, a column that contains a real number, or a function that returns a real number.

The PLACES value must be a positive integer (not negative or contain a decimal). Values with fewer decimal places than what you specified won't be affected by the ROUNDDOWN function. If zero decimal places are specified, ROUNDDOWN operates like the INT function.

ROUNDPERC¶

Rounds a real percentage value (between -100 and 100) to the number of decimal places you specify, ensuring that values near 0% and ±100% have at least the number of decimal places you specify and as many, but no more, decimal places to distinguish the value from true 0% or ±100% values.

Syntax

ROUNDPERC(VALUE, PLACES)

• VALUE is the real percentage value you want to round.
• PLACES is the minimum number of decimal places to round to.

Example

ROUNDPERC(@Column@, 2)

Notes on use

The VALUE you provide must be a real percentage value between -100 and 100, a column that contains a real percentage value, or a function that returns a real percentage value.

The PLACES value must be an integer (no decimals). Values with fewer decimal places than what you specified won't be affected by the ROUNDPERC function.

ROUNDUP¶

Rounds a number up to the number of decimal places you specify.

Syntax

ROUNDUP(VALUE, PLACES)

• VALUE is the real number you want to round up.
• PLACES is the number of decimal places to round up to.

Example

ROUNDUP(@Column@, 3)

Notes on use

ROUNDUP is similar to ROUND except that it always rounds up.

The VALUE you provide must be a real number, a column that contains a real number, or a function that returns a real number.

The PLACES value must be a positive integer (not negative or a contain decimal). Values with fewer decimal places than what you specified won't be affected by the ROUNDUP function.

SIGN¶

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. This provides the same output as Excel's SIGN function.

Syntax

SIGN(NUMBER)

NUMBER is any real number.

Example

SIGN(@Column A value@)

SQRT¶

Returns a positive square root. This function provides the same output as Excel's SQRT function.

Syntax¶

SQRT(NUMBER)

NUMBER is any positive number for which you want to calculate the square root.

Example

SQRT(@Column A value@)

SUM¶

Adds the given numeric values together.

Syntax

SUM(VALUE_1, [VALUE_2, ...])

• VALUE_1 is the first value.
• VALUE_2, ... [optional] are the additional values.

Example

SUM(@Column_A@, @Column_B@, @Column_C@)

Notes on use

The VALUE you provide must be a numeric value, a column that contains numeric values, or a function that returns a numeric value.