Contents[Hide]

Array functions

Function Description
boolean IN(object searchedValue, object value1[, object value2...])
New! Reporting for Server and Datacenter only		 Returns True if the searched value is equal to at least one of the specified values.
object[] MAKEARRAY(value1 [, value2, value3,...])
New! Reporting for Server and Datacenter only		 Creates an array, containing all the specified elements.

Financial functions

Function Description
double FV(double rate, long nper, double pmt, [double pv], [int type]) Calculates the future value of an investment based on a constant interest rate. One can use FV with either a periodic, constant payments, or a single lump sum payment
double IRR(double value1, ..., double valueN, [double guess]) Calculates the internal rate of return for a series of cash flows represented by the numbers in value1...valueN
double MIRR(double value1, ..., double valueN, double finance_rate, double reinvest_rate) Calculates the modified internal rate of a return for a series of periodic cash flows
double NPER(double rate, double pmt, double pv, [double fv], [long type]) Calculates the number of periods for an investment based on periodic, constant payments and a constant interest rate
double NPV(double rate, double value1, ..., double valueN) Calculates the net present value of an investment by using a discount rate and a series of future payments and income
double PMT(double rate, long nper, double pv, [double fv], [int type]) Calculates the payment for a loan based on constant payments and a constant interest rate
double PPMT(double rate, long per, long nper, double pv, [double fv], [int type]) Calculates the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate
double PV(double rate, long nper, double pmt, [double fv], [int type]) Calculates the present value of a loan or an investment, based on a constant interest rate
double RATE(long nper, double pmt, double pv, [double fv], [int type], [double guess]) Calculates the interest rate per period of an annuity
Function Description
string GETCONFLUENCEPAGEDATA(string page)
Server and Datacenter only		 Returns the contents of the specified page as plain text
string GETCONFLUENCEPAGEDATAASHTML(string page)
Server and Datacenter only		 Returns the contents of the specified page as HTML
long GETCONFLUENCEPAGEELEMENTCOUNT(string page, string selectQuery)
Server and Datacenter only		 Returns the number of the Confluence page elements according to the specified query. selectQuery is a query in jquery-alike selector syntax. Here are syntax rules and examples:
  • Common selection rules
    • "tagname" - selects elements by tag, e.g. "a"
    • "ns|tag" - selects elements by tag in a namespace, e.g. "ab|name" finds "<ab:name>" elements
    • "#id" - selects elements by ID, e.g. "#logo"
    • ".class" - selects elements by class name, e.g. ".masthead"
    • "[attribute]" – selects elements with attribute, e.g. "[href]"
    • "[^attr]" – selects elements with an attribute name prefix, e.g. "[^data-]" finds elements with HTML5 dataset attributes
    • "[attr=value]" – selects elements with attribute value, e.g. "[width=1920]" (also quotable, like "[data-name='launch sequence']")
    • "[attr^=value]", "[attr$=value]", "[attr*=value]" - selects elements with attributes that start with, end with, or contain the value, e.g. "[href*=/path/]"
    • "[attr~=regex]" - selects elements with attribute values that match the specific regular expression; e.g. "img[src~=(?i)\.(png|jpe?g)]"
    • "*" - selects all elements, e.g. "*"
  • Combinations
    • "el#id" - selects elements with ID, e.g. "div#logo"
    • "el.class" - selects elements with class, e.g. "div.masthead"
    • "el[attr]" - selects elements with attribute, e.g. "a[href]"
    • You may use any combination, e.g. "a[href].highlight"
    • "ancestor child" - selects child elements that descend from ancestor, e.g. ".body p" finds "p" elements anywhere under a block with class "body"
    • "parent > child" - selects child elements that descend directly from parent, e.g. "div.content > p" finds "p" elements; and "body > *" finds the direct children of the "body" tag
    • "siblingA + siblingB" - selects sibling B element immediately preceded by sibling A, e.g. "div.head + div"
    • "siblingA ~ siblingX" - selects sibling X element preceded by sibling A, e.g. "h1 ~ p"
    • "el, el, el" - groups multiple selectors, selects unique elements that match any of the selectors; e.g. "div.masthead, div.logo"
  • Using pseudo selectors
    • ":lt(n)" - selects elements whose sibling index (i.e. its position in the DOM tree relative to its parent) is less than n; e.g. "td:lt(3)"
    • ":gt(n)" - selects elements whose sibling index is greater than "n"; e.g. "div p:gt(2)"
    • ":eq(n)" - selects elements whose sibling index is equal to "n"; e.g. "form input:eq(1)"
    • ":has(selector)" - selects elements that contain elements matching the selector; e.g. "div:has(p)"
    • ":not(selector)" - selects elements that do not match the selector; e.g. "div:not(.logo)"
    • ":contains(text)" - selects elements that contain the given text. The search is case-insensitive; e.g. "p:contains(java)"
    • ":containsOwn(text)" - selects elements that directly contain the given text
    • ":matches(regex)" - selects elements whose text matches the specified regular expression; e.g. "div:matches((?i)login)"
    • ":matchesOwn(regex)" - selects elements whose own text matches the specified regular expression
    • Note that the above indexed pseudo-selectors are 0-based, that is, the first element is at index 0, the second at 1, etc.
