`ABS(Number)`

## Arguments

Argument | Data type | Description |

Number | Number | The number to return the absolute value of. |

The ABS function returns a number.

## Excel equivalent

## Examples

Formula | Result |

`ABS(-20)` | 20 |

`ABS(20)` | 20 |

`ABS(0)` | 0 |

You could use DIVIDE to determine the average monthly bonus of your sales team.

## Syntax

`DIVIDE(Dividend, Divisor)`

## Arguments

Argument | Data type | Description |

Dividend | Number | The number to divide. |

Divisor | Number | The number to divide by. |

## Syntax example

`DIVIDE(Commission, 7)`

Lee | Smith | Cho | Ako | Khan | Knor | Patel | |

Sales Revenue | 50,000 | 68,000 | 62,500 | 88,000 | 98,600 | 92,000 | 74,230 |

Commission Ratio | .05 | .05 | .08 | .07 | .05 | .06 | .07 |

Commission | 2,500 | 3,400 | 5,000 | 6,160 | 4,930 | 5,520 | 5,196 |

Ave. monthly commission | 208.3 | 283.3 | 416.6 | 513.3 | 410.8 | 460 | 433 |

## Excel equivalent

## Detailed Examples

Result | |

`DIVIDE` `(50,5)` | 10 |

`DIVIDE` `(750,000,200)` | 3,750 |

`DIVIDE` `(30,0)` | Infinity |

`DIVIDE` `(-45,0)` | -Infinity |

This function is the inverse of the LN function, which returns the natural logarithm of a number.

## Syntax

`EXP(Number)`

## Arguments

Argument | Data type | Description |

Number | Number | The power to raise e to. |

The EXP function returns a number.

## Excel equivalent

## Examples

This example shows the result of the EXP function when used with the numbers two through five.

The values in this example are rounded to eight significant digits. You can change the number of digits that display in Anaplan under **Format** in Blueprint.

Item 1 | Item 2 | Item 3 | Item 4 | |

n | 2 | 3 | 4 | 5 |

EXP result
| 7.3890561 | 20.085537 | 54.6 | 148.4 |

For example, you can use the FIRSTNONZERO function to avoid complex conditional formulas you would otherwise have to use to determine the first non-zero value in a collection of numbers.

The two formulas below are equivalent:

`FIRSTNONZERO(a, b, c)`

`IF a <> 0 THEN a ELSE IF b <> 0 THEN b ELSE IF c <> 0 THEN c ELSE 0`

## Syntax

`FIRSTNONZERO(Value 1, Value 2, [etc.])`

## Arguments

Argument | Data type | Description |

Value | Number | The FIRSTNONZERO function assesses each instance of this argument and returns the first value that is not zero. This argument can be given multiple times. You must provide a minimum of two values for the FIRSTNONZERO function to compare. The FIRSTNONZERO function assesses values in the order they're provided as arguments. |

The FIRSTNONZERO function returns a numeric result.

## Examples

In the example below, five line items that contain numeric values display on rows, named *a* through *e*. The Time dimension displays on columns.

Two line items contain formulas that demonstrate the FIRSTNONZERO function.

The *Alphabetical order* line item searches each line item for a non-zero value in alphabetical order, as the arguments are provided to the FIRSTNONZERO function in that order. The opposite is true for the formula in the *Reverse alphabetical order* line item.

Jan 22 | Feb 22 | Mar 22 | |

a | 5 | 0 | 0 |

b | 3 | 0 | 0 |

c | 12 | 2 | 7 |

d | 4 | 2 | 0 |

e | 56 | 2 | 4 |

Alphabetical order
| 5 | 2 | 7 |

Reverse alphabetical order
| 56 | 2 | 4 |

You can use the natural logarithm (LN) to work out the length of time it takes to achieve a unit of growth, such as with compound interest.

LN returns the natural logarithm of a number, based on the constant *e*. This function is the inverse of the EXP function, which raises e to the nth power.

In terms of measuring growth:

- EXP allows you to enter time in order to work out growth
- LN allows you to enter growth in order to work out the time it would take to achieve that growth

## Syntax

`LN(Number)`

## Arguments

Argument | Data type | Description |

Number | Numeric line item, property or expression | Number you want to return the natural logarithm for |

**Syntax example**

