# Formulas

## How to use the formula editor

The Formula editor is a powerful **Spreadsheet** feature allowing to perform computations and to have dynamic and up to date contents in cells.

Formula-enabled cells are those cells which content starts with the special character **=.**

### Floating formula editor

When editing a formula cell (by double clicking on it or pressing **F2** or **=** keys) a floating editor appears and provides edition means.

This floating editor is always visible and may be moved across the screen if needed. It remains opened even when another tab (or sheet) is active. This allows to easily compose complex formulas with cross-tab references. To close the floating editor, you can either

- press its validation button, which applies the current formula to the cell or
- press its cancel button which resets the cell's value and discards all changes.

### Formulas composition and editing

A formula is an expression that involves:

- Values
- Cells references
- Operators
- Functions

An example of formula may be:

`=SUM(A1, 5, 18, B2) + 42 `

When composing a formula, the floating editor offers some contextual help:

- It provides function documentation and suggestion (navigable with
**Up**and**Down**arrow keys, or pressing**Tab**key) - It automatically inserts cells references (even those on another sheet) by clicking on them
- It automatically resizes so that even long and complex formulas remain easily readable

When the floating editor suggests a list of functions, **Up** and **Do****wn** keys may be used to navigate between available functions and **Tab** key inserts the function at the current caret position. Clicking on a suggested function with a mouse also inserts it at the current caret position.

## List of functions

All available functions are categorized as follows:

### Financial

Function | Result |
---|---|

CUMIPMT | Returns the cumulative interest paid on a loan between start_period and end_period |

CUMPRINC | Returns the cumulative principal paid on a loan between start_period and end_period |

DDB | Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify |

DOLLARDE | Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number |

DOLLARFR | Convert decimal numbers to fractional dollar numbers |

EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year |

FV | Calculates the future value of an investment based on a constant interest rate |

FVSCHEDULE | Returns the future value of an initial principal after applying a series of compound interest rates |

IPMT | Returns the interest payment for an investment for a given period |

IRR | Returns the internal rate of return for a series of cash flows |

ISPMT | Returns the interest paid during a specific period of an investment |

MIRR | Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash |

NOMINAL | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year |

NPER | Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate |

NPV | Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values) |

PPMT | Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate |

PV | Calculates the present value of a loan or an investment, based on a constant interest rate |

RATE | Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions |

RRI | Returns an equivalent interest rate for the growth of an investment |

SLN | Returns the straight-line depreciation of an asset for one period |

TBILLEQ | Returns the bond-equivalent yield for a Treasury bill |

TBILLPRICE | Returns the price per $100 face value for a Treasury bill |

TBILLYIELD | Returns the yield for a Treasury bill |

XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic |

### Date and time

Function | Result |
---|---|

DATE | Returns the serial number of a particular date |

DATEVALUE | Converts a date that is stored as text to a serial number |

DAY | Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31 |

DAYS | Returns the number of days between two dates |

DAYS360 | Returns the number of days between two dates based on a 360-day year (twelve 30-day months) |

EDATE | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date) |

EOMONTH | Returns the serial number for the last day of the month that is the indicated number of months before or after start_date |

HOUR | Converts a serial number to an hour |

INTERVAL | Returns a temporal representation of a number |

ISOWEEKNUM | Returns the number of the ISO week number of the year for a given date |

MINUTE | Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59 |

MONTH | Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December) |

NETWORKDAYS | Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays |

NOW | Returns the serial number of the current date and time |

PDURATION | Returns the number of periods required by an investment to reach a specified value |

PMT | Calculates the payment for a loan based on constant payments and a constant interest rate |

SECOND | Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59 |

SYD | Returns the sum-of-years' digits depreciation of an asset for a specified period |

TIME | Returns the decimal number for a particular time |

TIMEVALUE | Returns the decimal number of the time represented by a text string |

TODAY | Returns the serial number of the current date |

WEEKDAY | Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default |

WEEKNUM | Returns the week number of a specific date |

WORKDAY | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date) |

YEAR | Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999 |

YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates |

### Engineering

Function | Result |
---|---|

BESSELI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments |

BESSELJ | Returns the Bessel function |

BESSELK | Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments |

