Every function in Sheets, with a worked example
The 482 functions the Sheets formula engine works out, in the categories Excel's Formulas tab uses. Each has its arguments, what it does, and an example that a test types into Sheets and checks against the answer Excel gives.
Sheets has XLOOKUP, VLOOKUP, INDEX and MATCH, SUMIFS and COUNTIFS, IFS and SWITCH, TEXTJOIN, TEXT with its format codes, NETWORKDAYS, and the loan functions PMT, RATE, IPMT and PPMT. It has Excel's dynamic arrays: FILTER, UNIQUE, SORT, SORTBY and SEQUENCE spill their results into the cells beside them, arithmetic works on whole ranges, array constants such as {1,2,3} can be typed, and A2# is the whole block a cell spilled. A formula shows #SPILL! when something is already in a cell it would spill into. LET and LAMBDA work inside a formula, with MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY, and a LAMBDA named in Name Manager can be called by that name from any cell. GROUPBY and PIVOTBY total a table by its groups and spill the summary. It also has the newer functions that reshape an array, VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, TOCOL, WRAPROWS and TEXTSPLIT; REGEXTEST, REGEXEXTRACT and REGEXREPLACE; the statistical distributions with their tests; the Engineering functions, CONVERT and the complex numbers among them; the database functions; and the bond and security functions. A workbook opened from .xlsx shows the value Excel saved for any formula Sheets cannot work out, and keeps that value in place of the formula.
The table the examples read
The examples that refer to cells read this table, typed into A1 of an empty sheet. The rest stand on their own and can be pasted into any cell.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Region | Amount | Date | Units | Cash flow | Value |
| 2 | Pens | North | 120 | 2026-01-15 | 3 | -1000 | =C2*E2 |
| 3 | Paper | South | 80 | 2026-02-01 | 5 | 400 | =C3*E3 |
| 4 | Ink | North | 150 | 2026-02-20 | 2 | 400 | =C4*E4 |
| 5 | Stapler | East | 40 | 2026-03-05 | 9 | 400 | =C5*E5 |
| 6 | Folders | South | 220 | 2026-03-31 | 1 | =C6*E6 |
Where Sheets gives a different answer from Excel
- Dates are kept as text. DATE, EDATE, EOMONTH, WORKDAY, WORKDAY.INTL and TODAY give a date written
yyyy-mm-dd, where Excel gives a serial number formatted as a date. Arithmetic and comparisons still treat it as a date (=D6-D2is 75), but=ISNUMBER(TODAY())is FALSE, and a date with days added to it shows as its serial number, 46067, until the cell is given a date format or the formula is wrapped in TEXT. - An empty cell in a spilled result shows empty.
=A2:A6over a column with a gap spills the gap as an empty cell, where Excel shows 0, as Sheets does for=A3on its own. - IMAGE fetches nothing from another site. It shows a picture on this site, or one written into the formula as a
data:address; any other address is#BLOCKED!, and the cell’s tooltip says why. - The database functions read conditions, not formulas. A heading in the criteria that is not a column of the database, which Excel reads as a computed criterion, gives
#VALUE!. - Excel's binary searches search in order. XLOOKUP and XMATCH with a search mode of 2 or -2 find what 1 and -1 find, which on a sorted list is the same answer.
Everything else in the tables below gave the answer Excel gives for the same formula when this page was built.
Math & Trig (82)
Arithmetic, rounding, trigonometry, matrices, and the adding-up functions with conditions: SUMIF, SUMIFS and SUBTOTAL.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ABS | ABS(number) Returns the absolute value of a number, a number without its sign. | =ABS(-7.5) | 7.5 |
| ACOS | ACOS(number) Returns the arccosine of a number, in radians. | =ROUND(ACOS(0.5),6) | 1.047198 |
| ACOSH | ACOSH(number) Returns the inverse hyperbolic cosine of a number. | =ROUND(ACOSH(10),6) | 2.993223 |
| ACOT | ACOT(number) Returns the arccotangent of a number, in radians in the range 0 to Pi. | =ROUND(ACOT(2),6) | 0.463648 |
| ACOTH | ACOTH(number) Returns the inverse hyperbolic cotangent of a number. | =ROUND(ACOTH(6),6) | 0.168236 |
| AGGREGATE | AGGREGATE(function_num, options, ref1, [ref2], ...) Returns an aggregate in a list or database, with the option to leave out hidden rows and error values.Function 14 is LARGE and option 6 leaves out errors, so the #DIV/0! of every row that is not North drops out. | =AGGREGATE(14,6,C2:C6/(B2:B6="North"),1) | 150 |
| ARABIC | ARABIC(text) Converts a Roman numeral to Arabic. | =ARABIC("MMXXVI") | 2026 |
| ASIN | ASIN(number) Returns the arcsine of a number, in radians. | =ROUND(ASIN(1),6) | 1.570796 |
| ASINH | ASINH(number) Returns the inverse hyperbolic sine of a number. | =ROUND(ASINH(1),6) | 0.881374 |
| ATAN | ATAN(number) Returns the arctangent of a number, in radians. | =ROUND(ATAN(1),6) | 0.785398 |
| ATAN2 | ATAN2(x_num, y_num) Returns the arctangent of the specified x- and y-coordinates, in radians. | =ROUND(ATAN2(-1,0),6) | 3.141593 |
| ATANH | ATANH(number) Returns the inverse hyperbolic tangent of a number. | =ROUND(ATANH(0.5),6) | 0.549306 |
| BASE | BASE(number, radix, [min_length]) Converts a number into a text representation with the given radix (base). | =BASE(255,16,4) | 00FF |
| CEILING | CEILING(number, significance) Rounds a number up, to the nearest multiple of significance. | =CEILING(22,5) | 25 |
| CEILING.MATH | CEILING.MATH(number, [significance]) Rounds a number up, to the nearest integer or to the nearest multiple of significance. | =CEILING.MATH(-4.3) | -4 |
| CEILING.PRECISE | CEILING.PRECISE(number, [significance]) Rounds a number up, to the nearest integer or to the nearest multiple of significance. | =CEILING.PRECISE(-4.3,2) | -4 |
| COMBIN | COMBIN(number, number_chosen) Returns the number of combinations for a given number of items. | =COMBIN(8,2) | 28 |
| COMBINA | COMBINA(number, number_chosen) Returns the number of combinations with repetitions for a given number of items. | =COMBINA(4,3) | 20 |
| COS | COS(number) Returns the cosine of an angle. | =COS(0) | 1 |
| COSH | COSH(number) Returns the hyperbolic cosine of a number. | =ROUND(COSH(1),6) | 1.543081 |
| COT | COT(number) Returns the cotangent of an angle. | =ROUND(COT(PI()/4),6) | 1 |
| COTH | COTH(number) Returns the hyperbolic cotangent of a number. | =ROUND(COTH(1),6) | 1.313035 |
| CSC | CSC(number) Returns the cosecant of an angle. | =ROUND(CSC(PI()/6),6) | 2 |
| CSCH | CSCH(number) Returns the hyperbolic cosecant of an angle. | =ROUND(CSCH(1),6) | 0.850918 |
| DECIMAL | DECIMAL(number, radix) Converts a text representation of a number in a given base into a decimal number. | =DECIMAL("FF",16) | 255 |
| DEGREES | DEGREES(angle) Converts radians to degrees. | =DEGREES(PI()) | 180 |
| EVEN | EVEN(number) Rounds a positive number up and a negative number down to the nearest even integer. | =EVEN(3.2) | 4 |
| EXP | EXP(number) Returns e raised to the power of a given number. | =ROUND(EXP(1),6) | 2.718282 |
| FACT | FACT(number) Returns the factorial of a number, equal to 1*2*3*...*number. | =FACT(5) | 120 |
| FACTDOUBLE | FACTDOUBLE(number) Returns the double factorial of a number. | =FACTDOUBLE(7) | 105 |
| FLOOR | FLOOR(number, significance) Rounds a number down to the nearest multiple of significance. | =FLOOR(22,5) | 20 |
| FLOOR.MATH | FLOOR.MATH(number, [significance]) Rounds a number down, to the nearest integer or to the nearest multiple of significance. | =FLOOR.MATH(-4.3) | -5 |
| FLOOR.PRECISE | FLOOR.PRECISE(number, [significance]) Rounds a number down, to the nearest integer or to the nearest multiple of significance. | =FLOOR.PRECISE(-4.3) | -5 |
| GCD | GCD(number1, [number2], ...) Returns the greatest common divisor. | =GCD(24,36) | 12 |
| INT | INT(number) Rounds a number down to the nearest integer. | =INT(-3.7) | -4 |
| ISO.CEILING | ISO.CEILING(number, [significance]) Rounds a number up, to the nearest integer or to the nearest multiple of significance. | =ISO.CEILING(4.3,2) | 6 |
| LCM | LCM(number1, [number2], ...) Returns the least common multiple. | =LCM(4,6) | 12 |
| LN | LN(number) Returns the natural logarithm of a number. | =ROUND(LN(10),6) | 2.302585 |
| LOG | LOG(number, [base]) Returns the logarithm of a number to the base you specify. | =LOG(8,2) | 3 |
| LOG10 | LOG10(number) Returns the base-10 logarithm of a number. | =LOG10(1000) | 3 |
| MDETERM | MDETERM(array) Returns the matrix determinant of an array. | =MDETERM({3,6,1;1,1,0;3,10,2}) | 1 |
| MINVERSE | MINVERSE(array) Returns the inverse matrix for the matrix stored in an array.On its own it spills the inverse, the same size as the matrix: here two rows, 0, 0.5 and -1, 2. | =TEXTJOIN(",",TRUE,MINVERSE({4,-1;2,0})) | 0,0.5,-1,2 |
| MMULT | MMULT(array1, array2) Returns the matrix product of two arrays, an array with the same number of rows as array1 and columns as array2.On its own it spills the product: a column of two, 17 and 39. | =TEXTJOIN(",",TRUE,MMULT({1,2;3,4},{5;6})) | 17,39 |
| MOD | MOD(number, divisor) Returns the remainder after a number is divided by a divisor. | =MOD(-7,3) | 2 |
| MROUND | MROUND(number, multiple) Returns a number rounded to the desired multiple. | =MROUND(17,5) | 15 |
| MULTINOMIAL | MULTINOMIAL(number1, [number2], ...) Returns the multinomial of a set of numbers. | =MULTINOMIAL(2,3,4) | 1260 |
| MUNIT | MUNIT(dimension) Returns the unit matrix for the specified dimension.On its own it spills a 3 by 3 grid, 1 down the diagonal and 0 elsewhere. | =SUM(MUNIT(3)) | 3 |
| ODD | ODD(number) Rounds a positive number up and a negative number down to the nearest odd integer. | =ODD(2.1) | 3 |
| PERCENTOF | PERCENTOF(data_subset, data_all) Returns the percentage that a subset makes up of a given data set. | =ROUND(PERCENTOF(C2:C3,C2:C6),4) | 0.3279 |
| PI | PI() Returns the value of Pi, 3.14159265358979, accurate to 15 digits. | =ROUND(PI(),5) | 3.14159 |
| POWER | POWER(number, power) Returns the result of a number raised to a power. | =POWER(2,10) | 1024 |
| PRODUCT | PRODUCT(number1, [number2], ...) Multiplies all the numbers given as arguments. | =PRODUCT(2,3,4) | 24 |
| QUOTIENT | QUOTIENT(numerator, denominator) Returns the integer portion of a division. | =QUOTIENT(-7,2) | -3 |
| RADIANS | RADIANS(angle) Converts degrees to radians. | =ROUND(RADIANS(180),5) | 3.14159 |
| RAND | RAND() Returns a random number greater than or equal to 0 and less than 1, evenly distributed. It changes on recalculation. | =AND(RAND()>=0,RAND()<1) | TRUE |
| RANDARRAY | RANDARRAY([rows], [columns], [min], [max], [whole_number]) Returns an array of random numbers.On its own it spills two rows of three whole numbers from 1 to 6, new ones each time the sheet recalculates. | =COUNT(RANDARRAY(2,3,1,6,TRUE)) | 6 |
| RANDBETWEEN | RANDBETWEEN(bottom, top) Returns a random number between the numbers you specify. | =RANDBETWEEN(3,3) | 3 |
| ROMAN | ROMAN(number, [form]) Converts an Arabic numeral to Roman, as text. | =ROMAN(2026) | MMXXVI |
| ROUND | ROUND(number, num_digits) Rounds a number to a specified number of digits. | =ROUND(2.345,2) | 2.35 |
| ROUNDDOWN | ROUNDDOWN(number, num_digits) Rounds a number down, toward zero. | =ROUNDDOWN(-3.789,1) | -3.7 |
| ROUNDUP | ROUNDUP(number, num_digits) Rounds a number up, away from zero. | =ROUNDUP(3.211,2) | 3.22 |
| SEC | SEC(number) Returns the secant of an angle. | =ROUND(SEC(PI()/3),6) | 2 |
| SECH | SECH(number) Returns the hyperbolic secant of an angle. | =ROUND(SECH(1),6) | 0.648054 |
| SEQUENCE | SEQUENCE(rows, [columns], [start], [step]) Returns an array of numbers counting on, such as 1, 2, 3, 4, spilling across and down.On its own, =SEQUENCE(4,5) spills four rows of five numbers, 1 to 20. Anything already in the cells it would fill stops it, and it shows #SPILL!. | =TEXTJOIN(",",TRUE,SEQUENCE(1,5,10,-2)) | 10,8,6,4,2 |
| SERIESSUM | SERIESSUM(x, n, m, coefficients) Returns the sum of a power series. | =SERIESSUM(2,1,1,{1,2,3}) | 34 |
| SIGN | SIGN(number) Returns the sign of a number: 1 if positive, zero if zero, -1 if negative. | =SIGN(-12) | -1 |
| SIN | SIN(number) Returns the sine of an angle. | =ROUND(SIN(PI()/6),6) | 0.5 |
| SINH | SINH(number) Returns the hyperbolic sine of a number. | =ROUND(SINH(1),6) | 1.175201 |
| SQRT | SQRT(number) Returns the square root of a number. | =SQRT(144) | 12 |
| SQRTPI | SQRTPI(number) Returns the square root of (number * Pi). | =ROUND(SQRTPI(2),6) | 2.506628 |
| SUBTOTAL | SUBTOTAL(function_num, ref1, [ref2], ...) Returns a subtotal in a list or database, leaving out the rows a filter hides. | =SUBTOTAL(9,C2:C6) | 610 |
| SUM | SUM(number1, [number2], ...) Adds all the numbers in a range of cells. | =SUM(C2:C6) | 610 |
| SUMIF | SUMIF(range, criteria, [sum_range]) Adds the cells specified by a given condition or criteria. | =SUMIF(B2:B6,"North",C2:C6) | 270 |
| SUMIFS | SUMIFS(sum_range, criteria_range1, criteria1, ...) Adds the cells specified by a given set of conditions or criteria. | =SUMIFS(C2:C6,B2:B6,"South",C2:C6,">100") | 220 |
| SUMPRODUCT | SUMPRODUCT(array1, [array2], ...) Returns the sum of the products of corresponding ranges or arrays.Arithmetic on whole ranges works place by place, so the Excel idiom of multiplying a condition by a range gives the total of the North rows, as SUMIFS(C2:C6,B2:B6,"North") does. | =SUMPRODUCT((B2:B6="North")*C2:C6) | 270 |
| SUMSQ | SUMSQ(number1, [number2], ...) Returns the sum of the squares of the arguments. | =SUMSQ(3,4) | 25 |
| SUMX2MY2 | SUMX2MY2(array_x, array_y) Sums the differences between the squares of two corresponding ranges or arrays. | =SUMX2MY2(E2:E6,{1,2,3,4,5}) | 65 |
| SUMX2PY2 | SUMX2PY2(array_x, array_y) Returns the sum total of the sums of squares of numbers in two corresponding ranges or arrays. | =SUMX2PY2(E2:E6,{1,2,3,4,5}) | 175 |
| SUMXMY2 | SUMXMY2(array_x, array_y) Sums the squares of the differences in two corresponding ranges or arrays. | =SUMXMY2(E2:E6,{1,2,3,4,5}) | 55 |
| TAN | TAN(number) Returns the tangent of an angle. | =ROUND(TAN(PI()/4),6) | 1 |
| TANH | TANH(number) Returns the hyperbolic tangent of a number. | =ROUND(TANH(0.5),6) | 0.462117 |
| TRUNC | TRUNC(number, [num_digits]) Truncates a number to an integer by removing the decimal, or fractional, part of the number. | =TRUNC(-3.789,1) | -3.7 |
Statistical (118)
Counting, averages, spread and rank, with and without conditions; the probability distributions and their inverses; and regression, correlation and the tests.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| AVEDEV | AVEDEV(number1, [number2], ...) Returns the average of the absolute deviations of data points from their mean. | =AVEDEV(C2:C6) | 50.4 |
| AVERAGE | AVERAGE(number1, [number2], ...) Returns the average (arithmetic mean) of its arguments. | =AVERAGE(C2:C6) | 122 |
| AVERAGEA | AVERAGEA(value1, [value2], ...) Returns the average of its arguments, evaluating text as 0 and TRUE as 1. | =AVERAGEA(B2,C2) | 60 |
| AVERAGEIF | AVERAGEIF(range, criteria, [average_range]) Finds the average of the cells specified by a given condition or criteria. | =AVERAGEIF(B2:B6,"South",C2:C6) | 150 |
| AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criteria1, ...) Finds the average of the cells specified by a given set of conditions or criteria. | =AVERAGEIFS(C2:C6,B2:B6,"North",C2:C6,">100") | 135 |
| BETA.DIST | BETA.DIST(x, alpha, beta, cumulative, [A], [B]) Returns the beta probability distribution function. | =ROUND(BETA.DIST(2,8,10,TRUE,1,3),6) | 0.685471 |
| BETA.INV | BETA.INV(probability, alpha, beta, [A], [B]) Returns the inverse of the cumulative beta probability density function. | =ROUND(BETA.INV(0.685470581,8,10,1,3),4) | 2 |
| BINOM.DIST | BINOM.DIST(number_s, trials, probability_s, cumulative) Returns the individual term binomial distribution probability. | =ROUND(BINOM.DIST(6,10,0.5,FALSE),6) | 0.205078 |
| BINOM.DIST.RANGE | BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) Returns the probability of a trial result using a binomial distribution. | =ROUND(BINOM.DIST.RANGE(60,0.75,45,50),6) | 0.52363 |
| BINOM.INV | BINOM.INV(trials, probability_s, alpha) Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. | =BINOM.INV(6,0.5,0.75) | 4 |
| CHISQ.DIST | CHISQ.DIST(x, deg_freedom, cumulative) Returns the left-tailed probability of the chi-squared distribution. | =ROUND(CHISQ.DIST(0.5,1,TRUE),6) | 0.5205 |
| CHISQ.DIST.RT | CHISQ.DIST.RT(x, deg_freedom) Returns the right-tailed probability of the chi-squared distribution. | =ROUND(CHISQ.DIST.RT(18.307,10),6) | 0.050001 |
| CHISQ.INV | CHISQ.INV(probability, deg_freedom) Returns the inverse of the left-tailed probability of the chi-squared distribution. | =ROUND(CHISQ.INV(0.93,1),6) | 3.28302 |
| CHISQ.INV.RT | CHISQ.INV.RT(probability, deg_freedom) Returns the inverse of the right-tailed probability of the chi-squared distribution. | =ROUND(CHISQ.INV.RT(0.05,10),4) | 18.307 |
| CHISQ.TEST | CHISQ.TEST(actual_range, expected_range) Returns the test for independence: the value from the chi-squared distribution for the statistic and the degrees of freedom. | =ROUND(CHISQ.TEST({58,35;11,25;10,23},{45.35,47.65;17.56,18.44;16.09,16.91}),6) | 0.000308 |
| CONFIDENCE.NORM | CONFIDENCE.NORM(alpha, standard_dev, size) Returns the confidence interval for a population mean, using a normal distribution. | =ROUND(CONFIDENCE.NORM(0.05,2.5,50),6) | 0.692952 |
| CONFIDENCE.T | CONFIDENCE.T(alpha, standard_dev, size) Returns the confidence interval for a population mean, using a Student’s t distribution. | =ROUND(CONFIDENCE.T(0.05,1,50),6) | 0.284197 |
| CORREL | CORREL(array1, array2) Returns the correlation coefficient between two data sets. | =ROUND(CORREL(C2:C6,E2:E6),6) | -0.920575 |
| COUNT | COUNT(value1, [value2], ...) Counts the number of cells in a range that contain numbers. | =COUNT(A1:C6) | 5 |
| COUNTA | COUNTA(value1, [value2], ...) Counts the number of cells in a range that are not empty. | =COUNTA(A1:A6) | 6 |
| COUNTBLANK | COUNTBLANK(range) Counts the number of empty cells in a specified range of cells. | =COUNTBLANK(H1:H6) | 6 |
| COUNTIF | COUNTIF(range, criteria) Counts the number of cells within a range that meet the given condition. | =COUNTIF(C2:C6,">100") | 3 |
| COUNTIFS | COUNTIFS(criteria_range1, criteria1, ...) Counts the number of cells specified by a given set of conditions or criteria. | =COUNTIFS(B2:B6,"South",C2:C6,">100") | 1 |
| COUNTUNIQUE | COUNTUNIQUE(value1, [value2], ...) Counts the number of different values in a list of values and ranges, as in Google Sheets.From Google Sheets. Text minds case, so North and NORTH would be two. | =COUNTUNIQUE(B2:B6) | 3 |
| COVARIANCE.P | COVARIANCE.P(array1, array2) Returns population covariance, the average of the products of deviations for each data point pair in two data sets. | =COVARIANCE.P(C2:C6,E2:E6) | -160 |
| COVARIANCE.S | COVARIANCE.S(array1, array2) Returns sample covariance, the average of the products of deviations for each data point pair in two data sets. | =COVARIANCE.S(C2:C6,E2:E6) | -200 |
| DEVSQ | DEVSQ(number1, [number2], ...) Returns the sum of squares of deviations of data points from their sample mean. | =DEVSQ(C2:C6) | 18880 |
| EXPON.DIST | EXPON.DIST(x, lambda, cumulative) Returns the exponential distribution. | =ROUND(EXPON.DIST(0.2,10,TRUE),6) | 0.864665 |
| F.DIST | F.DIST(x, deg_freedom1, deg_freedom2, cumulative) Returns the (left-tailed) F probability distribution (degree of diversity) for two data sets. | =ROUND(F.DIST(15.2069,6,4,TRUE),6) | 0.99 |
| F.DIST.RT | F.DIST.RT(x, deg_freedom1, deg_freedom2) Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. | =ROUND(F.DIST.RT(15.2069,6,4),6) | 0.01 |
| F.INV | F.INV(probability, deg_freedom1, deg_freedom2) Returns the inverse of the (left-tailed) F probability distribution. | =ROUND(F.INV(0.01,6,4),6) | 0.10931 |
| F.INV.RT | F.INV.RT(probability, deg_freedom1, deg_freedom2) Returns the inverse of the (right-tailed) F probability distribution. | =ROUND(F.INV.RT(0.01,6,4),4) | 15.2069 |
| F.TEST | F.TEST(array1, array2) Returns the result of an F-test, the two-tailed probability that the variances in Array1 and Array2 are not significantly different. | =ROUND(F.TEST({6,7,9,15,21},{20,28,31,38,40}),6) | 0.648318 |
| FISHER | FISHER(x) Returns the Fisher transformation. | =ROUND(FISHER(0.75),6) | 0.972955 |
| FISHERINV | FISHERINV(y) Returns the inverse of the Fisher transformation. | =ROUND(FISHERINV(0.972955),4) | 0.75 |
| FORECAST.ETS | FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) Returns the forecasted value for a specific future target date using the exponential smoothing method.Excel’s search for the smoothing weights is not published; Sheets fits the same model (additive Holt-Winters) by least squares, so on real data the figures can differ from Excel’s in the later digits. A series that follows its trend exactly, as here, gives the same answer. | =ROUND(FORECAST.ETS(6,{10,12,14,16,18},{1,2,3,4,5},0),4) | 20 |
| FORECAST.ETS.CONFINT | FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation]) Returns a confidence interval for the forecast value at the specified target date.With no error in the history there is no spread to the forecast, so the interval is nothing either side. | =ROUND(FORECAST.ETS.CONFINT(6,{10,12,14,16,18},{1,2,3,4,5},0.95,0),4) | 0 |
| FORECAST.ETS.SEASONALITY | FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation]) Returns the length of the repetitive pattern Excel detects for the specified time series.With no repeat to find it gives 0, which as the seasonality argument means none. | =FORECAST.ETS.SEASONALITY({1,5,1,5,1,5,1,5},{1,2,3,4,5,6,7,8}) | 2 |
| FORECAST.ETS.STAT | FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation]) Returns a statistical value as a result of time series forecasting.Statistic 8 is the step of the timeline; 1 to 7 are alpha, beta, gamma, MASE, SMAPE, MAE and RMSE. | =FORECAST.ETS.STAT({10,12,14,16,18},{1,2,3,4,5},8) | 1 |
| FORECAST.LINEAR | FORECAST.LINEAR(x, known_ys, known_xs) Calculates, or predicts, a future value along a linear trend by using existing values. | =ROUND(FORECAST.LINEAR(10,C2:C6,E2:E6),4) | 2 |
| FREQUENCY | FREQUENCY(data_array, bins_array) Calculates how often values occur within a range of values and then returns a vertical array of numbers.On its own it spills a column one longer than the bins: the count up to each bin, then the count above the last. | =TEXTJOIN(",",TRUE,FREQUENCY(C2:C6,{100,200})) | 2,2,1 |
| GAMMA | GAMMA(x) Returns the Gamma function value. | =GAMMA(5) | 24 |
| GAMMA.DIST | GAMMA.DIST(x, alpha, beta, cumulative) Returns the gamma distribution. | =ROUND(GAMMA.DIST(10,9,2,TRUE),6) | 0.068094 |
| GAMMA.INV | GAMMA.INV(probability, alpha, beta) Returns the inverse of the gamma cumulative distribution. | =ROUND(GAMMA.INV(0.068094,9,2),3) | 10 |
| GAMMALN | GAMMALN(x) Returns the natural logarithm of the gamma function. | =ROUND(GAMMALN(4.5),6) | 2.453737 |
| GAMMALN.PRECISE | GAMMALN.PRECISE(x) Returns the natural logarithm of the gamma function. | =ROUND(GAMMALN.PRECISE(4.5),6) | 2.453737 |
| GAUSS | GAUSS(x) Returns 0.5 less than the standard normal cumulative distribution. | =ROUND(GAUSS(2),6) | 0.47725 |
| GEOMEAN | GEOMEAN(number1, [number2], ...) Returns the geometric mean of an array or range of positive numeric data. | =ROUND(GEOMEAN(E2:E6),6) | 3.063887 |
| GROWTH | GROWTH(known_ys, [known_xs], [new_xs], [const]) Returns numbers in an exponential growth trend matching known data points. | =GROWTH({2,4,8},{1,2,3},4) | 16 |
| HARMEAN | HARMEAN(number1, [number2], ...) Returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals. | =ROUND(HARMEAN(E2:E6),6) | 2.331606 |
| HYPGEOM.DIST | HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) Returns the hypergeometric distribution. | =ROUND(HYPGEOM.DIST(1,4,8,20,TRUE),6) | 0.465428 |
| INTERCEPT | INTERCEPT(known_ys, known_xs) Calculates the point at which a line will intersect the y-axis by using a best-fit regression line plotted through the known x-values and y-values. | =ROUND(INTERCEPT(C2:C6,E2:E6),4) | 202 |
| KURT | KURT(number1, [number2], ...) Returns the kurtosis of a data set. | =ROUND(KURT(C2:C6),6) | -0.078911 |
| LARGE | LARGE(array, k) Returns the k-th largest value in a data set. | =LARGE(C2:C6,2) | 150 |
| LINEST | LINEST(known_ys, [known_xs], [const], [stats]) Returns statistics that describe a linear trend matching known data points, by fitting a straight line using the least squares method.On its own it spills the slope and then the intercept along a row; with stats TRUE, four more rows of regression statistics below them. | =TEXTJOIN(",",TRUE,LINEST({3,5,7,9},{1,2,3,4})) | 2,1 |
| LOGNORM.DIST | LOGNORM.DIST(x, mean, standard_dev, cumulative) Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev. | =ROUND(LOGNORM.DIST(4,3.5,1.2,TRUE),6) | 0.039084 |
| LOGNORM.INV | LOGNORM.INV(probability, mean, standard_dev) Returns the inverse of the lognormal cumulative distribution function of x. | =ROUND(LOGNORM.INV(0.039084,3.5,1.2),4) | 4 |
| MAX | MAX(number1, [number2], ...) Returns the largest value in a set of values. | =MAX(C2:C6) | 220 |
| MAXA | MAXA(value1, [value2], ...) Returns the largest value in a set of values. Does not ignore logical values and text. | =MAXA(F2,B2) | 0 |
| MAXIFS | MAXIFS(max_range, criteria_range1, criteria1, ...) Returns the maximum value among cells specified by a given set of conditions or criteria. | =MAXIFS(C2:C6,B2:B6,"North") | 150 |
| MEDIAN | MEDIAN(number1, [number2], ...) Returns the median, or the number in the middle of the set of given numbers. | =MEDIAN(C2:C6) | 120 |
| MIN | MIN(number1, [number2], ...) Returns the smallest number in a set of values. | =MIN(C2:C6) | 40 |
| MINA | MINA(value1, [value2], ...) Returns the smallest value in a set of values. Does not ignore logical values and text. | =MINA(C2:C6,B2) | 0 |
| MINIFS | MINIFS(min_range, criteria_range1, criteria1, ...) Returns the minimum value among cells specified by a given set of conditions or criteria. | =MINIFS(C2:C6,B2:B6,"South") | 80 |
| MODE | MODE(number1, [number2], ...) Returns the most frequently occurring value in a range of data. | =MODE(3,5,5,7) | 5 |
| MODE.MULT | MODE.MULT(number1, [number2], ...) Returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data.On its own it spills every value that occurs most often, down a column. | =TEXTJOIN(",",TRUE,MODE.MULT({1,2,2,3,3,4})) | 2,3 |
| MODE.SNGL | MODE.SNGL(number1, [number2], ...) Returns the most frequently occurring, or repetitive, value in an array or range of data. | =MODE.SNGL({1,2,2,3,3,4}) | 2 |
| NEGBINOM.DIST | NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success. | =ROUND(NEGBINOM.DIST(10,5,0.25,TRUE),6) | 0.313514 |
| NORM.DIST | NORM.DIST(x, mean, standard_dev, cumulative) Returns the normal distribution for the specified mean and standard deviation. | =ROUND(NORM.DIST(42,40,1.5,TRUE),6) | 0.908789 |
| NORM.INV | NORM.INV(probability, mean, standard_dev) Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. | =ROUND(NORM.INV(0.908789,40,1.5),4) | 42 |
| NORM.S.DIST | NORM.S.DIST(z, cumulative) Returns the standard normal distribution (has a mean of zero and a standard deviation of one). | =ROUND(NORM.S.DIST(1.333333,TRUE),6) | 0.908789 |
| NORM.S.INV | NORM.S.INV(probability) Returns the inverse of the standard normal cumulative distribution (has a mean of zero and a standard deviation of one). | =ROUND(NORM.S.INV(0.908789),4) | 1.3333 |
| PEARSON | PEARSON(array1, array2) Returns the Pearson product moment correlation coefficient, r. | =ROUND(PEARSON({9,7,5,3,1},{10,6,1,5,3}),6) | 0.699379 |
| PERCENTILE | PERCENTILE(array, k) Returns the k-th percentile of values in a range. | =PERCENTILE(C2:C6,0.25) | 80 |
| PERCENTILE.EXC | PERCENTILE.EXC(array, k) Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. | =PERCENTILE.EXC(C2:C6,0.25) | 60 |
| PERCENTILE.INC | PERCENTILE.INC(array, k) Returns the k-th percentile of values in a range, where k is in the range 0 to 1, inclusive. | =PERCENTILE.INC(C2:C6,0.9) | 192 |
| PERCENTRANK.EXC | PERCENTRANK.EXC(array, x, [significance]) Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. | =PERCENTRANK.EXC(C2:C6,100) | 0.416 |
| PERCENTRANK.INC | PERCENTRANK.INC(array, x, [significance]) Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set. | =PERCENTRANK.INC(C2:C6,100) | 0.375 |
| PERMUT | PERMUT(number, number_chosen) Returns the number of permutations for a given number of objects that can be selected from the total objects. | =PERMUT(5,2) | 20 |
| PERMUTATIONA | PERMUTATIONA(number, number_chosen) Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. | =PERMUTATIONA(3,2) | 9 |
| PHI | PHI(x) Returns the value of the density function for a standard normal distribution. | =ROUND(PHI(0.75),6) | 0.301137 |
| POISSON.DIST | POISSON.DIST(x, mean, cumulative) Returns the Poisson distribution. | =ROUND(POISSON.DIST(2,5,TRUE),6) | 0.124652 |
| PROB | PROB(x_range, prob_range, lower_limit, [upper_limit]) Returns the probability that values in a range are between two limits or equal to a lower limit. | =PROB({0,1,2,3},{0.2,0.3,0.1,0.4},1,2) | 0.4 |
| QUARTILE | QUARTILE(array, quart) Returns the quartile of a data set. | =QUARTILE(C2:C6,3) | 150 |
| QUARTILE.EXC | QUARTILE.EXC(array, quart) Returns the quartile of a data set, based on percentile values from 0..1, exclusive. | =QUARTILE.EXC(C2:C6,1) | 60 |
| QUARTILE.INC | QUARTILE.INC(array, quart) Returns the quartile of a data set, based on percentile values from 0 to 1, inclusive. | =QUARTILE.INC(C2:C6,1) | 80 |
| RANK | RANK(number, ref, [order]) Returns the rank of a number in a list of numbers. | =RANK(150,C2:C6) | 2 |
| RANK.AVG | RANK.AVG(number, ref, [order]) Returns the rank of a number in a list of numbers; if more than one value has the same rank, the average rank is returned. | =RANK.AVG(5,{7,5,5,2}) | 2.5 |
| RANK.EQ | RANK.EQ(number, ref, [order]) Returns the rank of a number in a list of numbers; if more than one value has the same rank, the top rank of that set is returned. | =RANK.EQ(150,C2:C6,1) | 4 |
| RSQ | RSQ(known_ys, known_xs) Returns the square of the Pearson product moment correlation coefficient through the given data points. | =ROUND(RSQ(C2:C6,E2:E6),6) | 0.847458 |
| SKEW | SKEW(number1, [number2], ...) Returns the skewness of a distribution: a characterisation of the degree of asymmetry of a distribution around its mean. | =ROUND(SKEW(C2:C6),6) | 0.433891 |
| SKEW.P | SKEW.P(number1, [number2], ...) Returns the skewness of a distribution based on a population. | =ROUND(SKEW.P(C2:C6),6) | 0.291063 |
| SLOPE | SLOPE(known_ys, known_xs) Returns the slope of the linear regression line through the given data points. | =ROUND(SLOPE(C2:C6,E2:E6),4) | -20 |
| SMALL | SMALL(array, k) Returns the k-th smallest value in a data set. | =SMALL(C2:C6,2) | 80 |
| STANDARDIZE | STANDARDIZE(x, mean, standard_dev) Returns a normalised value from a distribution characterised by a mean and standard deviation. | =ROUND(STANDARDIZE(42,40,1.5),6) | 1.333333 |
| STDEV | STDEV(number1, [number2], ...) Estimates standard deviation based on a sample. | =ROUND(STDEV(C2:C6),4) | 68.7023 |
| STDEV.P | STDEV.P(number1, [number2], ...) Calculates standard deviation based on the entire population given as arguments. | =ROUND(STDEV.P(C2:C6),4) | 61.4492 |
| STDEV.S | STDEV.S(number1, [number2], ...) Estimates standard deviation based on a sample. | =ROUND(STDEV.S(C2:C6),4) | 68.7023 |
| STDEVA | STDEVA(value1, [value2], ...) Estimates standard deviation based on a sample, including logical values and text. | =ROUND(STDEVA(E2:E6,TRUE),6) | 3.082207 |
| STDEVP | STDEVP(number1, [number2], ...) Calculates standard deviation based on the entire population given as arguments. | =ROUND(STDEVP(C2:C6),4) | 61.4492 |
| STDEVPA | STDEVPA(value1, [value2], ...) Calculates standard deviation based on an entire population, including logical values and text. | =ROUND(STDEVPA(E2:E6,TRUE),6) | 2.813657 |
| STEYX | STEYX(known_ys, known_xs) Returns the standard error of the predicted y-value for each x in a regression. | =ROUND(STEYX(C2:C6,E2:E6),4) | 30.9839 |
| T.DIST | T.DIST(x, deg_freedom, cumulative) Returns the left-tailed Student’s t-distribution. | =ROUND(T.DIST(1.5,10,TRUE),6) | 0.917746 |
| T.DIST.2T | T.DIST.2T(x, deg_freedom) Returns the two-tailed Student’s t-distribution. | =ROUND(T.DIST.2T(1.96,60),6) | 0.054645 |
| T.DIST.RT | T.DIST.RT(x, deg_freedom) Returns the right-tailed Student’s t-distribution. | =ROUND(T.DIST.RT(1.96,60),6) | 0.027322 |
| T.INV | T.INV(probability, deg_freedom) Returns the left-tailed inverse of the Student’s t-distribution. | =ROUND(T.INV(0.75,2),6) | 0.816497 |
| T.INV.2T | T.INV.2T(probability, deg_freedom) Returns the two-tailed inverse of the Student’s t-distribution. | =ROUND(T.INV.2T(0.05,60),6) | 2.000298 |
| T.TEST | T.TEST(array1, array2, tails, type) Returns the probability associated with a Student’s t-Test. | =ROUND(T.TEST({3,4,5,8,9,1,2,4,5},{6,19,3,2,14,4,5,17,1},2,1),6) | 0.196016 |
| TREND | TREND(known_ys, [known_xs], [new_xs], [const]) Returns numbers in a linear trend matching known data points, using the least squares method.With several new x values it spills one predicted y for each. | =TREND({3,5,7,9},{1,2,3,4},5) | 11 |
| TRIMMEAN | TRIMMEAN(array, percent) Returns the mean of the interior portion of a set of data values. | =ROUND(TRIMMEAN(C2:C6,0.4),4) | 116.6667 |
| VAR | VAR(number1, [number2], ...) Estimates variance based on a sample. | =VAR(C2:C6) | 4720 |
| VAR.P | VAR.P(number1, [number2], ...) Calculates variance based on the entire population. | =VAR.P(C2:C6) | 3776 |
| VAR.S | VAR.S(number1, [number2], ...) Estimates variance based on a sample. | =VAR.S(C2:C6) | 4720 |
| VARA | VARA(value1, [value2], ...) Estimates variance based on a sample, including logical values and text. | =ROUND(VARA(E2:E6,TRUE),6) | 9.5 |
| VARP | VARP(number1, [number2], ...) Calculates variance based on the entire population. | =VARP(C2:C6) | 3776 |
| VARPA | VARPA(value1, [value2], ...) Calculates variance based on the entire population, including logical values and text. | =ROUND(VARPA(E2:E6,TRUE),6) | 7.916667 |
| WEIBULL.DIST | WEIBULL.DIST(x, alpha, beta, cumulative) Returns the Weibull distribution. | =ROUND(WEIBULL.DIST(105,20,100,TRUE),6) | 0.929581 |
| Z.TEST | Z.TEST(array, x, [sigma]) Returns the one-tailed P-value of a z-test. | =ROUND(Z.TEST({3,6,7,8,6,5,4,2,1,9},4),6) | 0.090574 |
Lookup & Reference (39)
Finding a value in a table, working with where a cell is rather than what it holds, and reshaping an array: VSTACK, TAKE, WRAPROWS and the rest.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ADDRESS | ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Creates a cell reference as text, given row and column numbers. | =ADDRESS(2,3) | $C$2 |
| ARRAYFORMULA | ARRAYFORMULA(array_formula) Works a formula out over whole ranges and spills the results, as Google Sheets does.A function of Google Sheets. Saved to .xlsx it becomes an Excel array formula, and an Excel array formula opens as ARRAYFORMULA. | =ARRAYFORMULA(SUM(C2:C6*E2:E6)) | 1640 |
| CHOOSE | CHOOSE(index_num, value1, [value2], ...) Chooses a value from a list of values, based on an index number. | =CHOOSE(2,"red","amber","green") | amber |
| CHOOSECOLS | CHOOSECOLS(array, col_num1, [col_num2], ...) Returns the specified columns from an array.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,CHOOSECOLS(A2:C3,1,3)) | Pens,120,Paper,80 |
| CHOOSEROWS | CHOOSEROWS(array, row_num1, [row_num2], ...) Returns the specified rows from an array.A negative number counts from the end. On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,CHOOSEROWS(A2:A6,1,-1)) | Pens,Folders |
| COLUMN | COLUMN([reference]) Returns the column number of a reference. | =COLUMN(D1:D6) | 4 |
| COLUMNS | COLUMNS(array) Returns the number of columns in an array or reference. | =COLUMNS(A1:D6) | 4 |
| DROP | DROP(array, rows, [columns]) Drops rows or columns from the start or end of an array.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,DROP(A2:A6,2)) | Ink,Stapler,Folders |
| EXPAND | EXPAND(array, rows, [columns], [pad_with]) Expands an array to the specified dimensions.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",FALSE,EXPAND({1,2},2,2,0)) | 1,2,0,0 |
| FILTER | FILTER(array, include, [if_empty]) Filters a range or array to the rows or columns you choose with a condition, spilling the result.On its own, =FILTER(A2:C6,B2:B6="North") spills the two North rows, three columns wide, into the cells below and to the right. With nothing kept it is #CALC!, or its third argument. | =TEXTJOIN(", ",TRUE,FILTER(A2:A6,B2:B6="North")) | Pens, Ink |
| FORMULATEXT | FORMULATEXT(reference) Returns a formula as a string. | =FORMULATEXT(G2) | =C2*E2 |
| GROUPBY | GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]) Groups rows by the values in one or more fields and totals each group with a function, spilling the summary.On its own, =GROUPBY(B2:B6,C2:C6,SUM) spills East, North, South and Total with their sums; INDEX picks North’s. | =INDEX(GROUPBY(B2:B6,C2:C6,SUM),2,2) | 270 |
| HLOOKUP | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Looks for a value in the top row of a table and returns the value in the same column from a row you specify. | =HLOOKUP("Amount",A1:D6,3,FALSE) | 80 |
| HSTACK | HSTACK(array1, [array2], ...) Appends arrays horizontally and in sequence to return a larger array.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,HSTACK(A2:A3,C2:C3)) | Pens,120,Paper,80 |
| HYPERLINK | HYPERLINK(link_location, [friendly_name]) Creates a shortcut or jump that opens a document or a web address; shows the friendly name. | =HYPERLINK("https://obscuraos.com/sheets","Sheets") | Sheets |
| IMAGE | IMAGE(source, [alt_text], [sizing], [height], [width]) Shows a picture in the cell, from this site or from a data: address written into the formula.Sheets fetches nothing from another site. A picture on this site, or one written into the formula as a data: address, is drawn in the cell. | =IMAGE("https://example.com/logo.png") | #BLOCKED! |
| INDEX | INDEX(array, row_num, [column_num]) Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. | =INDEX(A2:C6,3,3) | 150 |
| INDIRECT | INDIRECT(ref_text, [a1]) Returns the reference specified by a text string. | =INDIRECT("C"&4) | 150 |
| LOOKUP | LOOKUP(lookup_value, lookup_vector, [result_vector]) Looks up a value either from a one-row or one-column range or from an array. | =LOOKUP(4,{1,3,5},{"low","mid","high"}) | mid |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value in a specified order. | =MATCH("Ink",A2:A6,0) | 3 |
| OFFSET | OFFSET(reference, rows, cols, [height], [width]) Returns a reference to a range that is a given number of rows and columns from a given reference. | =SUM(OFFSET(C1,1,0,3,1)) | 350 |
| PIVOTBY | PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to]) Groups rows by one set of fields and columns by another, and totals each crossing with a function, spilling the table.On its own the formula spills a table: regions down the side, FALSE and TRUE (more than two units) across the top, and totals; INDEX picks North’s TRUE. | =INDEX(PIVOTBY(B2:B6,E2:E6>2,C2:C6,SUM),3,3) | 120 |
| ROW | ROW([reference]) Returns the row number of a reference. | =ROW(C4) | 4 |
| ROWS | ROWS(array) Returns the number of rows in a reference or an array. | =ROWS(A2:C6) | 5 |
| SORT | SORT(array, [sort_index], [sort_order], [by_col]) Sorts the contents of a range or array, spilling the result.On its own it spills the sorted rows. Numbers sort before text and text before TRUE and FALSE, as in Excel. | =TEXTJOIN(", ",TRUE,SORT(A2:A6)) | Folders, Ink, Paper, Pens, Stapler |
| SORTBY | SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...) Sorts the contents of a range or array by the values in other ranges or arrays. | =TEXTJOIN(", ",TRUE,SORTBY(A2:A6,C2:C6,-1)) | Folders, Ink, Pens, Paper, Stapler |
| SPARKLINE | SPARKLINE(data, [options]) Draws a small line, column, win/loss or bar chart of the data in the cell, as Google Sheets does.A function of Google Sheets. It draws a line of C2:C6 in its cell, which has no value of its own; its options, a block of names and values beside the data, give a column, win/loss or bar chart and the colours. | =SPARKLINE(C2:C6) | |
| TAKE | TAKE(array, rows, [columns]) Returns rows or columns from the start or end of an array.A negative number takes from the end. On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,TAKE(A2:A6,-2)) | Stapler,Folders |
| TOCOL | TOCOL(array, [ignore], [scan_by_column]) Returns the array in a single column.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,TOCOL(E2:F3)) | 3,-1000,5,400 |
| TOROW | TOROW(array, [ignore], [scan_by_column]) Returns the array in a single row.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,TOROW(E2:F3,,TRUE)) | 3,5,-1000,400 |
| TRANSPOSE | TRANSPOSE(array) Returns a vertical range of cells as a horizontal range, or a horizontal one as vertical.On its own it spills: a column becomes a row. No Ctrl+Shift+Enter is needed. | =TEXTJOIN(",",TRUE,TRANSPOSE(E2:E4)) | 3,5,2 |
| TRIMRANGE | TRIMRANGE(range, [trim_rows], [trim_cols]) Scans in from the edges of a range or array until it finds a non-blank cell, and leaves out the blank rows and columns. | =ROWS(TRIMRANGE(F1:F10)) | 5 |
| UNIQUE | UNIQUE(array, [by_col], [exactly_once]) Returns the different values in a range or array, each once.Text is compared regardless of case, as in Excel. =UNIQUE(B2:B6,,TRUE) keeps only the values there once: East. | =TEXTJOIN(", ",TRUE,UNIQUE(B2:B6)) | North, South, East |
| VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. | =VLOOKUP("Ink",A2:C6,3,FALSE) | 150 |
| VSTACK | VSTACK(array1, [array2], ...) Appends arrays vertically and in sequence to return a larger array.On its own it spills its result into the cells beside it. | =TEXTJOIN(",",TRUE,VSTACK(A2:A3,A6)) | Pens,Paper,Folders |
| WRAPCOLS | WRAPCOLS(vector, wrap_count, [pad_with]) Wraps a row or column vector after a specified number of values, into columns.On its own it spills the columns: 1, 2 then 3, 4 then 5 and the padding, 0. | =TEXTJOIN(",",TRUE,WRAPCOLS(SEQUENCE(5),2,0)) | 1,3,5,2,4,0 |
| WRAPROWS | WRAPROWS(vector, wrap_count, [pad_with]) Wraps a row or column vector after a specified number of values, into rows.On its own it spills the rows: 1, 2 then 3, 4 then 5 and the padding, 0. | =TEXTJOIN(",",TRUE,WRAPROWS(SEQUENCE(5),2,0)) | 1,2,3,4,5,0 |
| XLOOKUP | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Searches a range for a match and returns the corresponding item from a second range. | =XLOOKUP("South",B2:B6,A2:A6,"none",0,-1) | Folders |
| XMATCH | XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) Returns the position of an item in a range or array, searching as XLOOKUP does. | =XMATCH("Ink",A2:A6) | 3 |
Logical (19)
Conditions, and the functions that choose between answers.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| AND | AND(logical1, [logical2], ...) Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. | =AND(C2>100,B2="North") | TRUE |
| BYCOL | BYCOL(array, lambda(column)) Applies a LAMBDA to each column and returns an array of the results, one for each column.BYCOL on its own spills one value to each column, along a row. | =TEXTJOIN(",",TRUE,BYCOL(E2:F6,LAMBDA(c,SUM(c)))) | 20,200 |
| BYROW | BYROW(array, lambda(row)) Applies a LAMBDA to each row and returns an array of the results, one for each row.BYROW on its own spills one value to each row, down a column. | =TEXTJOIN(",",TRUE,BYROW(E2:F4,LAMBDA(r,MAX(r)))) | 3,400,400 |
| FALSE | FALSE() Returns the logical value FALSE. | =FALSE() | FALSE |
| IF | IF(logical_test, [value_if_true], [value_if_false]) Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. | =IF(C2>100,"over","within") | over |
| IFERROR | IFERROR(value, value_if_error) Returns value_if_error if the expression is an error and the value of the expression itself otherwise. | =IFERROR(1/0,"n/a") | n/a |
| IFNA | IFNA(value, value_if_na) Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. | =IFNA(MATCH("Glue",A2:A6,0),"not stocked") | not stocked |
| IFS | IFS(logical_test1, value_if_true1, ...) Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition. | =IFS(C5>=200,"high",C5>=100,"medium",TRUE,"low") | low |
| LAMBDA | LAMBDA([parameter1, parameter2, ...], calculation) Makes a function of your own: to call at once, to give to LET, MAP and the others, or to name in Name Manager and call by that name from any cell.Written into a formula and called there, named with LET and handed to MAP, REDUCE, SCAN, BYROW, BYCOL or MAKEARRAY, or named in Name Manager (Formulas, Define Name, with =LAMBDA(x,y,x*y) as what it refers to) and then called by that name from any cell. A named LAMBDA is kept in an .xlsx as Excel keeps one. | =LAMBDA(x,y,x*y)(C2,E2) | 360 |
| LET | LET(name1, name_value1, [name2, name_value2, ...], calculation) Gives names to values or to results worked out along the way, and uses them in a calculation. | =LET(total,SUM(C2:C6),n,COUNT(C2:C6),total/n) | 122 |
| MAKEARRAY | MAKEARRAY(rows, columns, lambda(row, column)) Returns an array of the size you give, each value worked out by a LAMBDA from its row and column.On its own it spills the array it makes: here two rows of 1, 2, 3 and 2, 4, 6. | =SUM(MAKEARRAY(2,3,LAMBDA(r,c,r*c))) | 18 |
| MAP | MAP(array1, [array2, ...], lambda) Returns an array made by applying a LAMBDA to each value of the arrays.On its own it spills an array the shape of the one it is given. | =TEXTJOIN(",",TRUE,MAP(C2:C4,LAMBDA(v,v*2))) | 240,160,300 |
| NOT | NOT(logical) Changes FALSE to TRUE, or TRUE to FALSE. | =NOT(C5>100) | TRUE |
| OR | OR(logical1, [logical2], ...) Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. | =OR(B2="East",C2>200) | FALSE |
| REDUCE | REDUCE([initial_value], array, lambda(accumulator, value)) Reduces an array to one value by applying a LAMBDA to each value and carrying the result forward. | =REDUCE(0,C2:C6,LAMBDA(acc,v,IF(v>100,acc+v,acc))) | 490 |
| SCAN | SCAN([initial_value], array, lambda(accumulator, value)) Applies a LAMBDA to each value of an array and returns every result along the way.On its own it spills the running results, in the shape of the array. | =TEXTJOIN(",",TRUE,SCAN(0,E2:E6,LAMBDA(a,v,a+v))) | 3,8,10,19,20 |
| SWITCH | SWITCH(expression, value1, result1, [default_or_value2], ...) Evaluates an expression against a list of values and returns the result corresponding to the first matching value. | =SWITCH(B3,"North","N","South","S","other") | S |
| TRUE | TRUE() Returns the logical value TRUE. | =TRUE() | TRUE |
| XOR | XOR(logical1, [logical2], ...) Returns a logical Exclusive Or of all arguments. | =XOR(TRUE,TRUE,TRUE) | TRUE |
Text (38)
Cutting text up, putting it together, regular expressions, and showing a number or a date as text in a given format.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ARRAYTOTEXT | ARRAYTOTEXT(array, [format]) Returns an array of values as text: 0 concise, 1 strict, as a formula would write it. | =ARRAYTOTEXT(A2:B3,1) | {"Pens","North";"Paper","South"} |
| CHAR | CHAR(number) Returns the character specified by the code number. | =CHAR(65) | A |
| CLEAN | CLEAN(text) Removes all nonprintable characters from text. | =LEN(CLEAN("a"&CHAR(9)&"b")) | 2 |
| CODE | CODE(text) Returns a numeric code for the first character in a text string. | =CODE("A") | 65 |
| CONCAT | CONCAT(text1, [text2], ...) Joins a list or range of text strings. | =CONCAT(A2,"-",B2) | Pens-North |
| CONCATENATE | CONCATENATE(text1, [text2], ...) Joins several text strings into one text string. | =CONCATENATE("Q",1," ",2026) | Q1 2026 |
| ENCODEURL | ENCODEURL(text) Returns a URL-encoded string. | =ENCODEURL("a b&c") | a%20b%26c |
| EXACT | EXACT(text1, text2) Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive. | =EXACT("Ink","ink") | FALSE |
| FIND | FIND(find_text, within_text, [start_num]) Returns the starting position of one text string within another. FIND is case-sensitive. | =FIND("n","Banana",4) | 5 |
| FIXED | FIXED(number, [decimals], [no_commas]) Rounds a number to the specified number of decimals and returns the result as text with or without commas. | =FIXED(1234.567,1) | 1,234.6 |
| LEFT | LEFT(text, [num_chars]) Returns the specified number of characters from the start of a text string. | =LEFT("Stapler",4) | Stap |
| LEN | LEN(text) Returns the number of characters in a text string. | =LEN("Folders") | 7 |
| LOWER | LOWER(text) Converts all letters in a text string to lowercase. | =LOWER("NORTH") | north |
| MID | MID(text, start_num, num_chars) Returns the characters from the middle of a text string, given a starting position and length. | =MID("Stapler",2,3) | tap |
| NUMBERVALUE | NUMBERVALUE(text, [decimal_separator], [group_separator]) Converts text to a number in a way that does not depend on the locale. | =NUMBERVALUE("1.234,5",",",".") | 1234.5 |
| PROPER | PROPER(text) Converts a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase. | =PROPER("o'neill and SONS") | O'Neill And Sons |
| REGEXEXTRACT | REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) Extracts text from a string based on a supplied regular expression. | =REGEXEXTRACT("Order 4521 shipped","[0-9]+") | 4521 |
| REGEXMATCH | REGEXMATCH(text, pattern) Checks whether any part of text matches a regular expression, minding case, as in Google Sheets.From Google Sheets; REGEXTEST is Excel’s, and can ignore case. | =REGEXMATCH(A3,"^P") | TRUE |
| REGEXREPLACE | REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) Replaces text within a string based on a supplied regular expression. | =REGEXREPLACE("2026-03-05","(\d+)-(\d+)-(\d+)","$3/$2/$1") | 05/03/2026 |
| REGEXTEST | REGEXTEST(text, pattern, [case_sensitivity]) Determines whether any part of text matches the pattern. | =REGEXTEST(A3,"^P") | TRUE |
| REPLACE | REPLACE(old_text, start_num, num_chars, new_text) Replaces part of a text string with a different text string. | =REPLACE("2026-Q1",6,2,"Q2") | 2026-Q2 |
| REPT | REPT(text, number_times) Repeats text a given number of times. | =REPT("-",5) | ----- |
| RIGHT | RIGHT(text, [num_chars]) Returns the specified number of characters from the end of a text string. | =RIGHT("INV-1042",4) | 1042 |
| SEARCH | SEARCH(find_text, within_text, [start_num]) Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive). | =SEARCH("INK","Printer ink") | 9 |
| SPLIT | SPLIT(text, delimiters, [split_by_each], [remove_empty_text]) Splits text at the delimiters and spills the pieces along a row, as in Google Sheets.From Google Sheets. On its own it spills the pieces along a row; a piece that reads as a number is a number. | =TEXTJOIN("|",TRUE,SPLIT("a, b;c",",;")) | a| b|c |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces existing text with new text in a text string. | =SUBSTITUTE("07700 900 123"," ","") | 07700900123 |
| T | T(value) Checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it is not. | =T(A2) | Pens |
| TEXT | TEXT(value, format_text) Converts a value to text in a specific number format. | =TEXT(D5,"dddd d mmmm yyyy") | Thursday 5 March 2026 |
| TEXTAFTER | TEXTAFTER(text, delimiter, [instance_num]) Returns the text that comes after a delimiter. | =TEXTAFTER("invoice-2026-0042","-") | 2026-0042 |
| TEXTBEFORE | TEXTBEFORE(text, delimiter, [instance_num]) Returns the text that comes before a delimiter. | =TEXTBEFORE("invoice-2026-0042","-",2) | invoice-2026 |
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text1, ...) Joins a list or range of text strings using a delimiter. | =TEXTJOIN(", ",TRUE,A2:A4) | Pens, Paper, Ink |
| TEXTSPLIT | TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) Splits text into rows or columns using delimiters.On its own it spills the pieces along a row, or down a column with a row delimiter. | =TEXTJOIN("|",TRUE,TEXTSPLIT("a,b,c",",")) | a|b|c |
| TRIM | TRIM(text) Removes all spaces from a text string except for single spaces between words. | =TRIM(" two spaces ") | two spaces |
| UNICHAR | UNICHAR(number) Returns the Unicode character referenced by the given numeric value. | =UNICHAR(8364) | € |
| UNICODE | UNICODE(text) Returns the number (code point) corresponding to the first character of the text. | =UNICODE("€") | 8364 |
| UPPER | UPPER(text) Converts a text string to all uppercase letters. | =UPPER("south") | SOUTH |
| VALUE | VALUE(text) Converts a text string that represents a number to a number. | =VALUE("1,250.50") | 1250.5 |
| VALUETOTEXT | VALUETOTEXT(value, [format]) Returns a value as text: 0 concise, 1 strict, with text in quotation marks. | =VALUETOTEXT(A2,1) | "Pens" |
Date & Time (25)
Dates are counted in days from 1900, as in Excel, so a date can be taken from a date, and a time is a fraction of a day: 0.5 is noon.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| DATE | DATE(year, month, day) Returns the date for the given year, month and day.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =DATE(2026,2,30) | 2026-03-02 |
| DATEDIF | DATEDIF(start_date, end_date, unit) Returns the number of days, months or years between two dates. | =DATEDIF(D2,D6,"M") | 2 |
| DATEVALUE | DATEVALUE(date_text) Converts a date in the form of text to a number that represents the date. | =DATEVALUE("2026-03-09") | 46090 |
| DAY | DAY(serial_number) Returns the day of the month, a number from 1 to 31. | =DAY(D6) | 31 |
| DAYS | DAYS(end_date, start_date) Returns the number of days between the two dates. | =DAYS(D6,D2) | 75 |
| DAYS360 | DAYS360(start_date, end_date, [method]) Returns the number of days between two dates based on a 360-day year. | =DAYS360("2026-01-30","2026-03-31") | 60 |
| EDATE | EDATE(start_date, months) Returns the date that is the indicated number of months before or after the start date.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =EDATE("2026-01-31",1) | 2026-02-28 |
| EOMONTH | EOMONTH(start_date, months) Returns the last day of the month before or after a specified number of months.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =EOMONTH("2026-02-10",0) | 2026-02-28 |
| HOUR | HOUR(serial_number) Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). | =HOUR("2:35 PM") | 14 |
| ISOWEEKNUM | ISOWEEKNUM(date) Returns the ISO week number in the year for a given date. | =ISOWEEKNUM("2027-01-01") | 53 |
| MINUTE | MINUTE(serial_number) Returns the minute, a number from 0 to 59. | =MINUTE(875/1440) | 35 |
| MONTH | MONTH(serial_number) Returns the month, a number from 1 (January) to 12 (December). | =MONTH(D3) | 2 |
| NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole workdays between two dates. | =NETWORKDAYS("2026-09-28","2026-10-09") | 10 |
| NETWORKDAYS.INTL | NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) Returns the number of whole workdays between two dates with custom weekend parameters. | =NETWORKDAYS.INTL(D2,D6,11) | 65 |
| NOW | NOW() Returns the current date and time.Gives the date and time as text, yyyy-mm-dd hh:mm, by this computer’s clock, to the minute. | =YEAR(NOW())>=2026 | TRUE |
| SECOND | SECOND(serial_number) Returns the second, a number from 0 to 59. | =SECOND(43209/86400) | 9 |
| TIME | TIME(hour, minute, second) Converts hours, minutes and seconds given as numbers to a time, the fraction of a day. | =ROUND(TIME(14,35,0),6) | 0.607639 |
| TIMEVALUE | TIMEVALUE(time_text) Converts a text time to a number that represents the time, from 0 (12:00:00 AM) to 0.999988426 (11:59:59 PM). | =TIMEVALUE("2:24 AM") | 0.1 |
| TODAY | TODAY() Returns the current date.Gives today as text, yyyy-mm-dd, so =ISNUMBER(TODAY()) is FALSE here and TRUE in Excel. Date arithmetic on it works as usual. | =YEAR(TODAY())>=2026 | TRUE |
| WEEKDAY | WEEKDAY(serial_number, [return_type]) Returns a number from 1 to 7 identifying the day of the week of a date. | =WEEKDAY(D5,2) | 4 |
| WEEKNUM | WEEKNUM(serial_number, [return_type]) Returns the week number in the year.Takes Excel’s return types: 1 or 17 for weeks from Sunday, 2 or 11 from Monday, 12 to 16 for Tuesday to Saturday, and 21 for the ISO week. | =WEEKNUM("2026-03-08",2) | 10 |
| WORKDAY | WORKDAY(start_date, days, [holidays]) Returns the date before or after a specified number of workdays.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =WORKDAY(D5,3) | 2026-03-10 |
| WORKDAY.INTL | WORKDAY.INTL(start_date, days, [weekend], [holidays]) Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.Gives the date as text, written yyyy-mm-dd, where Excel gives a date serial number shown as a date. It still counts as a date in arithmetic and comparisons. | =WORKDAY.INTL(D5,3,11) | 2026-03-09 |
| YEAR | YEAR(serial_number) Returns the year of a date, an integer in the range 1900 - 9999. | =YEAR(D2) | 2026 |
| YEARFRAC | YEARFRAC(start_date, end_date, [basis]) Returns the year fraction representing the number of whole days between start_date and end_date. | =ROUND(YEARFRAC(D2,D6),6) | 0.211111 |
Financial (52)
Loans, savings, cash flows, depreciation, bonds and bills. Money paid out is negative and rates are per period, as in Excel: a 6% annual rate paid monthly is 0.06/12. The bond functions count days in Excel’s five ways, the basis argument from 0 to 4.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ACCRINT | ACCRINT(issue, first_interest, settlement, rate, [par], frequency, [basis], [calc_method]) Returns the accrued interest for a security that pays periodic interest. | =ROUND(ACCRINT(DATE(2008,3,1),DATE(2008,8,31),DATE(2008,5,1),0.1,1000,2,0),6) | 16.666667 |
| ACCRINTM | ACCRINTM(issue, settlement, rate, [par], [basis]) Returns the accrued interest for a security that pays interest at maturity. | =ROUND(ACCRINTM(DATE(2008,4,1),DATE(2008,6,15),0.1,1000,3),6) | 20.547945 |
| COUPDAYBS | COUPDAYBS(settlement, maturity, frequency, [basis]) Returns the number of days from the beginning of the coupon period to the settlement date. | =COUPDAYBS(DATE(2011,1,25),DATE(2011,11,15),2,1) | 71 |
| COUPDAYS | COUPDAYS(settlement, maturity, frequency, [basis]) Returns the number of days in the coupon period that contains the settlement date. | =COUPDAYS(DATE(2011,1,25),DATE(2011,11,15),2,1) | 181 |
| COUPDAYSNC | COUPDAYSNC(settlement, maturity, frequency, [basis]) Returns the number of days from the settlement date to the next coupon date. | =COUPDAYSNC(DATE(2011,1,25),DATE(2011,11,15),2,1) | 110 |
| COUPNCD | COUPNCD(settlement, maturity, frequency, [basis]) Returns the next coupon date after the settlement date. | =COUPNCD(DATE(2011,1,25),DATE(2011,11,15),2,1) | 2011-05-15 |
| COUPNUM | COUPNUM(settlement, maturity, frequency, [basis]) Returns the number of coupons payable between the settlement date and the maturity date. | =COUPNUM(DATE(2007,1,25),DATE(2008,11,15),2,1) | 4 |
| COUPPCD | COUPPCD(settlement, maturity, frequency, [basis]) Returns the previous coupon date before the settlement date. | =COUPPCD(DATE(2011,1,25),DATE(2011,11,15),2,1) | 2010-11-15 |
| CUMIPMT | CUMIPMT(rate, nper, pv, start_period, end_period, type) Returns the cumulative interest paid between two periods. | =ROUND(CUMIPMT(0.09/12,30*12,125000,13,24,0),2) | -11135.23 |
| CUMPRINC | CUMPRINC(rate, nper, pv, start_period, end_period, type) Returns the cumulative principal paid on a loan between two periods. | =ROUND(CUMPRINC(0.09/12,30*12,125000,13,24,0),2) | -934.11 |
| DB | DB(cost, salvage, life, period, [month]) Returns the depreciation of an asset for a specified period using the fixed-declining balance method. | =ROUND(DB(1000000,100000,6,1,7),2) | 186083.33 |
| DDB | DDB(cost, salvage, life, period, [factor]) Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. | =DDB(2400,300,10,1) | 480 |
| DISC | DISC(settlement, maturity, pr, redemption, [basis]) Returns the discount rate for a security. | =ROUND(DISC(DATE(2018,1,25),DATE(2018,6,15),97.975,100,1),6) | 0.05242 |
| DOLLARDE | DOLLARDE(fractional_dollar, fraction) Converts a dollar price, expressed as a fraction, into a dollar price expressed as a decimal number. | =DOLLARDE(1.02,16) | 1.125 |
| DOLLARFR | DOLLARFR(decimal_dollar, fraction) Converts a dollar price, expressed as a decimal number, into a dollar price expressed as a fraction. | =DOLLARFR(1.125,16) | 1.02 |
| DURATION | DURATION(settlement, maturity, coupon, yld, frequency, [basis]) Returns the annual duration of a security with periodic interest payments. | =ROUND(DURATION(DATE(2018,7,1),DATE(2048,1,1),0.08,0.09,2,1),4) | 10.9191 |
| EFFECT | EFFECT(nominal_rate, npery) Returns the effective annual interest rate. | =ROUND(EFFECT(0.0525,4),6) | 0.053543 |
| FV | FV(rate, nper, pmt, [pv], [type]) Returns the future value of an investment based on periodic, constant payments and a constant interest rate. | =ROUND(FV(0.05/12,12,-100),2) | 1227.89 |
| FVSCHEDULE | FVSCHEDULE(principal, schedule) Returns the future value of an initial principal after applying a series of compound interest rates. | =FVSCHEDULE(1,{0.09,0.11,0.1}) | 1.33089 |
| INTRATE | INTRATE(settlement, maturity, investment, redemption, [basis]) Returns the interest rate for a fully invested security. | =ROUND(INTRATE(DATE(2008,2,15),DATE(2008,5,15),1000000,1014420,2),5) | 0.05768 |
| IPMT | IPMT(rate, per, nper, pv, [fv], [type]) Returns the interest payment for a given period for an investment. | =ROUND(IPMT(0.06/12,1,36,10000),2) | -50 |
| IRR | IRR(values, [guess]) Returns the internal rate of return for a series of cash flows. | =ROUND(IRR(F2:F5),4) | 0.097 |
| ISPMT | ISPMT(rate, per, nper, pv) Returns the interest paid during a specific period of an investment. | =ROUND(ISPMT(0.1/12,1,36,8000000),2) | -64814.81 |
| MDURATION | MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) Returns the modified Macaulay duration for a security with an assumed par value of 100. | =ROUND(MDURATION(DATE(2008,1,1),DATE(2016,1,1),0.08,0.09,2,1),4) | 5.7357 |
| MIRR | MIRR(values, finance_rate, reinvest_rate) Returns the internal rate of return for a series of periodic cash flows, considering both cost of investment and interest on reinvestment of cash. | =ROUND(MIRR(F2:F5,0.1,0.12),4) | 0.1051 |
| NOMINAL | NOMINAL(effect_rate, npery) Returns the annual nominal interest rate. | =ROUND(NOMINAL(0.053543,4),4) | 0.0525 |
| NPER | NPER(rate, pmt, pv, [fv], [type]) Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | =ROUND(NPER(0.01,-100,1000),2) | 10.59 |
| NPV | NPV(rate, value1, [value2], ...) Returns the net present value of an investment based on a discount rate and a series of future payments and income. | =ROUND(NPV(0.1,F3:F5),2) | 994.74 |
| ODDFPRICE | ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis]) Returns the price per 100 face value of a security with an odd first period. | =ROUND(ODDFPRICE(DATE(2008,11,11),DATE(2021,3,1),DATE(2008,10,15),DATE(2009,3,1),0.0785,0.0625,100,2,1),4) | 113.5977 |
| ODDFYIELD | ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis]) Returns the yield of a security with an odd first period. | =ROUND(ODDFYIELD(DATE(2008,11,11),DATE(2021,3,1),DATE(2008,10,15),DATE(2009,3,1),0.0575,84.5,100,2,0),4) | 0.0772 |
| ODDLPRICE | ODDLPRICE(settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis]) Returns the price per 100 face value of a security with an odd last period. | =ROUND(ODDLPRICE(DATE(2008,2,7),DATE(2008,6,15),DATE(2007,10,15),0.0375,0.0405,100,2,0),4) | 99.8783 |
| ODDLYIELD | ODDLYIELD(settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis]) Returns the yield of a security with an odd last period. | =ROUND(ODDLYIELD(DATE(2008,4,20),DATE(2008,6,15),DATE(2007,12,24),0.0375,99.875,100,2,0),6) | 0.045192 |
| PDURATION | PDURATION(rate, pv, fv) Returns the number of periods required by an investment to reach a specified value. | =ROUND(PDURATION(0.025,2000,2200),2) | 3.86 |
| PMT | PMT(rate, nper, pv, [fv], [type]) Calculates the payment for a loan based on constant payments and a constant interest rate. | =ROUND(PMT(0.06/12,36,10000),2) | -304.22 |
| PPMT | PPMT(rate, per, nper, pv, [fv], [type]) Returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate. | =ROUND(PPMT(0.06/12,1,36,10000),2) | -254.22 |
| PRICE | PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) Returns the price per 100 face value of a security that pays periodic interest. | =ROUND(PRICE(DATE(2008,2,15),DATE(2017,11,15),0.0575,0.065,100,2,0),6) | 94.634362 |
| PRICEDISC | PRICEDISC(settlement, maturity, discount, redemption, [basis]) Returns the price per 100 face value of a discounted security. | =ROUND(PRICEDISC(DATE(2008,2,16),DATE(2008,3,1),0.0525,100,2),5) | 99.79583 |
| PRICEMAT | PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) Returns the price per 100 face value of a security that pays interest at maturity. | =ROUND(PRICEMAT(DATE(2008,2,15),DATE(2008,4,13),DATE(2007,11,11),0.061,0.061,0),6) | 99.984499 |
| PV | PV(rate, nper, pmt, [fv], [type]) Returns the present value of an investment: the total amount that a series of future payments is worth now. | =ROUND(PV(0.05/12,60,-200),2) | 10598.14 |
| RATE | RATE(nper, pmt, pv, [fv], [type], [guess]) Returns the interest rate per period of a loan or an investment. | =ROUND(RATE(36,-304.22,10000)*12,4) | 0.06 |
| RECEIVED | RECEIVED(settlement, maturity, investment, discount, [basis]) Returns the amount received at maturity for a fully invested security. | =ROUND(RECEIVED(DATE(2008,2,15),DATE(2008,5,15),1000000,0.0575,2),2) | 1014584.65 |
| RRI | RRI(nper, pv, fv) Returns an equivalent interest rate for the growth of an investment. | =ROUND(RRI(96,10000,11000),6) | 0.000993 |
| SLN | SLN(cost, salvage, life) Returns the straight-line depreciation of an asset for one period. | =SLN(30000,7500,10) | 2250 |
| SYD | SYD(cost, salvage, life, per) Returns the sum-of-years’ digits depreciation of an asset for a specified period. | =ROUND(SYD(30000,7500,10,1),2) | 4090.91 |
| TBILLEQ | TBILLEQ(settlement, maturity, discount) Returns the bond-equivalent yield for a Treasury bill. | =ROUND(TBILLEQ(DATE(2008,3,31),DATE(2008,6,1),0.0914),6) | 0.094151 |
| TBILLPRICE | TBILLPRICE(settlement, maturity, discount) Returns the price per 100 face value for a Treasury bill. | =TBILLPRICE(DATE(2008,3,31),DATE(2008,6,1),0.09) | 98.45 |
| TBILLYIELD | TBILLYIELD(settlement, maturity, pr) Returns the yield for a Treasury bill. | =ROUND(TBILLYIELD(DATE(2008,3,31),DATE(2008,6,1),98.45),6) | 0.091417 |
| XIRR | XIRR(values, dates, [guess]) Returns the internal rate of return for a schedule of cash flows. | =ROUND(XIRR(F2:F5,D2:D5),4) | 6.2777 |
| XNPV | XNPV(rate, values, dates) Returns the net present value for a schedule of cash flows. | =ROUND(XNPV(0.09,F2:F5,D2:D5),2) | 190.41 |
| YIELD | YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) Returns the yield on a security that pays periodic interest. | =ROUND(YIELD(DATE(2008,2,15),DATE(2016,11,15),0.0575,95.04287,100,2,0),4) | 0.065 |
| YIELDDISC | YIELDDISC(settlement, maturity, pr, redemption, [basis]) Returns the annual yield for a discounted security, for example a Treasury bill. | =ROUND(YIELDDISC(DATE(2008,2,16),DATE(2008,3,1),99.795,100,2),6) | 0.052823 |
| YIELDMAT | YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) Returns the annual yield of a security that pays interest at maturity. | =ROUND(YIELDMAT(DATE(2008,3,15),DATE(2008,11,3),DATE(2007,11,8),0.0625,100.0123,0),6) | 0.060954 |
Information (16)
Questions about a value: whether it is a number, text, blank or an error.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| ERROR.TYPE | ERROR.TYPE(error_val) Returns a number matching an error value. | =ERROR.TYPE(1/0) | 2 |
| ISBLANK | ISBLANK(value) Checks whether a reference is to an empty cell, and returns TRUE or FALSE. | =ISBLANK(H2) | TRUE |
| ISERR | ISERR(value) Checks whether a value is an error other than #N/A, and returns TRUE or FALSE. | =ISERR(NA()) | FALSE |
| ISERROR | ISERROR(value) Checks whether a value is an error, and returns TRUE or FALSE. | =ISERROR(1/0) | TRUE |
| ISEVEN | ISEVEN(number) Returns TRUE if the number is even. | =ISEVEN(C2) | TRUE |
| ISFORMULA | ISFORMULA(reference) Checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE. | =ISFORMULA(G2) | TRUE |
| ISLOGICAL | ISLOGICAL(value) Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE. | =ISLOGICAL(C2>100) | TRUE |
| ISNA | ISNA(value) Checks whether a value is #N/A, and returns TRUE or FALSE. | =ISNA(NA()) | TRUE |
| ISNONTEXT | ISNONTEXT(value) Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE. | =ISNONTEXT(A2) | FALSE |
| ISNUMBER | ISNUMBER(value) Checks whether a value is a number, and returns TRUE or FALSE. | =ISNUMBER(C2) | TRUE |
| ISODD | ISODD(number) Returns TRUE if the number is odd. | =ISODD(3) | TRUE |
| ISREF | ISREF(value) Checks whether a value is a reference, and returns TRUE or FALSE. | =ISREF(C2) | TRUE |
| ISTEXT | ISTEXT(value) Checks whether a value is text, and returns TRUE or FALSE. | =ISTEXT(A2) | TRUE |
| N | N(value) Converts a value that is not a number to a number, dates to serial numbers, TRUE to 1, anything else to 0. | =N(TRUE) | 1 |
| NA | NA() Returns the error value #N/A (value not available). | =NA() | #N/A |
| TYPE | TYPE(value) Returns an integer representing the data type of a value: number = 1; text = 2; logical value = 4; array = 64. | =TYPE("a") | 2 |
Engineering (50)
Numbers in binary, octal and hexadecimal, bitwise arithmetic, the error function, CONVERT between units of measurement, and complex numbers, written as text the way Excel writes them: “3+4i” or “3+4j”.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| BIN2DEC | BIN2DEC(number) Converts a binary number to decimal. | =BIN2DEC("1100100") | 100 |
| BIN2HEX | BIN2HEX(number, [places]) Converts a binary number to hexadecimal. | =BIN2HEX("11111011",4) | 00FB |
| BIN2OCT | BIN2OCT(number, [places]) Converts a binary number to octal. | =BIN2OCT("1001",3) | 011 |
| BITAND | BITAND(number1, number2) Returns a bitwise And of two numbers. | =BITAND(13,25) | 9 |
| BITLSHIFT | BITLSHIFT(number, shift_amount) Returns a number shifted left by shift_amount bits. | =BITLSHIFT(4,2) | 16 |
| BITOR | BITOR(number1, number2) Returns a bitwise Or of two numbers. | =BITOR(23,10) | 31 |
| BITRSHIFT | BITRSHIFT(number, shift_amount) Returns a number shifted right by shift_amount bits. | =BITRSHIFT(13,2) | 3 |
| BITXOR | BITXOR(number1, number2) Returns a bitwise Exclusive Or of two numbers. | =BITXOR(5,3) | 6 |
| COMPLEX | COMPLEX(real_num, i_num, [suffix]) Converts real and imaginary coefficients into a complex number. | =COMPLEX(3,4) | 3+4i |
| CONVERT | CONVERT(number, from_unit, to_unit) Converts a number from one measurement system to another. | =ROUND(CONVERT(26.2,"mi","km"),2) | 42.16 |
| DEC2BIN | DEC2BIN(number, [places]) Converts a decimal number to binary. | =DEC2BIN(9,4) | 1001 |
| DEC2HEX | DEC2HEX(number, [places]) Converts a decimal number to hexadecimal. | =DEC2HEX(255) | FF |
| DEC2OCT | DEC2OCT(number, [places]) Converts a decimal number to octal. | =DEC2OCT(58,3) | 072 |
| DELTA | DELTA(number1, [number2]) Tests whether two numbers are equal. | =DELTA(5,4) | 0 |
| ERF | ERF(lower_limit, [upper_limit]) Returns the error function. | =ROUND(ERF(0.745),6) | 0.707929 |
| ERF.PRECISE | ERF.PRECISE(x) Returns the error function. | =ROUND(ERF.PRECISE(1),6) | 0.842701 |
| ERFC | ERFC(x) Returns the complementary error function. | =ROUND(ERFC(1),6) | 0.157299 |
| ERFC.PRECISE | ERFC.PRECISE(x) Returns the complementary error function. | =ROUND(ERFC.PRECISE(1),6) | 0.157299 |
| GESTEP | GESTEP(number, [step]) Tests whether a number is greater than a threshold value. | =GESTEP(5,4) | 1 |
| HEX2BIN | HEX2BIN(number, [places]) Converts a hexadecimal number to binary. | =HEX2BIN("F",8) | 00001111 |
| HEX2DEC | HEX2DEC(number) Converts a hexadecimal number to decimal. | =HEX2DEC("A5") | 165 |
| HEX2OCT | HEX2OCT(number, [places]) Converts a hexadecimal number to octal. | =HEX2OCT("F",3) | 017 |
| IMABS | IMABS(inumber) Returns the absolute value (modulus) of a complex number. | =IMABS("3+4i") | 5 |
| IMAGINARY | IMAGINARY(inumber) Returns the imaginary coefficient of a complex number. | =IMAGINARY("3+4i") | 4 |
| IMARGUMENT | IMARGUMENT(inumber) Returns the argument theta, an angle expressed in radians. | =ROUND(IMARGUMENT("3+4i"),6) | 0.927295 |
| IMCONJUGATE | IMCONJUGATE(inumber) Returns the complex conjugate of a complex number. | =IMCONJUGATE("3+4i") | 3-4i |
| IMCOS | IMCOS(inumber) Returns the cosine of a complex number. | =IMCOS("1+i") | 0.833730025131149-0.988897705762865i |
| IMCOSH | IMCOSH(inumber) Returns the hyperbolic cosine of a complex number. | =IMCOSH("4+3i") | -27.0349456030742+3.85115333481178i |
| IMCOT | IMCOT(inumber) Returns the cotangent of a complex number. | =ROUND(IMREAL(IMCOT("4+3i")),6) | 0.004901 |
| IMCSC | IMCSC(inumber) Returns the cosecant of a complex number. | =IMCSC("4+3i") | -0.0754898329158637+0.0648774713706355i |
| IMCSCH | IMCSCH(inumber) Returns the hyperbolic cosecant of a complex number. | =ROUND(IMREAL(IMCSCH("4+3i")),6) | -0.036276 |
| IMDIV | IMDIV(inumber1, inumber2) Returns the quotient of two complex numbers. | =IMDIV("-238+240i","10+24i") | 5+12i |
| IMEXP | IMEXP(inumber) Returns the exponential of a complex number. | =IMEXP("1+i") | 1.46869393991589+2.28735528717884i |
| IMLN | IMLN(inumber) Returns the natural logarithm of a complex number. | =IMLN("3+4i") | 1.6094379124341+0.927295218001612i |
| IMLOG10 | IMLOG10(inumber) Returns the base-10 logarithm of a complex number. | =IMLOG10("3+4i") | 0.698970004336019+0.402719196273373i |
| IMLOG2 | IMLOG2(inumber) Returns the base-2 logarithm of a complex number. | =IMLOG2("3+4i") | 2.32192809488736+1.33780421245098i |
| IMPOWER | IMPOWER(inumber, number) Returns a complex number raised to a power.Worked out through the polar form, as in Excel, which is why the last figure is not 9. | =IMPOWER("2+3i",3) | -46+9.00000000000001i |
| IMPRODUCT | IMPRODUCT(inumber1, [inumber2], ...) Returns the product of complex numbers. | =IMPRODUCT("3+4i","5-3i") | 27+11i |
| IMREAL | IMREAL(inumber) Returns the real coefficient of a complex number. | =IMREAL("6-9i") | 6 |
| IMSEC | IMSEC(inumber) Returns the secant of a complex number. | =IMSEC("4+3i") | -0.0652940278579471-0.0752249603027732i |
| IMSECH | IMSECH(inumber) Returns the hyperbolic secant of a complex number. | =IMSECH("4+3i") | -0.0362534969158689-0.00516434460775318i |
| IMSIN | IMSIN(inumber) Returns the sine of a complex number. | =IMSIN("4+3i") | -7.61923172032141-6.548120040911i |
| IMSINH | IMSINH(inumber) Returns the hyperbolic sine of a complex number. | =IMSINH("4+3i") | -27.0168132580039+3.85373803791938i |
| IMSQRT | IMSQRT(inumber) Returns the square root of a complex number. | =IMSQRT("1+i") | 1.09868411346781+0.455089860562227i |
| IMSUB | IMSUB(inumber1, inumber2) Returns the difference between two complex numbers. | =IMSUB("13+4i","5+3i") | 8+i |
| IMSUM | IMSUM(inumber1, [inumber2], ...) Returns the sum of complex numbers. | =IMSUM("3+4i","5-3i") | 8+i |
| IMTAN | IMTAN(inumber) Returns the tangent of a complex number. | =IMTAN("4+3i") | 0.00490825806749606+1.00070953606723i |
| OCT2BIN | OCT2BIN(number, [places]) Converts an octal number to binary. | =OCT2BIN("3",3) | 011 |
| OCT2DEC | OCT2DEC(number) Converts an octal number to decimal. | =OCT2DEC("54") | 44 |
| OCT2HEX | OCT2HEX(number, [places]) Converts an octal number to hexadecimal. | =OCT2HEX("100",4) | 0040 |
Database (12)
Totals over the rows of a table that meet conditions laid out in a block of their own: a row of headings, and under it the conditions, one row for each way a record can qualify.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| DAVERAGE | DAVERAGE(database, field, criteria) Averages the values in a column of a list or database that match the conditions you specify.B1:B2 is the criteria: the heading Region and, under it, North. | =DAVERAGE(A1:F6,"Amount",B1:B2) | 135 |
| DCOUNT | DCOUNT(database, [field], criteria) Counts the cells containing numbers in a field of the records that match the conditions you specify. | =DCOUNT(A1:F6,"Units",B1:B2) | 2 |
| DCOUNTA | DCOUNTA(database, [field], criteria) Counts the nonblank cells in a field of the records that match the conditions you specify.Two rows of conditions under one heading, North or South. | =DCOUNTA(A1:F6,"Item",B1:B3) | 4 |
| DGET | DGET(database, field, criteria) Extracts from a database the single record that matches the conditions you specify. | =DGET(A1:F6,"Amount",A1:A2) | 120 |
| DMAX | DMAX(database, field, criteria) Returns the largest number in a field of the records that match the conditions you specify. | =DMAX(A1:F6,"Amount",B1:B3) | 220 |
| DMIN | DMIN(database, field, criteria) Returns the smallest number in a field of the records that match the conditions you specify. | =DMIN(A1:F6,"Amount",B1:B3) | 80 |
| DPRODUCT | DPRODUCT(database, field, criteria) Multiplies the values in a field of the records that match the conditions you specify. | =DPRODUCT(A1:F6,"Units",B1:B2) | 6 |
| DSTDEV | DSTDEV(database, field, criteria) Estimates the standard deviation based on a sample from the selected database entries. | =ROUND(DSTDEV(A1:F6,"Amount",B1:B3),4) | 59.0903 |
| DSTDEVP | DSTDEVP(database, field, criteria) Calculates the standard deviation based on the entire population of the selected database entries. | =ROUND(DSTDEVP(A1:F6,"Amount",B1:B3),4) | 51.1737 |
| DSUM | DSUM(database, field, criteria) Adds the numbers in a field of the records that match the conditions you specify.Plain text in the criteria finds the values that begin with it, as Excel’s does; a criterion written as the formula =“=North” finds North alone. | =DSUM(A1:F6,"Amount",B1:B2) | 270 |
| DVAR | DVAR(database, field, criteria) Estimates variance based on a sample from the selected database entries. | =ROUND(DVAR(A1:F6,"Amount",B1:B3),2) | 3491.67 |
| DVARP | DVARP(database, field, criteria) Calculates variance based on the entire population of the selected database entries. | =DVARP(A1:F6,"Amount",B1:B3) | 2618.75 |
Compatibility (31)
The names Excel kept from before 2010, each giving what the function that replaced it gives: NORMDIST is NORM.DIST. Workbooks that use them keep their answers; a new formula is better written with the newer name.
| Function | What it takes and does | Example | Gives |
|---|---|---|---|
| BETADIST | BETADIST(x, alpha, beta, [A], [B]) Returns the cumulative beta probability density function. | =ROUND(BETADIST(2,8,10,1,3),6) | 0.685471 |
| BETAINV | BETAINV(probability, alpha, beta, [A], [B]) Returns the inverse of the cumulative beta probability density function. | =ROUND(BETAINV(0.685470581,8,10,1,3),4) | 2 |
| BINOMDIST | BINOMDIST(number_s, trials, probability_s, cumulative) Returns the individual term binomial distribution probability. | =ROUND(BINOMDIST(6,10,0.5,TRUE),6) | 0.828125 |
| CHIDIST | CHIDIST(x, deg_freedom) Returns the right-tailed probability of the chi-squared distribution. | =ROUND(CHIDIST(18.307,10),6) | 0.050001 |
| CHIINV | CHIINV(probability, deg_freedom) Returns the inverse of the right-tailed probability of the chi-squared distribution. | =ROUND(CHIINV(0.05,10),4) | 18.307 |
| CHITEST | CHITEST(actual_range, expected_range) Returns the test for independence: the value from the chi-squared distribution for the statistic and the degrees of freedom. | =ROUND(CHITEST({58,35;11,25;10,23},{45.35,47.65;17.56,18.44;16.09,16.91}),6) | 0.000308 |
| CONFIDENCE | CONFIDENCE(alpha, standard_dev, size) Returns the confidence interval for a population mean, using a normal distribution. | =ROUND(CONFIDENCE(0.05,2.5,50),6) | 0.692952 |
| COVAR | COVAR(array1, array2) Returns covariance, the average of the products of deviations for each data point pair in two data sets. | =COVAR(C2:C6,E2:E6) | -160 |
| CRITBINOM | CRITBINOM(trials, probability_s, alpha) Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. | =CRITBINOM(6,0.5,0.75) | 4 |
| EXPONDIST | EXPONDIST(x, lambda, cumulative) Returns the exponential distribution. | =ROUND(EXPONDIST(0.2,10,FALSE),6) | 1.353353 |
| FDIST | FDIST(x, deg_freedom1, deg_freedom2) Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets. | =ROUND(FDIST(15.2069,6,4),6) | 0.01 |
| FINV | FINV(probability, deg_freedom1, deg_freedom2) Returns the inverse of the (right-tailed) F probability distribution. | =ROUND(FINV(0.01,6,4),4) | 15.2069 |
| FORECAST | FORECAST(x, known_ys, known_xs) Calculates, or predicts, a future value along a linear trend by using existing values. | =ROUND(FORECAST(10,C2:C6,E2:E6),4) | 2 |
| FTEST | FTEST(array1, array2) Returns the result of an F-test, the two-tailed probability that the variances in Array1 and Array2 are not significantly different. | =ROUND(FTEST({6,7,9,15,21},{20,28,31,38,40}),6) | 0.648318 |
| GAMMADIST | GAMMADIST(x, alpha, beta, cumulative) Returns the gamma distribution. | =ROUND(GAMMADIST(10,9,2,FALSE),6) | 0.032639 |
| GAMMAINV | GAMMAINV(probability, alpha, beta) Returns the inverse of the gamma cumulative distribution. | =ROUND(GAMMAINV(0.068094,9,2),3) | 10 |
| HYPGEOMDIST | HYPGEOMDIST(sample_s, number_sample, population_s, number_pop) Returns the hypergeometric distribution. | =ROUND(HYPGEOMDIST(1,4,8,20),6) | 0.363261 |
| LOGINV | LOGINV(probability, mean, standard_dev) Returns the inverse of the lognormal cumulative distribution function of x. | =ROUND(LOGINV(0.039084,3.5,1.2),4) | 4 |
| LOGNORMDIST | LOGNORMDIST(x, mean, standard_dev) Returns the cumulative lognormal distribution of x. | =ROUND(LOGNORMDIST(4,3.5,1.2),6) | 0.039084 |
| NEGBINOMDIST | NEGBINOMDIST(number_f, number_s, probability_s) Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success. | =ROUND(NEGBINOMDIST(10,5,0.25),6) | 0.055049 |
| NORMDIST | NORMDIST(x, mean, standard_dev, cumulative) Returns the normal cumulative distribution for the specified mean and standard deviation. | =ROUND(NORMDIST(42,40,1.5,FALSE),6) | 0.10934 |
| NORMINV | NORMINV(probability, mean, standard_dev) Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. | =ROUND(NORMINV(0.908789,40,1.5),4) | 42 |
| NORMSDIST | NORMSDIST(z) Returns the standard normal cumulative distribution (has a mean of zero and a standard deviation of one). | =ROUND(NORMSDIST(1.333333),6) | 0.908789 |
| NORMSINV | NORMSINV(probability) Returns the inverse of the standard normal cumulative distribution (has a mean of zero and a standard deviation of one). | =ROUND(NORMSINV(0.908789),4) | 1.3333 |
| PERCENTRANK | PERCENTRANK(array, x, [significance]) Returns the rank of a value in a data set as a percentage of the data set. | =PERCENTRANK(C2:C6,100) | 0.375 |
| POISSON | POISSON(x, mean, cumulative) Returns the Poisson distribution. | =ROUND(POISSON(2,5,FALSE),6) | 0.084224 |
| TDIST | TDIST(x, deg_freedom, tails) Returns the Student’s t-distribution. | =ROUND(TDIST(1.96,60,2),6) | 0.054645 |
| TINV | TINV(probability, deg_freedom) Returns the two-tailed inverse of the Student’s t-distribution. | =ROUND(TINV(0.05,60),6) | 2.000298 |
| TTEST | TTEST(array1, array2, tails, type) Returns the probability associated with a Student’s t-Test. | =ROUND(TTEST({3,4,5,8,9,1,2,4,5},{6,19,3,2,14,4,5,17,1},1,2),6) | 0.095998 |
| WEIBULL | WEIBULL(x, alpha, beta, cumulative) Returns the Weibull distribution. | =ROUND(WEIBULL(105,20,100,FALSE),6) | 0.035589 |
| ZTEST | ZTEST(array, x, [sigma]) Returns the one-tailed P-value of a z-test. | =ROUND(ZTEST({3,6,7,8,6,5,4,2,1,9},4),6) | 0.090574 |
Excel functions Sheets does not have yet
- ISOMITTED. Not in the engine yet. A LAMBDA works inside a formula, with LET, and named in Name Manager to call from any cell.
- VDB. Variable declining balance depreciation. DDB, DB, SLN and SYD are there.
- BESSELJ, BESSELY, BESSELI, BESSELK. The Bessel functions, from Excel’s Engineering category.
- CELL, INFO, SHEET, SHEETS. Less common, and not yet in the engine.
An .xlsx that uses one of these still opens, and each such cell shows the value Excel last saved in it. The formula itself is not kept: the cell holds that value from then on, and saving the workbook writes the value, not the formula. Keep a copy of the original if the formula matters.
Open a new workbook in Sheets. It is free, needs no account, and the workbook is kept in this browser.