Here's an example of how to use LN to find out how long it will take to achieve a specific amount of growth based on compound interest.

- If an investment grows at a rate of 10% per annum how long will it take for the investment to reach a specific amount?

To calculate this, use this syntax:

`LN(total amount after growth/current amount )/LN(1 + percentage growth rate represented as a multiplier e.g. 1.10 for 10% in this instance)= Time to hit the specific amount`

## Constraints

You can only use LN with positive numbers.

## Excel equivalent

**Example**

Using the example above, let's work out how long it will take for an initial investment of $25,000 to reach $65,000.

LN(total amount after growth/current amount )/LN(1 + interest rate represented as a multiplier)= Time in years to hit the specific amount

LN($65,000/$25,000)/LN(1 + 1.10)=10.0252821576 (approximately 10 years).

Initial investment | $25,000 |

Investment goal | $65,000 |

Annual percentage rate (APR) | 10% |

Time to hit goal (using LN)
| 10 years |

This function is the inverse of the POWER function.

For example, if the formula `POWER(a, b)`

gives a result of c, the formula `LOG(c, a)`

gives a result of b.

## Syntax

`LOG(Number, Base)`

## Arguments

Argument | Data type | Description |

Number | Number | The number to return the logarithm of. |

Base (optional) | Number | The base to apply when returning the logarithm. If omitted, the LOG function uses a default base of 10. |

## Calculation engine functionality differences

In Polaris, the LOG function returns a value of *NaN* (Not a Number) if you use positive infinity for the *Base* argument.

In the Classic Engine, the LOG function returns 0 if you use positive infinity for the *Base* argument.

## Excel equivalent

## Examples

### General examples

In this example, the *Number* and *Base* line items each contain four numeric values to be used for the *Number* and *Base* arguments respectively. The other two line items contain formulas to calculate the logarithm for the numbers.

As the formula for the *Logarithm base 10* line item does not contain the *Base* argument, the function returns the base 10 logarithm by default.

The values in this example are rounded to eight significant digits. You can change the number of digits that display in Anaplan under **Format** in Blueprint.

Item 1 | Item 2 | Item 3 | Item 4 | |

Number | 14.25 | 234.56 | 35.00 | 456.78 |

Base | 8 | 6 | 9 | 3 |

Logarithm with various bases
| 1.2776300 | 3.0460067 | 1.6181086 | 5.5744888 |

Logarithm base 10
| 1.1538149 | 2.3702540 | 1.5440680 | 2.6597071 |

For example, you can use the MAX function to force negative numbers to be zero without affecting positive numbers.

## Syntax

`MAX(Value to compare, Value to compare 2, [etc.])`

## Arguments

Argument | Data type | Description |

Value to compare (required) | Number, date | The values to search for the maximum value. Returns the highest number or most recent date. All values must be of the same data type. This argument can be repeated to provide multiple values for comparison. It must be provided a minimum of two times. |

The MAX function returns a result of the same data type as the *Value to compare* arguments.

## Syntax example

`MAX(P1, 100, P2 / 2)`

This example returns the highest value out of:

- The P1 line item
- The number 100
- The P2 line item divided by two

## Excel equivalent

## Examples

### Use MAX to force negative numbers to zero

In this example, the MAX function is used to return only positive values from the *Adjustment values* line item. This is done by using the number 0 and the *Adjustment values* line item with the MAX function.

The module also uses the MIN function to return only negative values.

Jan 22 | Feb 22 | Mar 22 | Apr 22 | May 22 | Jun 22 | |

Adjustment values | -1,230 | 2,637 | 1,829 | -3,109 | 2,019 | 320 |

Positive Adjustment values only
| 0 | 2,637 | 1,829 | 0 | 2,019 | 320 |

Negative Adjustment values only
| -1,230 | 0 | 0 | -3,109 | 0 | 0 |

### General number example

In this example, a module is dimensioned by *Time* and line items. The first five line items contain the monthly salary costs for different departments. The final line item contains a formula that determines which department incurs the highest salary cost each month.

Jan 21 | Feb 21 | Mar 21 | |

Sales | 124,254 | 112,469 | 132,525 |

Production | 64,631 | 66,298 | 72,049 |

Executive | 175,302 | 169,030 | 190,298 |