BESSELY | Returns the Bessel function, which is also called the Weber function or the Neumann function |

BIN2DEC | Converts a binary number to decimal |

BIN2HEX | Converts a binary number to hexadecimal |

BIN2OCT | Converts a binary number to octal |

BITAND | Returns a bitwise 'AND' of two numbers |

BITLSHIFT | Returns a number shifted left by the specified number of bits |

BITOR | Returns a bitwise 'OR' of two numbers |

BITRSHIFT | Returns a value number shifted right by shift_amount bits |

BITXOR | Returns a bitwise 'XOR' of two numbers |

COMPLEX | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj |

CONVERT | Converts a number from one measurement system to another |

DEC2BIN | Converts a decimal number to binary |

DEC2HEX | Converts a decimal number to hexadecimal |

DEC2OCT | Converts a decimal number to octal |

DELTA | Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise |

ERF | Returns the error function integrated between lower_limit and upper_limit |

ERFC | Returns the complementary ERF function integrated between x and infinity |

GESTEP | Tests whether a number is greater than a threshold value |

HEX2BIN | Converts a hexadecimal number to binary |

HEX2DEC | Converts a hexadecimal number to decimal |

HEX2OCT | Converts a hexadecimal number to octal |

IMABS | Returns the absolute value (modulus) of a complex number |

IMAGINARY | Returns the imaginary coefficient of a complex number |

IMARGUMENT | Returns the argument theta, an angle expressed in radians |

IMCONJUGATE | Returns the complex conjugate of a complex number |

IMCOS | Returns the cosine of a complex number |

IMCOSH | Returns the hyperbolic cosine of a complex number |

IMCOT | Returns the cotangent of a complex number |

IMCSC | Returns the cosecant of a complex number |

IMCSCH | Returns the hyperbolic cosecant of a complex number |

IMDIV | Returns the quotient of two complex numbers |

IMEXP | Returns the exponential of a complex number |

IMLN | Returns the natural logarithm of a complex number |

IMLOG10 | Returns the base 10 logarithm of a complex number |

IMLOG2 | Returns the base 2 logarithm of a complex number |

IMPOWER | Returns a complex number raised to an integer power |

IMPRODUCT | Returns the product of complex numbers |

IMREAL | Returns the real coefficient of a complex number |

IMSEC | Returns the secant of a complex number |

IMSECH | Returns the hyperbolic secant of a complex number |

IMSIN | Returns the sine of a complex number |

IMSINH | Returns the hyperbolic sine of a complex number |

IMSQRT | Returns the square root of a complex number |

IMSUB | Returns the difference between two complex numbers |

IMSUM | Returns the sum of complex numbers |

IMTAN | Returns the tangent of a complex number |

OCT2BIN | Converts an octal number to binary |

OCT2DEC | Converts an octal number to decimal |

OCT2HEX | Converts an octal number to hexadecimal |

### Information

Function | Result |
---|---|

EQ | Tests if two values are equal |

ISBLANK | Returns TRUE if the value is blank |

ISBINARY | Returns TRUE if the value is binary |

ISERR | Returns TRUE if the value is any error value except #N/A |

ISERROR | Returns TRUE if the value is any error value |

ISEVEN | Returns TRUE if the number is even |

ISLOGICAL | Returns TRUE if the value is a logical value |

ISNA | Returns TRUE if the value is the #N/A error value |

ISNONTEXT | Returns TRUE if the value is not text |

ISNUMBER | Returns TRUE if the value is a number |

ISODD | Returns TRUE if the number is odd |

ISTEXT | Returns TRUE if the value is text |

LTE | Returns true if number1 is lower than or equal to number2 |

NA | Returns the error value #N/A |

NE | Returns true if number1 does not equal number2 |

Statistical

Function | Result |
---|---|

AVEDEV | Returns the average of the absolute deviations of data points from their mean |

AVERAGE | Returns the average of its arguments |

BETA.DIST / BETADIST | Returns the the cumulative beta probability density function |

BETA.INV / BETAINV | Returns the inverse of the beta cumulative probability density function |

BINOM.DIST / BINOMDIST | Returns the individual term binomial distribution probability |

BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value |

CHISQ.DIST | Returns the chi-squared distribution |

