{autotoc }

Report columns calculated with UPPER, LEFT, LEN, ROUND, IF and DAYS

Example: functions from this page used in Reporting Table Item columns. The same functions work in CelesteCS Math, Conditions and Reporting formulas.

Operations

The following operations are supported:

Operation Description
a + b Addition
a - b Subtraction
a * b Multiplication
a / b Division
a && b Logical AND
a || b Logical OR
a & b Concatenation
a ^ b Powering
- a Negation
+ a Positivation

Array functions

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

Confluence-related functions

Function Description
string GETCONFLUENCEPAGEDATA(string page) Returns the contents of the specified page as plain text
string GETCONFLUENCEPAGEDATAASHTML(string page) Returns the contents of the specified page as HTML
long GETCONFLUENCEPAGEELEMENTCOUNT(string page, string selectQuery) 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]) Returns the contents of the specified page element as plain text
string GETCONFLUENCEPAGEELEMENTDATAASHTML(string page, string selectQuery, [long index]) 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) 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
datetime EDATE(datetime start_date, long months)
New!		 Returns the date that is the indicated number of months before or after start_date. If the resulting day of month does not exist in the target month, it is clamped to that month’s last day.
datetime EOMONTH(datetime start_date, long months)
New!		 Returns the last day of the month that is the indicated number of months before or after start_date.
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) 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) 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

Information functions