Finance | 97,210 | 97,210 | 102,051 |

HR | 71,035 | 71,035 | 71,035 |

Highest monthly departmental salary costs
| 175,302 | 169,030 | 190,298 |

### General date example

In this example, a module is dimensioned by the *Products* list and line items. The first four line items contain product release dates for different regions, which have the date data type. The final line item contains a formula that determines the most recent product release for each product among each region.

Monet dress | Renoir dress | Picasso top | Cezanne sweatpants | Matisse pumps | |

N America | 01/04/2021 | 01/04/2021 | 12/07/2021 | 12/07/2021 | 21/07/2021 |

S America | 07/05/2021 | 23/05/2021 | 19/07/2021 | 19/07/2021 | 21/07/2021 |

Europe | 27/03/2021 | 27/03/2021 | 12/03/2021 | 12/07/2021 | 28/07/2021 |

Asia Pacific | 12/06/2021 | 12/06/2021 | 12/06/2021 | 12/07/2021 | 28/07/2021 |

Most recent global release date
| 12/06/2021 | 12/06/2021 | 19/07/2021 | 19/07/2021 | 28/07/2021 |

For example, you could enter a numerical constant in the MIN function to apply a cap to a set of values.

**Syntax**

`MIN(Value to compare, Value to compare 2, [etc.])`

## Arguments

Argument | Data type | Description |

Value to compare (required) | Number, date | The values to search for the minimum value. Returns the lowest number or earliest date. All values must be of the same data type. This argument can be repeated to provide multiple values for comparison. It must be provided a minimum of two times. |

The MIN function returns a result of the same data type as the *Value to compare* arguments.

## Syntax example

`MIN(P1, 100, P2 / 2)`

This formula would return the lowest value from between:

- The value of P1
- 100
- The value of P2 divided by two.

## Additional information

The MIN function ignores empty cells, but includes zero values.

## Calculation engine functionality differences

In Polaris, when comparing a blank date value to a non-blank date value, the MIN function returns the non-blank value.

In the Classic engine, when comparing a blank date value to a non-blank date value, the MIN function returns the blank value.

## Excel equivalent

## Examples

### Use MIN to apply a cap to a set of values

In this example, a module has an *Employees* list on columns, and line items on rows. The first line item is *Sales*, which shows the amount of sales each employee generated. The next line item, S*ales bonus,* calculates each employee's bonus, which is 20% of sales, with a limit of $5,000.

Ludwig VB | Amadeus M | Clara S | Felix M | |

Sales | 60,000 | 22,500 | 73,000 | 17,500 |

Sales bonus
| 5,000 | 4,500 | 5,000 | 3,500 |

## General number example

In this example, a module is dimensioned by *Time* and line items. The first five line items contain the monthly salary costs for different departments. The final line item contains a formula that determines which department incurs the lowest salary cost each month.

Jan 21 | Feb 21 | March 21 | |

Sales | 124,254 | 112,469 | 132,525 |

Production | 64,631 | 66,298 | 72,049 |

Executive | 175,302 | 169,030 | 190,298 |

Finance | 97,210 | 97,210 | 102,051 |

HR | 71,035 | 71,035 | 71,035 |

Lowest departmental salary costs
| 64,631 | 66,298 | 71,035 |

## General date example

In this example, a module is dimensioned by the *Products* list and line items. The first four line items contain product release dates for different regions, which have the date data type. The final line item contains a formula that determines the earliest product release for each product among each region.

Monet dress | Renoir dress | Picasso top | Cezanne sweatpants | Matisse pumps | |

N America | 01/04/2021 | 01/04/2021 | 12/07/2021 | 21/07/2021 | |

S America | 07/05/2021 | 23/05/2021 | 19/07/2021 | 19/07/2021 | 21/07/2021 |

Europe | 27/03/2021 | 12/03/2021 | 12/07/2021 | 28/07/2021 | |

Asia Pacific | 12/06/2021 | 12/06/2021 | 12/06/2021 | 12/07/2021 | 28/07/2021 |

Earliest global release date
| 01/04/2021 | 27/03/2021 | 12/03/2021 | 12/07/2021 | 21/07/2021 |

## Syntax

`MOD(Dividend, Divisor)`

## Arguments

Argument | Data type | Description |