CHISQ.DIST.RT | Returns the right-tailed probability of the chi-squared distribution |

CHISQ.INV | Returns the inverse of the left-tailed probability of the chi-squared distribution |

CHISQ.INV.RT | Returns the inverse of the right-tailed probability of the chi-squared distribution |

CONFIDENCE | Returns the confidence interval for a population mean, using a normal distribution |

CONFIDENCE.NORM | Returns the confidence interval for a population mean, using a normal distribution |

CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student's t distribution |

CORREL | Returns the correlation coefficient of the Array1 and Array2 cell ranges |

COUNT | Counts how many numbers are in the list of arguments |

COUNTA | Counts how many values are in the list of arguments |

COUNTBLANK | Counts the number of blank cells within a range |

COUNTIF | Counts the number of cells that meet a criterion |

COUNTIFS | Applies criteria to cells across multiple ranges and counts the number of times all criteria are met |

COUNTIN | Counts the number of cells containing Value |

COUNTUNIQUE | Counts the number of unique values in the given ranges |

COVARIANCE.P | Returns population covariance, the average of the products of deviations for each data point pair in two data sets |

COVARIANCE.S | Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets |

DEVSQ | Returns the sum of squares of deviations of data points from their sample mean |

EXPON.DIST / EXPONDIST | Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash |

F.DIST / FDIST | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets |

F.DIST.RT/ FDISTRT | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets |

F.INV / FINV | Returns the inverse of the F probability distribution. If p = F.DIST(x,...), then F.INV(p,...) = x |

F.INV.RT / FINVRT | Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(p,...) = x |

FISHER | Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed |

FISHERINV | Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x |

FORECAST | Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression |

FREQUENCY | Calculates how often values occur within a range of values, and then returns a vertical array of numbers |

GAMMA | Returns the Gamma function value |

GAMMA.DIST / GAMMADIST | Returns the gamma distribution |

GAMMA.INV / GAMMAINV | Returns the inverse of the gamma cumulative distribution |

GAMMALN | Returns the natural logarithm of the gamma function, Î“(x) |

GAMMALN.PRECISE | Returns the natural logarithm of the gamma function, Î“(x) |

GAUSS | Returns 0.5 less than the standard normal cumulative distribution |

GEOMEAN | Returns the geometric mean |

GROWTH | Returns values along an exponential trend |

HARMEAN | Returns the harmonic mean |

HYPGEOM.DIST / HYPGEOMDIST | Returns the hypergeometric distribution |

LARGE | Returns the k-th largest value in a data set |

LOGNORM.DIST / LOGNORMDIST | Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev |

LOGNORM.INV / LOGNORMINV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev |

MAX | Returns the maximum value in a list of arguments |

MAXA | Returns the largest value in a list of arguments |

MEDIAN | Returns the median of the given numbers. The median is the number in the middle of a set of numbers |

MIN | Returns the minimum value in a list of arguments |

MINA | Returns the smallest value in the list of arguments |

NEGBINOM.DIST / NEGBINOMDIST | Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success |

NORM.DIST / NORMDIST | Returns the normal distribution for the specified mean and standard deviation |

NORM.INV / NORMINV | Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation |

NORM.S.DIST / NORMSDIST | Returns the standard normal distribution (has a mean of zero and a standard deviation of one) |

NORM.S.INV / NORMSINV | Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one |

PEARSON | Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets |

PERMUTATIONA | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects |

PHI | Returns the value of the density function for a standard normal distribution |

POISSON.DIST / POISSONDIST | Returns the Poisson distribution |

RANK.EQ / RANKEQ | Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned |

RSQ | Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's |

SKEW | Returns the skewness of a distribution |

SKEW.P / SKEWP | Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean |

SLOPE | Returns the slope of the linear regression line through data points in known_y's and known_x's |

SMALL | Returns the k-th smallest value in a data set |

STANDARDIZE | Returns a normalized value from a distribution characterized by mean and standard_dev |

STDEV.P / STDEVP | Calculates standard deviation based on the entire population given as arguments |

STDEV.S / STDEVS | Estimates standard deviation based on a sample |

STDEVA | Estimates standard deviation based on a sample |