string GETCONFLUENCEPAGEELEMENTDATA(string page, string selectQuery, [long index])
Server and Datacenter only		 Returns the contents of the specified page element as plain text
string GETCONFLUENCEPAGEELEMENTDATAASHTML(string page, string selectQuery, [long index])
Server and Datacenter only		 Returns the contents of the specified page element as HTML

Date and time functions

Function Description
datetime DATE(long year, long month, long day) Returns the sequential serial number that represents a particular date
long DATEDIF(datetime start_date, datetime end_date, string unit)
New!		 Returns the number of days, months and years between specified dates. Supported values for unit parameter:
  • "Y" - return the number of complete years in the period;
  • "M" - return the number of complete months in the period;
  • "D" - return the number of days in the period;
  • "MD" - return the difference between the days in start_date and end_date; the months and years of the dates are to be ignored;
  • "YM" - return the difference between the months in start_date and end_date; the days and years of the dates are to be ignored;
  • "YD" - return the difference between the days of start_date and end_date; the years of the dates are to be ignored.
The start_date parameter value should not be greater than the end_date parameter value.
datetime DATEVALUE(string date_text) Converts a date that is stored as text to a datetime value
long DAY(datetime date) Returns the day of a specified date, represented by datetime parameter. The day is given as an integer ranging from 1 to 31
long DAYS(datetime end_date, datetime start_date) Returns the number of days between two dates
long HOUR(datetime date) Returns the hour of the specified time value. The hour is given as a number, ranging from 0 (12:00 A.M.) till 23 (11:00 P.M.)
long HOURS(datetime end_datetime, datetime start_datetime)
New!		 Returns the number of hours between two date-time values. Minutes and seconds are ignored. Note, that the first parameter is End date, and the second one is Start date.
long MINUTE(datetime date) Returns the minutes of the specified time value. The minute is given as a number, ranging from 0 to 59
long MINUTES(datetime end_datetime, datetime start_datetime)
New!		 Returns the number of minutes between two date-time values. Seconds are ignored. Note, that the first parameter is End date, and the second one is Start date.
long MONTH(datetime date) Returns the month of a date represented by the specified serial number. The month is given as a number, ranging from 1 (January) to 12 (December)
long NETWORKDAYS(datetime start_date, datetime end_date [, datetime holiday_1, ...]) Returns the number of working days between start date and end date. Working days exclude weekends and the dates specified as holidays.
datetime NOW() Returns the serial number of the current date and time
long SECOND(datetime date) Returns the seconds of the specified time value. The second is given as a number in the range 0 (zero) to 59
datetime TIME(long hour, long minute, long second) Returns the decimal number for the particular time
datetime TODAY() Returns the serial number of the current date
long WEEKDAY(datetime date) Returns the day of the week corresponding to the specified date. The day is given as a number, ranging from 1 (Sunday) to 7 (Saturday)
datetime WORKDAY(datetime start_date, long days [, datetime holiday_1, ...]) Returns a date that is the indicated number of working days before or after the starting date. Work days exclude weekends and the dates, identified as holidays.
long YEAR(datetime date) Returns the year corresponding to the specified date. The year is returned as a number in the range 1900-9999

Engineering functions