Dividend | Number | The number to divide. |

Divisor | Number | The number to divide by. If this is 0, the MOD function returns a value of 0. |

The MOD function returns a numeric value.

## Excel equivalent

## Examples

Formula | Result |

`MOD(10, 3)` | 1 |

`MOD(26.6, 7.1)` | 5.3 |

`MOD(-50,7)` | 6 |

`MOD(271.56, -8.8)` | -1.24 |

`MOD(5, 0)` | 0 |

`MOD(0, 5)` | 0 |

For example, if you require a certain number of a part to make a single product, you can use MROUND to calculate the number of products that can be made.

## Syntax

`MROUND(Number to round [, Multiple to round to] [, Rounding direction])`

## Arguments

Argument | Data type | Description |

Number to round (required) | Number | The number to round. |

Multiple to round to | Number | The multiple to round to. Using 0 returns a value of |

Rounding direction | Keyword | The direction to round in. The keywords are UP, DOWN, NEAREST, TOWARDSZERO, and AWAYFROMZERO. There's more information below. |

The MROUND function returns a numeric result.

### Rounding direction keywords

Keyword | Description |

UP | Rounds the value of the This behavior is different to the Excel function ROUNDUP, which rounds away from zero. |

DOWN | Rounds the value of the This behavior is different to the Excel function ROUNDDOWN, which rounds towards zero. |

NEAREST | The default keyword if you omit the Rounds the value of the |

TOWARDSZERO | Rounds the value of the Number to round argument towards zero. |

AWAYFROMZERO | Rounds the value of the Number to round argument away from zero. |

## Syntax example

`MROUND(Product components, 4, Down) / 4`

In this example the total number of *Product components* rounds down to the nearest multiple of four, and then divides by four. In this hypothetical example, you need four components to create a single product. As such, this formula provides the total number of products you can make with the current number of product components.

In Polaris, if *Number of decimal places* resolves to *NaN* (Not a Number) or 0, then the MROUND function returns 0. In the Classic Engine, this returns *NaN*.

## Excel equivalent

## Examples

Formula | Description | Result |

`MROUND(1234.56)` | Only the value to be rounded, 1234.56, has been provided. The formula uses the default arguments of 0 decimal places and the NEAREST direction. | 1,235 |

`MROUND(1234.56, 10)` | Rounds 1234.56 to the nearest multiple of 10. The formula contains no rounding direction, so the default NEAREST direction is used. | 1,230 |

`MROUND(1236.54, 10, TOWARDSZERO)` | Rounds 1236.54 to a multiple of 10. The formula uses the TOWARDSZERO rounding direction, so 1236.54, a positive number, was rounded down. | 1,230 |

`MROUND(1234.56, 10, AWAYFROMZERO)` | Rounds 1234.56 to the nearest multiple of 10. The formula uses the AWAYFROMZERO rounding direction, so 1234.56, a positive number, was rounded up. | 1,240 |

`MROUND(1234.56, 1000)` | Rounds 1234.56 to the nearest multiple of 1,000. The formula contains no rounding direction, so the default NEAREST direction is used. | 1,000 |

`MROUND(-1234.56, 1000, UP)` | Rounds -1234.56 to a multiple of 1,000. The formula uses the UP rounding direction, so x rounds towards positive infinity. | -1,000 |

`MROUND(-1234.56, 1000, DOWN)` | Rounds -1234.56 to a multiple of 1,000. The formula uses the DOWN rounding direction, so x rounds towards negative infinity. | -2,000 |

`MROUND(15555, 10)` | Rounds 15555 to the nearest multiple of 10. The formula contains no rounding direction, so the default NEAREST direction is used. As 55 is a positive number halfway between 50 and 60, x rounds up, towards positive infinity. | 15,560 |

`MROUND(-15555, 10)` | Rounds -15555 to the nearest multiple of 10. The formula contains no rounding direction, so the default NEAREST direction is used. As -55 is a negative number halfway between -50 and -60, x rounds down, towards negative infinity. | -15,560 |

This function is the inverse of the LOG function.

For example, if the formula `LOG(a, b)`

gives a result of c, the formula `POWER(b, c)`

gives a result of a.

## Syntax

`POWER(Number, Power)`

## Arguments

Argument | Data type | Description |