STDEVP | Calculates standard deviation based on the entire population given as arguments |

STDEVPA | Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean) |

STEYX | Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x |

T.DIST / TDIST | Returns the Student's left-tailed t-distribution |

T.DIST.2T / TDIST2T | Returns the two-tailed Student's t-distribution |

T.DIST.RT / TDISTRT | Returns the right-tailed Student's t-distribution |

T.INV / TINV | Returns the left-tailed inverse of the Student's t-distribution |

T.INV.2T / TINV2T | Returns the two-tailed inverse of the Student's t-distribution |

VAR.P / VARP | Calculates variance based on the entire population (ignores logical values and text in the population) |

VAR.S / VARS | Estimates variance based on a sample (ignores logical values and text in the sample) |

VARA | Estimates variance based on a sample |

VARPA | Calculates variance based on the entire population |

WEIBULL.DIST / WEIBULLDIST | Returns the Weibull distribution |

### Maths

Function | Result |
---|---|

ABS | Returns the absolute value of a number |

ACOS | Returns the arccosine of a number |

ACOSH | Returns the inverse hyperbolic cosine of a number |

ACOT | Returns the arccotangent of a number |

ACOTH | Returns the hyperbolic arccotangent of a number |

ADD | Returns the sum of two numbers |

ARABIC | Converts a Roman number to Arabic, as a number |

ASIN | Returns the arcsine of a number |

ASINH | Returns the inverse hyperbolic sine of a number |

ATAN | Returns the arctangent of a number |

ATAN2 | Returns the arctangent from x- and y-coordinates |

ATANH | Returns the inverse hyperbolic tangent of a number |

AVERAGEA | Returns the numerical average value of its arguments |

AVERAGEIF | Returns the average of a range depending on criteria |

BASE | Converts a number into a text representation with the given radix (base) |

CEILING | Rounds a number to the nearest integer or to the nearest multiple of significance |

CEILING.MATH / CEILINGMATH | Rounds a number up, to the nearest integer or to the nearest multiple of significance |

CEILING.PRECISE / CEILINGPRECISE | Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. |

COMBIN | Returns the number of combinations for a given number of objects |

COMBINA | Returns the number of combinations with repetitions for a given number of items |

COS | Returns the cosine of a number |

COSH | Returns the hyperbolic cosine of a number |

COT | Returns the cotangent of an angle |

COTH | Returns the hyperbolic cotangent of a number |

CSC | Returns the cosecant of an angle |

CSCH | Returns the hyperbolic cosecant of an angle |

DECIMAL | Converts a text representation of a number in a given base into a decimal number |

DEGREES | Converts radians to degrees |

DIVIDE | Returns the result of the division of two numbers |

EVEN | Rounds a number up to the nearest even integer |

EXP | Returns e raised to the power of a given number |

FACT | Returns the factorial of a number |

FACTDOUBLE | Returns the double factorial of a number |

FLOOR | Rounds a number down, toward zero |

FLOOR.MATH | Rounds a number down, to the nearest integer or to the nearest multiple of significance |

FLOOR.PRECISE | Rounds a number down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down. |

GCD | Returns the greatest common divisor |

INT | Rounds a number down to the nearest integer |

ISO.CEILING | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance |

LCM | Returns the least common multiple |

LN | Returns the natural logarithm of a number |

LOG | Returns the logarithm of a number to a specified base |

LOG10 | Returns the base-10 logarithm of a number |

MINUS | Return the result of number1 minus number2 |

MOD | Returns the remainder from division |

MROUND | Returns a number rounded to the desired multiple |

MULTINOMIAL | Returns the multinomial of a set of numbers |

MULTIPLY | Returns the product of two numbers |

ODD | Rounds a number up to the nearest odd integer |

PI | Returns the value of pi |

POWER | Returns the result of a number raised to a power |

PRODUCT | Multiplies its arguments |

QUOTIENT | Returns the integer portion of a division |

RADIANS | Converts degrees to radians |

RAND | Returns a random number between 0 and 1 |

RANDBETWEEN | Returns a random number between the numbers you specify |

ROUND | Rounds a number to a specified number of digits |

ROUNDDOWN | Rounds a number down, toward zero |

