Contents[Hide]

## Date and time functions

Function | Description |
---|---|

datetime DATE(long year, long month, long day) | Returns the sequential serial number that represents a particular date |

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 MINUTE(datetime date) | Returns the minutes of the specified time value. The minute is given as a number, ranging from 0 to 59 |

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 |

## 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 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(text, old_text, new_text, [instance_num]) | Substitutes new_text for old_text in a text string |

string TEXT(value, 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 |

## Working with JIRA 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:**If you need to add any other function or operand, feel free to contact our support team.