Number | Number | The number to raise to a power. |

Power | Number | The power, or exponent, to raise the number to. |

## Constraints

### Cannot return root of negative numbers

The POWER function can be used to calculate the root of a positive number by using a fraction for the *Power* argument. For example using 1/2 or 1/3 for the *Power* argument returns the square root and cube root respectively. However, if you attempt to return the root of a negative number, the POWER function returns a result of NaN (Not a Number).

## Calculation engine functionality differences

In Polaris, `POWER(0,0)`

returns 0.

In the Classic Engine, `POWER(0,0)`

returns 1.

## Excel equivalent

## Examples

Formula | Description | Result |

`POWER(2, 4)` | This formula raises two to the power of four. | 16 |

`POWER(9, 9)` | This formula raises nine to the power of nine. | 387,420,489 |

For example, you can use ROUND to calculate the number of products a certain number of parts can create.

## Syntax

`ROUND(Number to round [, Number of decimal places] [, Rounding direction] [, Rounding method])`

## Arguments

**Note**: Only the first argument is required. If you use the optional arguments, then all preceding arguments are required.

Argument | Data type | Description |

Number to round (required) | Number | The number to round. |

Number of decimal places | Number | The number of decimal places to round to. If you omit this argument, the ROUND function rounds to the nearest whole integer. |

Rounding direction | Keyword | The direction to round in. The keywords are UP, DOWN, NEAREST, TOWARDSZERO, and AWAYFROMZERO. There's more information in the Rounding direction keywords section below. If you provide the |

Rounding method | Keyword | The rounding method to use. The keywords are NORMAL and EXACT. If you omit the There's more information in the Rounding direction keywords section below. |

The ROUND function returns a number-formatted result.

### Rounding direction keywords

Keyword | Description |

UP | Rounds the value of the This behavior is different to the Excel function ROUNDUP, which rounds away from zero. |

DOWN | Rounds the value of the This behavior is different to the Excel function ROUNDDOWN, which rounds towards zero. |

NEAREST | The default keyword if you omit the Rounds the value of the |

TOWARDSZERO | Rounds the value of the Number to round argument toward zero. |

AWAYFROMZERO | Rounds the value of the Number to round argument away from zero. |

### Rounding method keywords

Keyword | Description |

NORMAL | The default keyword if you omit the When you use the NORMAL keyword, for some rare input values, the ROUND function results in a small degree of floating point error for the least significant digits. |

EXACT | When you use the EXACT keyword, the ROUND function performs additional processing to minimize the effect of floating point error. |

## Syntax example

`ROUND((Payment amount * Exchange rate), 2, UP, EXACT)`

This formula multiplies a payment amount by an exchange rate and rounds the result to two decimal places. The formula uses the UP keyword to round upwards toward positive infinity and uses the EXACT keyword to minimize any floating point error.

## Additional Information

### Round to powers of ten

You can use a negative number for the *Number of decimal places* argument. If you do this, the ROUND function rounds the *Number to round* argument to a power of ten. You can also use the MROUND function to do this, and that function can also round to numbers that are not a power of ten.

### Precision differences

When you round a number that's very large or has a lot of decimal places, sometimes tiny precision differences can arise due to floating point math. Find out more about Precision differences and large number calculations.

## Calculation engine functionality differences

In Polaris, the ROUND function always uses the EXACT rounding method. As such, you cannot provide the *Rounding method* argument. In the Classic engine, the ROUND function uses the NORMAL rounding method unless you provide EXACT for the *Rounding method* argument.

In Polaris, if *Number to round* is 0 and *Number of decimal places* is *NaN* (Not a Number), then the ROUND function returns 0. In the Classic Engine, this returns *NaN*.

In Polaris, if *Number to round* is not 0 and *Number of decimal places* is *NaN* (Not a Number), then the ROUND function returns *NaN*. In the Classic Engine, this returns *NaN* for the NORMAL *Rounding method*, and the *Number to round* for the EXACT *Rounding method*.

In Polaris, if *Number of decimal places* is not a whole number, then Number of decimal places rounds to the nearest whole number. The Classic Engine does not support the NORMAL *Rounding method* in this case, and with the EXACT *Rounding method*, ROUND rounds towards zero.