ROUNDUP | Rounds a number up, away from zero |

SEC | Returns the secant of an angle |

SECH | Returns the hyperbolic secant of an angle |

SERIESSUM | Returns the sum of a power series |

SIGN | Returns the sign of a number |

SIN | Returns the sine of the given angle |

SINH | Returns the hyperbolic sine of a number |

SQRT | Returns a positive square root |

SQRTPI | Returns the square root of (number * pi) |

SUM | Adds its arguments |

SUBTOTAL | Returns a subtotal in a list or database |

SUMIF | Sums the values in a range that meet criteria that you specify |

SUMIFS | Sums the values in multiples ranges that meet multiple criterisa that you specify |

SUMPRODUCT | Multiplies corresponding components in the given arrays, and returns the sum of those products |

SUMSQ | Returns the sum of the squares of the arguments |

SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays |

SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays |

SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays |

TAN | Returns the tangent of a number |

TANH | Returns the hyperbolic tangent of a number |

TRUNC | Truncates a number to an integer |

### Logical

Function | Result |
---|---|

AND | Returns TRUE if all of its arguments are TRUE |

FALSE | Returns the logical value FALSE |

IF | Specifies a logical test to perform |

IFERROR | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula |

IFNA | Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression |

NOT | Reverses the logic of its argument |

OR | Returns TRUE if any argument is TRUE |

SWITCH | Evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value |

TRUE | Returns the logical value TRUE |

XOR | Returns a logical exclusive OR of all arguments |

### Lookup and reference

Function | Result |
---|---|

CHOOSE | Uses index_num to return a value from the list of value arguments |

COLUMN | Returns the column number of the given cell reference |

COLUMNS | Returns the number of columns in an array or reference |

HLOOKUP | Looks in the top row of an array and returns the value of the indicated cell |

INDEX | Uses an index to choose a value from a reference or array |

MATCH | Searches for a specified item in a range of cells, and then returns the relative position of that item in the range |

ROW | Returns the row number of a reference |

ROWS | Returns the number of rows in a reference or array |

TRANSPOSE | Returns a vertical range of cells as a horizontal range, or vice versa |

UNIQUE | Returns a list of unique values in a list or range |

VLOOKUP | Finds things in a table or a range by row |

### Text

Function | Result |
---|---|

CHAR | Returns the character specified by the code number |

CLEAN | Removes all nonprintable characters from text |

CODE | Returns a numeric code for the first character i a text string |

CONCATENATE | Joins several text items into one text item |

DOLLAR | Converts a number to text using dollars currency format, with the decimals rounded to the number of places you specify |

EXACT | Checks to see if two text values are identical |

FIND | Finds one text value within another (case-sensitive) |

FIXED | Formats a number as text with a fixed number of decimals |

HTML2TEXT | Converts HTML to text |

LEFT | Returns the leftmost characters from a text value |

LEN | Returns the number of characters in a text string |

LOWER | Converts text to lowercase |

MID | Returns a specific number of characters from a text string starting at the position you specify |

NUMBERS | Returns values that are numbers |

NUMERAL | Returns a formatted number value |

PROPER | Capitalizes the first letter in each word of a text value |

REGEXEXTRACT | Extracts matching substrings according to a regular expression |

REGEXMATCH | Returns true if a piece of text matches a regular expression |

REGEXREPLACE | Replaces part of a text string with a different text string using regular expressions |

REPLACE | Replaces characters within text |

REPT | Repeats text a given number of times |

RIGHT | Returns the rightmost characters from a text value |

ROMAN | Converts an arabic numeral to roman, as text |

SEARCH | Locate 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 |

SPLIT | Divides text around a specified character |

SUBSTITUTE | Substitutes new text for old text in a text string |

T | Converts its arguments to text |

TRIM | Removes spaces from text |

UNICHAR | Returns the Unicode character that is references by the given numeric value |

UNICODE | Returns the number (code point) that corresponds to the first character of the text |

UPPER | Converts text to uppercase |

VALUE | Converts a text argument to a number |

## New formulas system

Since November 2022, we have decided to move to migrate our solution to a new one to offer a better experience of our users and enable more use cases. It is impacting the current functions proposal at 2 levels:

- A few functions are not available anymore (please contact the dedicated support portal if you face any issues)
- A few functions are named differently

### New functions available

Function | Result |
---|---|

FORMULATEXT | Returns a formula in a given cell as a string. |

OFFSET | Returns the value of a cell offset by a certain number of rows and columns from a given reference point. |

CHIDIST | Returns probability of chi-square right-side distribution. |

CHIINV | Returns inverse of chi-square right-side distribution. |

CHIINVRT | Returns inverse of chi-square right-side distribution. |

CHIQ.TEST | Returns 'CHISQ.TEST' value for a dataset. |

CHITEST | Returns 'CHISQ.TEST' value for a dataset. |

COVAR | Returns the covariance between two data sets, population normalized. |

CRITBINOM | Returns inverse binomial distribution value. |

F.DIST | Returns value of F distribution. |

F.TEST / FTEST | Returns f-test value for a dataset. |

LOGINV | Returns value of inverse lognormal distribution. |

MAXIFS | Returns the maximum value of the cells in a range that meet a set of criteria. |

MINIFS | Returns the minimum value of the cells in a range that meet a set of criteria. |

POISSON | Returns density of Poisson distribution. |

T.TEST TTEST | Returns t-test value for a dataset. |

VAR | Returns variance of a sample. |

WEIBULL | Returns density of Weibull distribution. |

Z.TEST / ZTEST | Returns z-test value for a dataset. |

DATEDIF | Calculates distance between two dates, in provided unit parameter. |

NETWORKDAYS.INTL | Returns the number of working days between two given dates. |

WORKDAYS.INTL | Returns the working day number of days from start day. |

ISFORMULA | Checks whether referenced cell is a formula. |

ISREF | Returns TRUE if provided value is #REF! error. |

SHEET | Returns sheet number of a given value or a formula sheet number if no argument is provided. |

SHEETS | Returns number of sheet of a given reference or number of all sheets in workbook when no argument is provided. |

MMULT | Calculates the array product of two arrays. |

MEDIANPOOL | Calculates a smaller range which is a median of a window_size, in a given range, for every stride element. |

MAXPOOL | Calculates a smaller range which is a maximum of a window_size, in a given range, for every stride element. |

FILTER | Filters an array, based on multiple conditions (boolean arrays) |

ARRAYFORMULA | Enables the array arithmetic mode for a single formula |

ARRAY_CONSTRAIN | Truncates an array to given dimensions |

### Functions no longer supported

Function | Result |
---|---|

ACCRINT | Returns the accrued interest for a security that pays periodic interest |

AGGREGATE | Returns an aggregate in a list or database |

ARGS2ARRAY | Converts the given arguments to an array |

AVERAGEIFS | Returns the average of a range depending on multiple criteria |

BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution |

FLATTEN | Converts a range, or multiple ranges, into a single column. |

INTERCEPT | Returns the intercept of the linear regression line |

JOIN | Joins texts from a range, using a separator (comma by default) |

KURT | Returns the kurtosis of a data set |

LINEST | Calculates the statistics for a line by using the 'least squares' method to calculate a straight line that best fits your data, and then returns an array that describes the line |

LOGEST | Calculates an exponential curve that fits your data and returns an array of values that describes the curve |

MODEMULT | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data |

MODESNGL | Returns the most frequently occurring, or repetitive, value in an array or range of data |

NUMBER | Returns values that are numbers |

PERCENTILEEXC | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive |

PERCENTILEINC | Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive |

PERCENTRANKEXC | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |

PERCENTRANKINC | Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set |

PERMUT | Returns the number of permutations for a given number of objects that can be selected from number objects |

PROB | Returns the probability that values in a range are between two limits |

QUARTILEEXC | Returns the quartile of the data set, based on percentile values from 0..1, exclusive |

QUARTILEINC | Returns the quartile of a data set, based on percentile values from 0..1, inclusive |

RANKAVG | Returns the rank of a number in a list of numbers: its size relative to other values in the list, if more than one value has the same rank, the average rank is returned |

REGEXEXTRACT | Extracts matching substrings according to a regular expression |

REGEXMATCH | Returns true if a piece of text matches a regular expression |