Function Description
long BIN2DEC(string number) Converts a binary number to a decimal value
string DEC2BIN(long number, [long places]) Converts a decimal number to a binary value
string DEC2HEX(long number, [long places]) Converts a decimal number to a hexadecimal value
long DELTA(double number1, [double number2]) Tests whether two values are equal to each other. Returns 1 if number1 is equal to number2; returns 0 in other case
long HEX2DEC(string number) Converts a hexadecimal number to a decimal value
long IMAGINARY(string inumber) Returns the imaginary coefficient of a complex number in x + yi or x + yj text format of expression
long IMREAL(string inumber) Returns the real coefficient of a complex number in x + yi or x + yj text format of expression
long OCT2DEC(string number) Converts an octal number to a decimal value

Financial functions

Function Description
double FV(double rate, long nper, double pmt, [double pv], [int type]) Calculates the future value of an investment based on a constant interest rate. One can use FV with either a periodic, constant payments, or a single lump sum payment
double IRR(double value1, ..., double valueN, [double guess]) Calculates the internal rate of return for a series of cash flows represented by the numbers in value1...valueN
double MIRR(double value1, ..., double valueN, double finance_rate, double reinvest_rate) Calculates the modified internal rate of a return for a series of periodic cash flows
double NPER(double rate, double pmt, double pv, [double fv], [long type]) Calculates the number of periods for an investment based on periodic, constant payments and a constant interest rate
double NPV(double rate, double value1, ..., double valueN) Calculates the net present value of an investment by using a discount rate and a series of future payments and income
double PMT(double rate, long nper, double pv, [double fv], [int type]) Calculates the payment for a loan based on constant payments and a constant interest rate
double PPMT(double rate, long per, long nper, double pv, [double fv], [int type]) Calculates the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate
double PV(double rate, long nper, double pmt, [double fv], [int type]) Calculates the present value of a loan or an investment, based on a constant interest rate
double RATE(long nper, double pmt, double pv, [double fv], [int type], [double guess]) Calculates the interest rate per period of an annuity

JSON processing functions

Function

Description
json JSONARRAYEXTRACTPATH(json jsonArray, string path [, string resultIfNone])
Reporting plug-in only

Lists the specified JSON array and returns a JSON array of the JSON objects by the specified path
json JSONARRAYGET(json jsonArray, long index [, string resultIfNone])
Reporting plug-in only

Returns Nth element of the specified JSON array
json JSONARRAYGETFIRST(json jsonArray [, string resultIfNone])
Reporting plug-in only

Returns the first element of the specified JSON array
json JSONARRAYGETLAST(json jsonArray [, string resultIfNone])
Reporting plug-in only

Returns the last element of the specified JSON array
long JSONARRAYLENGTH(json jsonArray)
Reporting plug-in only

Returns the specified JSON array length
Object[] JSONARRAYTOARRAY(json jsonArray [, string resultIfNone])
Reporting plug-in only

Converts the specified JSON array to an Object array for using in the functions, which accept arrays, like SUM, MIN, MAX, etc.
json JSONXTRACTPATH(json jsonObject, string path [, string resultIfNone])
Reporting plug-in only

Extracts a JSON object by the specified path of the specified JSON object

Logical functions

Function Description
boolean AND(boolean a; boolean b; ...) Returns TRUE if all parameters are non-zero values
boolean FALSE() Returns the logical value FALSE
string IF(boolean aCondition, string ifTrue, string ifFalse) Returns ifTrue value if aCondition is non-zero value. Otherwise returns ifFalse value
boolean NOT(boolean a) Returns TRUE if a is zero or zero if a is non zero value
boolean OR(boolean a; boolean b; ...) Returns TRUE if any of the arguments is non-zero
string SWITCH(string expression, string value1, string result1, [string default or string value2, string result2],…[string default or string value3, string result3]) Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned.
boolean TRUE() Returns the logical value TRUE
boolean XOR(boolean logical1, [boolean logical2],…) Returns a logical Exclusive Or of all arguments

Lookup and reference functions

Function Description
string HLOOKUP(string lookup_value, range lookup_range, long row) Searches for a value in the top row of a range, and then returns a value in the same column from a row one specifies in the range. Use HLOOKUP when your comparison values are located in a row across the top of a range, and you want to look down a specified number of rows.
string VLOOKUP(string lookup_value, range lookup_range, long column) Finds things in a range by row. For example, looks up a price of an automotive part by the part number.

Math and trigonometry functions