In Polaris, if *Number of decimal places* is -Infinity or less than -308.5, then ROUND returns 0, -Infinity, or Infinity, as expected. If *Number of decimal places* is Infinity or greater than 308.5, then ROUND returns *Number to round*, -Infinity, or Infinity, as expected. In the Classic Engine, all these return *NaN* (Not a Number).

In Polaris, if *Rounding direction* is UP, DOWN, AWAYFROMZERO, or TOWARDSZERO, and the result is too small to represent, then ROUND returns the smallest possible number. In the Classic Engine, these cases, with a *Rounding method* of EXACT, return 0.

In Polaris, if *Number to round* is negative, *Rounding direction* is NEAREST, and *Number of decimal places* is negative, then ROUND rounds away from zero. The Classic Engine rounds this towards zero.

## Examples

### General examples

Formula | Description | Result |

`ROUND(12.344)` | Only the value to be rounded, 12.344, has been provided. The formula uses the default arguments of: 0 decimal places, the NEAREST direction, and NORMAL rounding method | 12.0 |

`ROUND(12.399, 1, DOWN)` | This formula contains arguments of 1 decimal place and the DOWN direction. This means that 12.399 is rounded down to one decimal place. | 12.3 |

`ROUND(-12.5)` | As this formula only contains ROUND and a value to be rounded, it uses the default arguments of: 0 decimal places, the NEAREST direction, and NORMAL rounding method. As -12.5 is a negative number, it rounds down. | -13 |

`ROUND(532.8399, 2, TOWARDSZERO)` | This formula contains arguments of 2 decimal places and the TOWARDSZERO direction. This means that 532.8399 is rounded down towards zero to 2 decimal places. This usage of ROUND can calculate the price of a product. Rounding towards zero maximizes profit margin. | 532.83 |

`ROUND(28.135, 1, UP)` | This formula contains arguments of 1 decimal place and the UP direction. This means that 28.135 is rounded up to one decimal place. | 28.2 |

`ROUND(2.509, 2, NEAREST, NORMAL)` | This is an example where the NORMAL rounding method results in a small degree of error. | 2.5100000000000002 |

`ROUND(2.509, 2, NEAREST, EXACT)` | The additional processing performed by the EXACT rounding method corrects the small degree of error in the previous example. | 2.51 |

### ROUND with currencies

In foreign exchange, exchange rates often include up to 5 decimal places. However, some currencies such as Euros, U.S. Dollars, or Pound Sterling can only be paid in increments of two decimal places.

In this example, payments are made from a company in U.S. Dollars to an account that uses Euros. It contains a *Transaction* list on columns, and line items on rows. The line items include:

- The amount paid in U.S. dollars.
- The detailed exchange rate for U.S. Dollars to Euros, to five decimal places.
- A formula that applies the detailed exchange rate to the U.S. Dollar amount, then uses the ROUND function to round the final amount up to two decimal places for payment.

Transaction 1 | Transaction 2 | Transaction 3 | Transaction 4 | Transaction 5 | |

Amount to be paid (USD) | USD 500 | USD 750 | USD 100 | USD 125 | USD 375 |

Exchange rate at time of payment | 0.84271 | 0.84037 | 0.82473 | 0.82829 | 0.85154 |

Amount paid (EUR)
| EUR 421.36 | EUR 630.28 | EUR 82.48 | EUR 103.54 | EUR 319.33 |

`SIGN(Number)`

## Arguments

Argument | Data type | Description |

Number | Number | The number to determine the sign of. |

The SIGN function returns a number.

## Excel equivalent

## Examples

Formula | Result |

`SIGN(100)` | 1 |

`SIGN(0)` | 0 |

`SIGN(-10)` | -1 |

## Syntax

`SQRT(Number)`

## Arguments

Argument | Data type | Description |

Number | Number | The number to calculate the square root of, or radicand. Must be a positive number or zero. |

The SQRT function returns a number.

## Constraints

You can only use the SQRT function with a positive number or zero. If you use the SQRT function with a negative number, it returns a value of NaN (Not a Number).

## Excel equivalent

## Examples

Formula | Result |

`SQRT(16)` | 4 |

`SQRT(169.45)` | 13.0172961862 |

`SQRT(0)` | 0 |

`SQRT(-16)` | NaN |