REGEXREPLACE | Replaces part of a text string with a different text string using regular expressions |

TREND | Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's |

TRIMMEAN | Returns the mean of the interior of a data set |

XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic |

### Functions renamed

Current function name | New function name | Result |
---|---|---|

ADD | HF.ADD | Adds two values. |

BETADIST | BETADIST BETA.DIST | Returns the the cumulative beta probability density function. |

BETAINV | BETAINV BETA.INV | Returns the inverse of the beta cumulative probability density function. |

BINOMINV | BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |

BINOMDIST | BINOMDIST BINOM.DIST | Returns the individual term binomial distribution probability. |

CEILINGMATH | CEILING.MATH | Rounds a number up to the nearest integer or to the nearest multiple of significance. Mode controls whether Number is rounded toward or away from for negative numbers |

CEILINGPRECISE | CEILING.PRECISE | Rounds a number up to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. However, if the number or the significance is zero, zero is returned. |

COVARIANCEP | COVARIANCEP COVARIANCE.P | Returns the covariance between two data sets, population normalized. |

COVARIANCES | COVARIANCES COVARIANCE.S | Returns the covariance between two data sets, sample normalized. |

DIVIDE | HF.DIVIDE | Divides two values. |

EQ | HF.EQ | Tests if the two values are equals. |

EXPONDIST | EXPONDIST EXPON.DIST | Returns density of a exponential distribution. |

FDISTRT | FDISTRT F.DIST.RT | Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. |

FINVRT | FINVRT F.INV.RT | Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(p,...) = x. |

GAMMADIST | GAMMADIST GAMMA.DIST | Returns density of Gamma distribution. |

GAMMAINV | GAMMAINV GAMMA.INV | Returns inverse Gamma distribution value. |

GTE | HF.GTE | Tests two values for greater-equal relation. |

HYPGEOMDIST | HYPGEOMDIST HYPGEOM.DIST | Returns density of hypergeometric distribution. |

LOGNORMDIST | LOGNORMDIST LOGNORM.DIST | Returns density of lognormal distribution. |

LOGNORMINV | LOGNORMINV LOGNORM.INV | Returns value of inverse lognormal distribution. |

LT | HF.LT | Tests two values for less-than relation. |

LTE | HF.LTE | Tests two values for less-equal relation. |

MINUS | HF.MINUS | Subtracts two values. |

MULTIPLY | HF.MULTIPLY | Multiplies two values. |

NE | HF.NE | Tests two values for inequality. |

NEGBINOMDIST | NEGBINOMDIST NEGBINOM.DIST | Returns density of negative binomial distribution. |

NORMDIST | NORMDIST NORM.DIST | Returns density of normal distribution. |

NORMINV | NORMINV NORM.INV | Returns value of inverse normal distribution. |

NORMSDIST | NORMSDIST NORM.S.DIST | Returns the standard normal distribution (has a mean of zero and a standard deviation of one). |

NORMSINV | NORM.S.INV | Returns value of inverse normal distribution. |

POISSONDIST | POISSONDIST POISSON.DIST | Returns density of Poisson distribution. |

POW | HF.POW | Computes power of two values. |

SKEWP | SKEWP SKEW.P | Returns skeweness of a population. |

STDEVP | STDEVP STDEV.P | Calculates standard deviation based on the entire population given as arguments. |

STDEVS | STDEVS STDEV.S | Estimates standard deviation based on a sample. |

TDIST | TDIST T.DIST | Returns density of Student-t distribution, both-sided or right-tailed. |

TDIST2T | TDIST2T T.DIST.2T | Returns density of Student-t distribution, both-sided. |

TDISTRT | TDISTRT T.DIST.RT | Returns density of Student-t distribution, right-tailed. |

TINV | TINV T.INV | Returns inverse Student-t distribution, both-sided. |

TINV2T | TINV2T T.INV.2T | Returns inverse Student-t distribution, both-sided. |

VARP | VARP VAR.P | Calculates variance based on the entire population (ignores logical values and text in the population). |

VARS | VARS VAR.S | Estimates variance based on a sample (ignores logical values and text in the sample). |

WEIBULLDIST | WEIBULLDIST | Returns density of Weibull distribution. |