Function Description
double ABS(double a) Returns the absolute value of a double value
double ACOS(double a) Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi
double ASIN(double a) Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2
double ATAN(double a) Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2
double ATAN2(double y; double x) Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta)
double CBRT(double a) Returns the cube root of a double value
double CEIL(double a) Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer
double COPYSIGN(double magnitude; double sign) Returns the first floating-point argument with the sign of the second floating-point argument
double COS(double a) Returns the trigonometric cosine of an angle
double COSH(double x) Returns the hyperbolic cosine of a double value
double EXP(double a) Returns Euler's number e raised to the power of a double value
double EXPM1(double x) Returns e^x -1
double FLOOR(double a) Returns the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer
long GETEXPONENT(double d) Returns the unbiased exponent used in the representation of a double
double HYPOT(double x; double y) Returns sqrt(x^2 +y^2) without intermediate overflow or underflow
double IEEEREMAINDER(double f1; double f2) Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard
double LOG(double a) Returns the natural logarithm (base e) of a double value
double LOG10(double a) Returns the base 10 logarithm of a double value
double LOG1P(double x) Returns the natural logarithm of the sum of the argument and 1
double NEXTAFTER(double start; double direction) Returns the floating-point number adjacent to the first argument in the direction of the second argument
double NEXTUP(double d) Returns the floating-point value adjacent to d in the direction of positive infinity
double PI()
New!		 Returns the number 3.141592653589793, the mathematical constant pi, accurate to 16 digits
double POW(double a; double b) Returns the value of the first argument raised to the power of the second argument
double RANDOM() Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0
double RINT(double a) Returns the double value that is closest in value to the argument and is equal to a mathematical integer
long ROUND(double a [, long num_digits]) Returns the closest long to the argument, with ties rounding up
double SCALB(double d; int scaleFactor) Return d ? 2^scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set
double SIGNUM(double d) Returns the signum function of the argument; zero if the argument is zero, 1.0 if the argument is greater than zero, -1.0 if the argument is less than zero
double SIN(double a) Returns the trigonometric sine of an angle
double SINH(double x) Returns the hyperbolic sine of a double value
double SQRT(double a) Returns the correctly rounded positive square root of a double value
double SUM(double a; double b; ...) Returns sum of all arguments. Read more...
double SUMIF(range, criteria [, sumRange])
New!		 Sums the values in a range that meet criteria
double SUMIFS(sum_range, criteria_range1, criteria1[, criteria_range2, criteria2, ...])
New!		 Adds all of its arguments that meet multiple criteria
double TAN(double a) Returns the trigonometric tangent of an angle
double TANH(double x) Returns the hyperbolic tangent of a double value
double TODEGREES(double angrad) Converts an angle measured in radians to an approximately equivalent angle measured in degrees
double TORADIANS(double angdeg) Converts an angle measured in degrees to an approximately equivalent angle measured in radians
double ULP(double d) Returns the size of an ulp of the argument

Statistical functions

Function Description
double AVERAGE(double a; double b; ...) Returns average of all arguments
double AVERAGE(double a; double b; ...) Returns average of all arguments
double AVERAGEIF(range, criteria [, averageRange])
New!		 Returns an average (arithmetic mean) of all the values in a range that meet a given criteria
double AVERAGEIFS(average_range, criteria_range1, criteria1 [, criteria_range2, criteria2, ...])
New!		 Returns an average (arithmetic mean) of all values that meet multiple criteria
long COUNT(value1, [value2,])
New!		 Counts the number of cells that contain numbers
long COUNTA(value1, [value2,])
New!		 Counts the number of cells that are not empty
long COUNTIF(value1, [value2,] condition)
New!		 Counts the number of cells that meet a condition; for example, to count the number of times a particular name appears in the list
long COUNTIFS(criteria_range1, criteria1[, criteria_range2, criteria2...])
New!		 Applies criteria to cells across multiple ranges and counts the number of times all criteria are met
double MAX(double a; double b, ...) Returns the largest of all arguments
double MIN(double a; double b, ...) Returns the smaller of all arguments

Text functions

