Number Functions

FLOOR(1.3)

Returns 1

CEILING(1.3)

Returns 2

ROUND(1.239, 3)

Returns 1.24

Like the Excel function, this rounds the first argument to the number of decimals of the second argument. Make sure to also set the number of decimals on the formula field to match it, otherwise extra zero's will be added.

FORMATTED_VALUE(value, number_of_decimals)

Show a number value with a certain number of decimals. Can also be used inside Text String in a formula, for example: IF(@NumberField >50, "FORMATTED_VALUE(@FormulaField, 3) items", "Try again")

FVSCHEDULE(principal, schedule [,modifier])

Calculate the future value of an investment with a variable interest rate.

principal = the present value (required)
schedule = array of interest rates to apply (required)
modifier = multiply every interest rate by this number (optional)

FVSCHEDULE( @NumberField, FINDIFS_ALL(T1!B, T1!A, ">= @QF From ", T1!A, "< @QK To ") )

This will calculate the future value of @NumberField when applied with the variable interest rates found in Table 1, based on the first column (T1!A) being between @QF and @QK

FV(rate, periods, payment, value, type)

Return the future value of an investment.

rate = The interest rate per period
periods - The total number of payment periods
payment = The payment made each period. Must be entered as a negative number
value = [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number
type = [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0

IRR(values, [guess])

Internal Rate of Return

values = Array of values that represents the cashflow. You can use something like FINDIFS_ALL(T1!A, T1!B, ">0") to return all the values in T1!A for which T1!B is greater than zero
guess = [optional] a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return). If omitted, the function will take a default value of 0.1 (=10%)

NPV(rate, value1, [value2, ...])

Net Present Value

rate = The rate of discount over the length of one period
value1, value2... - value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.

NPV2(rate, values)

Net Present Value with a Table column

rate = The rate of discount over the length of one period
values = Array of values that represent the payments. You can use something like FINDIFS_ALL(T1!A, T1!B, ">0") to return all the values in T1!A for which T1!B is greater than zero

RANDOM(ID, [max])

Random number between 0 and max-1

ID = the ID of the random call. Use any number as a unique identifier for this RANDOM() call. If you use the same ID in another RANDOM call, the same number will be returned.
max = a number greater than 1, optional, If missing, max will be set to 100. The random number will be a whole number between greater than or equal to zero and less than max.

RANDOM(111, 4) will return a random number between 0 and 3 (so 0, 1, 2 or 3)
RANDOM(111) will return the same number as above, and RANDOM (222, 100) will return a random number between 0 and 99