Reference: Mathematical Formula Functions
Adaptive Planning formulas supports simple math functions and complex math functions. You can use Formula Assistant to access all the math functions.
Syntax in Documentation
A few notes on syntax in documentation:
- We use capital letters in parenthesis, like (N), or text inside chevrons, like <account_code>, to indicate variables. You must replace the variables with valid constants, account references, or formula expressions.
- We use commas to separate terms, according to the standard of many countries. If your browser uses commas as decimal points, you must separate the terms with semicolons.
- We add spaces to make the formulas readable, although they're not necessary. You can also add spaces or leave them out of your formulas.
- We capitalize functions, such as Divf, and use all caps for elements, like ACCT, but formulas aren't case sensitive.
Simple Math Functions
You can find simple math functions in Formula Assistant using the buttons from the toolbar, or by typing math symbols from your keyboard.
Math Operator | Example Formula |
|---|---|
Addition (+) | ACCT.001 + ACCT.002 |
Subtraction (-) | ACCT.001 - ACCT.002 |
Multiplication (*) | ACCT.001 * ACCT.002 |
Division (Divf) | Divf (ACCT.001, ACCT.002) Divides ACCT.001 (the dividend) by ACCT.002 (the divisor) |
Modulo (%) | 10 % 3 Calculates the remainder of the quotient. Returns 1 because 10 divided 3 = 3 remainder 1. |
Parenthesis () | (ACCT.001 + ACCT.002) * 12 Dictates the order of operation: Add the accounts before multiplying by 12. |
Round(N) | Round (ACCT.001) Rounds to the nearest whole number. In the example if the value of ACCT.001 is 5.75, the formula returns 6. |
Complex Math Functions
Find complex math when you select
Mathematical
from the Function
prompt in Formula Assistant. The functions display in the drop-down menu next to the Function
prompt. Syntax | Description | Example |
|---|---|---|
Div (N, D) Divf (N, D) | Divides N by D. N is the numerator. D is the denominator. Use to calculate metrics and ratios, like:
| Replace both N and D with account references, numbers, or formula expressions. Existing values: ACCT.001 = 7.8 ACCT.002 = 4.2 Expression: Divf (ACCT.001, ACCT.002) Calculation: = Divf (7.8, 4.2) = 1.86 |
Round (N) | Rounds N to the nearest whole number. Use to simplify calculations or for calculating values were fractions don't make sense. | Replace N with account references, numbers, or formula expressions. Existing values: ACCT.001 = 7.8. Expression: Round (ACCT.001) Calculation: Round (7.8) = 8 |
Trunc (N) | Drops the digits after the decimal point. Use to simplify calculations or for calculating values were fractions don't make sense. | Replace N with account references, numbers, or formula expressions. Existing values: ACCT.001 = 7.8 ACCT.002 = 4.2 Expression: Trunc (ACCT.001 * ACCT.002) Calculation: = Trunc (7.8 * 4.2) = Trunc (32.76) = 32 |
Floor (N) | Rounds N down to the nearest whole number. Use when fractional values don't make sense and you want to round down to whole numbers. Example: Calculate price per units sold when you don’t want units to be a fraction. | Replace N with account references, numbers, or formula expressions. Existing values: ACCT.001 = 23.89 ACCT.002 = 4.75 Expression: Floor (Divf (ACCT.001, ACCT.002)) Calculation: = Floor (Divf (23.89, 4.75)) = Floor (5.688) = 5 For Floor (-5.688), the result is -6 because -6 is less than -5.688. |
Ceil (N) | Rounds up to the nearest whole number. Use when fractional values don't make sense and you want to round up. Example: Calculate headcount coverage based on projected sales. If the answer is 1.3, you need 2 workers to prevent understaffing the store. | Replace N with account references, numbers, or formula expressions. Existing values: ACCT.001 = 23.89 ACCT.002 = 4.2 Expression: Ceil (Divf (Acct.001, Acct.002)) Calculation: = Ceil (Divf (23.89, 4.2)) = Ceil (5.688) = 6 Ceil (-5.688) is -5 because -5 is greater than -5.688. |
Power (N, E) | Raises the value of N by the power of the exponent, E. Exponents multiply the number, N, by itself a specific number times. N must be a positive number and E must be an integer. Use to measure exponential growth or decay of a given set of data. Example: Calculate the future value of an investment with an annual compounded interest rate | Replace N and E with account references, numbers, or formula expressions. Existing Values: Interest rate: ASSUM.001 = 2% Asset: ACCT.001 = 100 Number of years = 3 Expression: ACCT.001 * (Power (1+ASSUM.001, 3)) Calculation: = 100 * (Power (1 + .02, 3)) = 100 * (Power (1.02, 3)) = 100 * (1.02 * 1.02 * 1.02) = 100 * 1.061 = 106.12 |
Ln (N) | Calculates the natural logarithm of N, or the inverse of the exponential function. Ln calculates the exponent necessary to raise Euler's constant, which equals about 2.718, to the value of N. N must be a positive number. Use Ln to measure exponential growth or decay. Example: Calculate the continuous compound growth rate of revenue over time. | Replace N and with account references, numbers, or formula expressions. Existing values: Revenue 2024: ACCT.001 = 100,000 Desired value: 500,000 Periods to reach desired value = 4. Expression: Divf (Ln (Divf (500,000, ACCT.001 [time = 2024]), 4) Calculation: = Divf (Ln (Divf (500,000, 100,000), 4 = Divf (Ln (5), 4) = Divf (1.609, 4) = .402 Converted to percent: 40.2% compounded growth. |
Greatest (N1, N2, ...) | Populates the cell with the largest value of a series. Use Greatest to identify the maximum value among multiple options. Example: Calculate commission-based compensation when you also provide a minimum salary. | Replace N1, N2 and so on with account references, numbers, or formula expressions, separating each term with commas. Existing values: Salary Minimum: ASSUM.001 = 30,000 Gross Sales: ACCT.001 = 50,000 Commission Rate: 20% Expression: Greatest (ASSUM.001, (ACCT.001 * .2)) Calculation: = Greatest (30,000, (50,000 * .2) = Greatest (30,000, 10,000) = 30,000 |
Least (N1, N2, ...) | Populates the cell with the smallest value in a series. Use Least to identify the minimum value among multiple options. Example: Calculate prices that don’t exceed the competition. | Replace N with account references, numbers, or formula expressions, separating each term with commas. Existing values: Cost: ACCT.001=200 Competitor Price: ACCT.002 = 250 Pricing Strategy: 20% minimum profit. Expression: Least ((ACCT.001 + (ACCT.001 * .2), ACCT.002) Calculation: = Least ((200 + (200 * .2)), 250) = Least ((200 + 40), 250) = Least (240, 250) = 240 |
Spread445 (N, M) Spread454 (N, M) Spread545 (N, M) | Returns the value based on the theoretic allocation of another value across a specified number of months (M) using a 4-4-5, 4-5-4, or 5-4-4 accounting calendar. Example: Use to calculate revenue based on an allocation over a specific period. See Concept: Spread Formula Functions. | Replace N with account references, numbers, or formula expressions. Replace M with the number of periods or remove to calculate over a 12-period spread. Existing Values: Revenue: ACCT.001 = 100,000 Expression: Spread445 (ACCT.001, 6) Calculations: Step 1 . Calculates the number of weeks in the period: = 6 months = 2 quarters with 13 weeks each = 2 * 13 = 26 weeks Step 2 . Calculates the value per week: = 100,000 / 26 = 3,836. Step 3 . Multiplies by 4 or 5 based on the current period: If the 1st or 2nd month in the quarter (Jan, Feb, Apr, May, Jul, Aug, Oct, Nov): = 3836 * 4 = 15,386 If 3rd month in the quarter (Mar, Jun, Sept, Dec): = 3836 * 5 = 19,231 |
Npv (discount_rate, value1, value2, ..., valueN) | Returns the present value of future cash flows, discounted back to the present time. The formula divides the first value by the discount rate plus 1. It divides the 2nd value by the discount rate plus 1 taken to the 2nd power. It divides the 3rd value by the discount rate plus 1 taken to the 3rd power and so on. The NPV function in Adaptive Planning does not factor in the initial investment cost. You need to subtract this cost separately to obtain the true Net Present Value. The accuracy of the NPV calculation depends heavily on the accuracy of the projected cash flows and the chosen discount rate. Example: Calculate the potential profitability of an investment taking into the account the cost of capital with the discount rate compounded over time. Best to:
| Replace discount_rate with minimum rate of return required to justify the investment. Replace value1, value2,...value N with a cashflow account modified by a time range to capture the potential return over time. Existing values: Discount_rate = 8% Cashflow: ACCT.001 = 100 for the 1st year 200 for the 2nd year 300 for the 3rd year Expression: NPV(0.08, ACCT.001 [time = this.year : this.year+2] Calculation for value1: = 100 / 1.08 = 92.59 Calculation for value2: = 200 / (1.08 * 1.08) = 200 / 1.17 = 170.94 Calculation for value3: = 300 / (1.08 * 1.08 * 1.08) = 300 / 1.26 = 238.10 Full calculation: value1 + value2 + value3 = 92.59 + 170.94 + 238.10 = 501.63 A positive integer suggests that the future cashflow is greater than the cost. |
Irr (value1, value2, ..., valueN, estimated_irr) | Estimates the internal rate of return for a series of cash flows. Uses up to 600 iterations to get close to the true value of the internal rate of return. Use to evaluate the profitability of an investment or project. | Replace value1 with either a cashflow account modified by a range of time or replace each value with different cashflow accounts. Replace estimated_irr with your estimate, or remove to use the default, which is 0.1. Existing values : N/A Expression: IRR(ROW.Cashflow[time=this:this+36], 0.2) Calculation : N/A |
Abs (N) | Returns the positive value of any number. Use when you need to avoid negative numbers in your calculations. Example: Identify the magnitude of variances in Net Income, regardless of whether it’s a profit or a loss. | Replace N with account references, numbers, or formula expressions. Existing Values: Net Income in 2023: ACCT.001 = 100,000 Net Income in 2022: ACCT.001 = 150,000 Expression: Abs ((ACCT.001 [time = FY2023]) - (ACCT.001 [time = FY2022])) Calculation: = Abs (100,000 - 150,000) = Abs (-50,000) = 50,000 |
Sqrt (N) | Calculates the square root of the number (N). Example: Convert monthly volatility to annual volatility. | Replace N with account references, numbers, or formula expressions that capture a variance. Existing Values: Monthly Volatility: ACCT.001 = 5% Expression: ACCT.001 * Sqrt (12) Calculation: = 0.05 * Sqrt (12) = .05 * 3.46 = .17 = 17% |
Trigonometry Functions
Use trigonometry functions to measure cyclical patterns in data, like:
- Seasonal trends in sales.
- Price fluctuations in stocks.
- Distances for territory mapping. See Example: Calculate Distances for Territory Optimization.
Syntax | Description | Example |
|---|---|---|
Radians (N) | Converts the degree of an angle, N, into Radians using Pi (3.14). Radians = (N * Pi) / 180 Use in combination with other trigonometry functions. Radians function converts degrees to radians so that you can calculate other trigonometry functions, like Sine and Cosine. | Replace N with an expression that measures an angle in degrees. Expression: Radians (270) Calculation: = (270 * 3.14) / 180 = 847.8 / 180 = 4.71 |
Sin (N) | Calculates the sine of a right-angle triangle where N is the degree of an angle converted to radians. Sine = quotient of
| Replace N with an expression that calculates radians. Expression: Sin (Radians(30)) Calculation: = .5 |
Cos (N) | Calculates the cosine of a right-angle triangle where N is the degree of an angle converted into radians. Cosine = quotient of:
| Replace N with an expression that calculates radians. Expression: Cos (Radians(60)) Calculation: = .5 |
Asin (N) | Calculates the arcsine of a right-angle triangle, where N is sine. Arcsine calculates the angle of the opposite side and the hypotenuse. | Replace N with an expression that calculates sine. Expression: Asin (-0.5) Calculation: = -0.52 |
Acos (N) | Calculates the arccosine of a right-angle triangle, where N is the cosine. Arccosine calculates the angle of the adjacent side and hypotenuse. | Replace N with an expression that calculates cosine. Expression: Acos (-0.5) Calculation: = 2.09 |