Function Description
string CHAR(long number) Returns the character specified by a number
string CONCAT(string text1, [string text2],…) Combines the text from multiple strings
long FIND(string find_text, string within_text, [long start_num]) FIND locates one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string
string JOIN(separator [, text1, text2,...])
New! Reporting for Server and Datacenter only		 The JOIN function combines the text from multiple ranges and/or strings adding a specified separator between them.
string LEFT(string text, [long num_chars]) LEFT returns the specified number of characters in the specified text string
long LEN(string text) Returns the number of characters in a text string
string LOWER(string text) Converts all uppercase letters in a text string to lowercase
string MID(string text, long start_num, long num_chars) MID returns a specific number of characters from a text string, starting at the specified position
string REPLACE(string old_text, long start_num, long num_chars, string new_text) Replaces part of a text string, based on the number of characters you specify, with a different text string
string RIGHT(string text, [long num_chars]) RIGHT returns the specified number of last characters in the specified text string
string SUBSTITUTE(string text, string old_text, string new_text, [long instance_num]) Substitutes new_text for old_text in a text string
string TEXT(object value, string format_text) Converts a numeric value to text and lets one specify the display formatting by using special format strings. For example: TEXT("77.77", "$0.00") will result in "$77.77"
string UPPER(string text) Converts text to uppercase
double VALUE(string text) Converts a text string that represents a number to a number
Function Description
long JIRAISSUECOUNT(string jql_request, [string app_link_name])

Returns the number of issues, which conform to specified JQL request. When appLinkName parameter value is not set, the primary JIRA application link is used. Otherwise, a specified application link will be taken. Example: "JIRAISSUECOUNT('Project = Test', 'Your Company JIRA')"

Note for Cloud version: Math Cloud plug-in performs user-impersonated calls to Jira Cloud methods in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. This functionality is available when CelesteCS Math for Confluence Cloud - Jira Integration plug-in is installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported. So, if you need to use Jira-related functions on your Confluence Cloud, please, install the CelesteCS Math for Confluence Cloud - Jira Integration plug-in on your primary Jira Cloud instance.
object[] JIRAISSUEFIELDVALUES(string jql_request, string field_name, object value_if_null [, string app_link_name]) Returns the list of specified issue field values. The type of the value is based on the field value type - string, long, double, datetime or boolean. Custom fields are esupported. Parameters:
  • string jql_request - JQL query to filter out the issues to read field values from.
  • string field_name - the name of the field to read values for. The names are localized, so use the names you see in your Jira GUI.
  • object value_if_null - default value, which will be used when specified field value is absent in an issue. You may specify a string, a number or an input of the other function.
  • string app_link_name - Jira application link name. When this parameter value is not set, the primary JIRA application link is used. Otherwise, a specified application link will be taken.

Example: "JIRAISSUEFIELDVALUES('Project = Test', 'Watchers', 0, 'Your Company JIRA')". Read more about calculations on Jira issue fields here.

Note for Cloud version: Math Cloud plug-in performs user-impersonated calls to Jira Cloud methods in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. This functionality is available when CelesteCS Math for Confluence Cloud - Jira Integration plug-in is installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported. So, if you need to use Jira-related functions on your Confluence Cloud, please, install the CelesteCS Math for Confluence Cloud - Jira Integration plug-in on your primary Jira Cloud instance.
object[] JIRAISSUEFIELDVALUESBYFIELDID(string jql_request, string field_id, object value_if_null [, string app_link_name]) Returns the list of specified issue field values. The type of the value is based on the field value type - string, long, double, datetime or boolean. Parameters:
  • string jql_request - JQL query to filter out the issues to read field values from.
  • string field_id - the identifier of the field to read values for. For custom fields the standard notation like 'customfield_10100' is used. Read more about field identifiers here.
  • object value_if_null - default value, which will be used when specified field value is absent in an issue. You may specify a string, a number or an input of the other function.
  • string app_link_name - Jira application link name. When this parameter value is not set, the primary JIRA application link is used. Otherwise, a specified application link will be taken.

Example: "JIRAISSUEFIELDVALUESBYFIELDID('Project = Test', 'watches', 0, 'Your Company JIRA')". Read more about calculations on Jira issue fields here.

Note for Cloud version: Math Cloud plug-in performs user-impersonated calls to Jira Cloud methods in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. This functionality is available when CelesteCS Math for Confluence Cloud - Jira Integration plug-in is installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported. So, if you need to use Jira-related functions on your Confluence Cloud, please, install the CelesteCS Math for Confluence Cloud - Jira Integration plug-in on your primary Jira Cloud instance.