Function Description
boolean ISBLANK(value)
New!		 Returns TRUE if value refers to an empty cell or value, FALSE otherwise.
boolean ISNUMBER(value)
New!		 Returns TRUE if value is a number, FALSE otherwise.
boolean ISTEXT(value)
New!		 Returns TRUE if value is text, FALSE otherwise.

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 JSONEXTRACTPATH(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. Only the returned value is calculated, so IF(B2=0; 0; A2/B2) does not fail when B2 is 0 (since Math 27.0.0).
value IFERROR(value aValue, value valueIfError) New! Returns aValue, or valueIfError when calculating aValue fails: division by zero, a lookup that finds nothing, an error in a referenced cell, NaN. Example: IFERROR(XLOOKUP(A2; Table1.A2:A9; Table1.C2:C9); "-"). Only the value that is returned is calculated (since Math 27.1.0; in Cloud since 19 October 2026).
value IFNA(value aValue, value valueIfNa) New! Like IFERROR, but replaces only the "not found" result of XLOOKUP, XMATCH, MATCH and LOOKUP (Excel's #N/A) and keeps every other error visible. Example: IFNA(MATCH("Kiwi"; Table1.A2:A9; 0); 0) (since Math 27.1.0; in Cloud since 19 October 2026).
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. Only the returned result is calculated (since Math 27.0.0).
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
object CHOOSE(long index_num, value1, [value2, ...])
New!		 Returns a value from a list of values, chosen by the index_num position (1-based). Only the chosen value is calculated (since Math 27.0.0).
string HLOOKUP(string lookup_value, range lookup_range, long row, [boolean range_lookup]) 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. The optional range_lookup (since Math 27.0.0): FALSE() (default) – exact match; TRUE() – approximate match in a top row sorted ascending.
object INDEX(range array, long row_num, [long column_num])
New!		 Returns the value at the given row/column position within array. If array has a single row or a single column, the corresponding row_num or column_num argument is optional. array may also be an array such as MAKEARRAY(10;20;30): INDEX(MAKEARRAY(10;20;30); 2) = 20 (since Math 27.0.0).
object LOOKUP(object lookup_value, range lookup_vector, [range result_vector])
New!		 Looks for a value in a one-row or one-column range (sorted ascending), and returns a value from the same position in a second one-row or one-column range.
long MATCH(object lookup_value, range lookup_array, [long match_type])
New!		 Returns the relative position of lookup_value in lookup_array. match_type 1 (default) finds the largest value less than or equal to lookup_value, in an array sorted ascending; 0 finds the first value exactly equal to lookup_value; -1 finds the smallest value greater than or equal to lookup_value, in an array sorted descending.
object XLOOKUP(object lookup_value, range lookup_array, range return_array, [object if_not_found])
New!		 Searches lookup_array for an exact match of lookup_value and returns the value at the same position in return_array. lookup_array and return_array must be the same size. If lookup_value is not found, if_not_found is returned when given; otherwise the formula fails. Only exact matching is supported (Excel's match_mode and search_mode arguments are not). Example: 'XLOOKUP("b", {"a","b","c"}, {10,20,30})' returns 20.
long XMATCH(object lookup_value, range lookup_array)
New!		 Returns the relative position (starting from 1) of the first exact match of lookup_value in lookup_array. If lookup_value is not found, the formula fails. Only exact matching is supported.
string VLOOKUP(string lookup_value, range lookup_range, long column, [boolean range_lookup]) Finds things in a range by row. For example, looks up a price of an automotive part by the part number. The optional range_lookup (since Math 27.0.0): FALSE() (default) – exact match; TRUE() – approximate match, the last row whose first value is less than or equal to lookup_value (the first column must be sorted ascending).

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 CEILING(double a) Same as CEIL; provided under its Excel-compatible name
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 DEGREES(double angle)
New!		 Converts an angle measured in radians to an approximately equivalent angle measured in degrees. Same as TODEGREES.
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
long INT(double number)
New!		 Rounds a number down to the nearest integer, toward negative infinity.
double LN(double number)
New!		 Returns the natural logarithm (base e) of a number. Same as LOG.
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 MOD(double number; double divisor)
New!		 Returns the remainder after number is divided by divisor. The result takes the sign of divisor.
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() 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 POWER(double number; double power)
New!		 Returns the result of a number raised to a power. Same as POW.
double PRODUCT(double a; double b; ...)
New!		 Multiplies all its arguments together.
double RADIANS(double angle)
New!		 Converts an angle measured in degrees to an approximately equivalent angle measured in radians. Same as TORADIANS.
double RAND()
New!		 Returns a random real number greater than or equal to 0 and less than 1. Same as RANDOM.
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
double ROUND(double a [, long num_digits]) Returns the closest double to the argument, with ties rounding up. The decimal value is rounded: ROUND(1.005; 2) = 1.01, as in Excel (since Math 27.0.0).
double ROUNDDOWN(double number [, long num_digits])
New!		 Rounds a number down, toward zero.
double ROUNDUP(double number [, long num_digits])
New!		 Rounds a number up, away from zero.
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 SIGN(double number)
New!		 Returns the sign of a number: 1 if positive, -1 if negative, 0 if zero. Same as SIGNUM.
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. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
double SUMIFS(sum_range, criteria_range1, criteria1[, criteria_range2, criteria2, ...])
New!		 Adds all of its arguments that meet multiple criteria. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
double SUMPRODUCT(range array1; range array2; ...)
New!		 Multiplies corresponding elements of the given arrays or ranges together, then sums those products.
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 TRUNC(double number [, long num_digits])
New!		 Truncates a number toward zero, to the specified number of decimal places (default 0). Unlike INT, does not round toward negative infinity.
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 AVERAGEIF(range, criteria [, averageRange]) Returns an average (arithmetic mean) of all the values in a range that meet a given criteria. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
double AVERAGEIFS(average_range, criteria_range1, criteria1 [, criteria_range2, criteria2, ...]) Returns an average (arithmetic mean) of all values that meet multiple criteria. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
long COUNT(value1, [value2,]) Counts the number of cells that contain numbers
long COUNTA(value1, [value2,]) Counts the number of cells that are not empty
long COUNTBLANK(range)
New!		 Counts empty cells in a range.
long COUNTIF(value1, [value2,] condition) 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...]) Applies criteria to cells across multiple ranges and counts the number of times all criteria are met. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
double LARGE(range array, long k)
New!		 Returns the k-th largest value in a data set. k=1 returns the largest value.
double MAX(double a; double b, ...) Returns the largest of all arguments
double MAXIFS(max_range, criteria_range1, criteria1 [, criteria_range2, criteria2, ...])
New!		 Returns the maximum value among cells specified by a given set of conditions. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
double MEDIAN(double a; double b; ...)
New!		 Returns the median (the middle value) of the given numbers.
double MIN(double a; double b, ...) Returns the smaller of all arguments
double MINIFS(min_range, criteria_range1, criteria1 [, criteria_range2, criteria2, ...])
New!		 Returns the minimum value among cells specified by a given set of conditions. A range may also be an array such as MAKEARRAY(1;2;3) (since Math 27.0.0).
long RANK(double number, range ref, [long order])
New!		 Returns the rank of a number within a list: how large or small it is relative to other values. Tied values receive the same rank, and the next distinct value’s rank skips the tie count. order=0 (default) ranks descending (largest = 1); any non-zero order ranks ascending (smallest = 1).
double SMALL(range array, long k)
New!		 Returns the k-th smallest value in a data set. k=1 returns the smallest value.
double STDEV(double a; double b; ...)
New!		 Estimates standard deviation based on a sample (uses the n-1 method).
double VAR(double a; double b; ...)
New!		 Estimates variance based on a sample (uses the n-1 method).

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
boolean EXACT(string text1, string text2)
New!		 Compares two text strings and returns TRUE if they are exactly the same (case-sensitive), FALSE otherwise.
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!		 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 PROPER(string text)
New!		 Capitalizes the first letter of each word in a text string.
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 REPT(string text, long number_times)
New!		 Repeats text a given number of times.
string RIGHT(string text, [long num_chars]) RIGHT returns the specified number of last characters in the specified text string
long SEARCH(string find_text, string within_text, [long start_num])
New!		 Returns the position of the first occurrence of find_text within within_text, ignoring case. Unlike FIND, wildcards are not supported.
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 TRIM(string text)
New!		 Removes leading, trailing and repeated spaces from a text string, leaving single spaces between words.
string UPPER(string text) Converts text to uppercase
double VALUE(string text) Converts a text string that represents a number to a number. Thousands separators and percent are understood: VALUE("1,234.5") = 1234.5, VALUE("50%") = 0.5 (since Math 27.0.0).

Jira-related functions

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 performs user-impersonated calls to Jira Cloud in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. Jira integration is built into CelesteCS Math for Confluence Cloud, so no separate integration plug-in is needed: just make sure the app is also installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported.
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 supported. 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 performs user-impersonated calls to Jira Cloud in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. Jira integration is built into CelesteCS Math for Confluence Cloud, so no separate integration plug-in is needed: just make sure the app is also installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported.
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 performs user-impersonated calls to Jira Cloud in JIRAISSUECOUNT, JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID functions. Jira integration is built into CelesteCS Math for Confluence Cloud, so no separate integration plug-in is needed: just make sure the app is also installed on your primary Jira Cloud instance. Specifying non-primary Jira Cloud instances is not supported.