Settings

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.

ABCDEFG
1ItemRegionAmountDateUnitsCash flowValue
2PensNorth1202026-01-153-1000=C2*E2
3PaperSouth802026-02-015400=C3*E3
4InkNorth1502026-02-202400=C4*E4
5StaplerEast402026-03-059400=C5*E5
6FoldersSouth2202026-03-311=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-D2 is 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:A6 over a column with a gap spills the gap as an empty cell, where Excel shows 0, as Sheets does for =A3 on 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.

FunctionWhat it takes and doesExampleGives
ABSABS(number)
Returns the absolute value of a number, a number without its sign.
=ABS(-7.5)7.5
ACOSACOS(number)
Returns the arccosine of a number, in radians.
=ROUND(ACOS(0.5),6)1.047198
ACOSHACOSH(number)
Returns the inverse hyperbolic cosine of a number.
=ROUND(ACOSH(10),6)2.993223
ACOTACOT(number)
Returns the arccotangent of a number, in radians in the range 0 to Pi.
=ROUND(ACOT(2),6)0.463648
ACOTHACOTH(number)
Returns the inverse hyperbolic cotangent of a number.
=ROUND(ACOTH(6),6)0.168236
AGGREGATEAGGREGATE(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
ARABICARABIC(text)
Converts a Roman numeral to Arabic.
=ARABIC("MMXXVI")2026
ASINASIN(number)
Returns the arcsine of a number, in radians.
=ROUND(ASIN(1),6)1.570796
ASINHASINH(number)
Returns the inverse hyperbolic sine of a number.
=ROUND(ASINH(1),6)0.881374
ATANATAN(number)
Returns the arctangent of a number, in radians.
=ROUND(ATAN(1),6)0.785398
ATAN2ATAN2(x_num, y_num)
Returns the arctangent of the specified x- and y-coordinates, in radians.
=ROUND(ATAN2(-1,0),6)3.141593
ATANHATANH(number)
Returns the inverse hyperbolic tangent of a number.
=ROUND(ATANH(0.5),6)0.549306
BASEBASE(number, radix, [min_length])
Converts a number into a text representation with the given radix (base).
=BASE(255,16,4)00FF
CEILINGCEILING(number, significance)
Rounds a number up, to the nearest multiple of significance.
=CEILING(22,5)25
CEILING.MATHCEILING.MATH(number, [significance])
Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=CEILING.MATH(-4.3)-4
CEILING.PRECISECEILING.PRECISE(number, [significance])
Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=CEILING.PRECISE(-4.3,2)-4
COMBINCOMBIN(number, number_chosen)
Returns the number of combinations for a given number of items.
=COMBIN(8,2)28
COMBINACOMBINA(number, number_chosen)
Returns the number of combinations with repetitions for a given number of items.
=COMBINA(4,3)20
COSCOS(number)
Returns the cosine of an angle.
=COS(0)1
COSHCOSH(number)
Returns the hyperbolic cosine of a number.
=ROUND(COSH(1),6)1.543081
COTCOT(number)
Returns the cotangent of an angle.
=ROUND(COT(PI()/4),6)1
COTHCOTH(number)
Returns the hyperbolic cotangent of a number.
=ROUND(COTH(1),6)1.313035
CSCCSC(number)
Returns the cosecant of an angle.
=ROUND(CSC(PI()/6),6)2
CSCHCSCH(number)
Returns the hyperbolic cosecant of an angle.
=ROUND(CSCH(1),6)0.850918
DECIMALDECIMAL(number, radix)
Converts a text representation of a number in a given base into a decimal number.
=DECIMAL("FF",16)255
DEGREESDEGREES(angle)
Converts radians to degrees.
=DEGREES(PI())180
EVENEVEN(number)
Rounds a positive number up and a negative number down to the nearest even integer.
=EVEN(3.2)4
EXPEXP(number)
Returns e raised to the power of a given number.
=ROUND(EXP(1),6)2.718282
FACTFACT(number)
Returns the factorial of a number, equal to 1*2*3*...*number.
=FACT(5)120
FACTDOUBLEFACTDOUBLE(number)
Returns the double factorial of a number.
=FACTDOUBLE(7)105
FLOORFLOOR(number, significance)
Rounds a number down to the nearest multiple of significance.
=FLOOR(22,5)20
FLOOR.MATHFLOOR.MATH(number, [significance])
Rounds a number down, to the nearest integer or to the nearest multiple of significance.
=FLOOR.MATH(-4.3)-5
FLOOR.PRECISEFLOOR.PRECISE(number, [significance])
Rounds a number down, to the nearest integer or to the nearest multiple of significance.
=FLOOR.PRECISE(-4.3)-5
GCDGCD(number1, [number2], ...)
Returns the greatest common divisor.
=GCD(24,36)12
INTINT(number)
Rounds a number down to the nearest integer.
=INT(-3.7)-4
ISO.CEILINGISO.CEILING(number, [significance])
Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=ISO.CEILING(4.3,2)6
LCMLCM(number1, [number2], ...)
Returns the least common multiple.
=LCM(4,6)12
LNLN(number)
Returns the natural logarithm of a number.
=ROUND(LN(10),6)2.302585
LOGLOG(number, [base])
Returns the logarithm of a number to the base you specify.
=LOG(8,2)3
LOG10LOG10(number)
Returns the base-10 logarithm of a number.
=LOG10(1000)3
MDETERMMDETERM(array)
Returns the matrix determinant of an array.
=MDETERM({3,6,1;1,1,0;3,10,2})1
MINVERSEMINVERSE(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
MMULTMMULT(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
MODMOD(number, divisor)
Returns the remainder after a number is divided by a divisor.
=MOD(-7,3)2
MROUNDMROUND(number, multiple)
Returns a number rounded to the desired multiple.
=MROUND(17,5)15
MULTINOMIALMULTINOMIAL(number1, [number2], ...)
Returns the multinomial of a set of numbers.
=MULTINOMIAL(2,3,4)1260
MUNITMUNIT(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
ODDODD(number)
Rounds a positive number up and a negative number down to the nearest odd integer.
=ODD(2.1)3
PERCENTOFPERCENTOF(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
PIPI()
Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
=ROUND(PI(),5)3.14159
POWERPOWER(number, power)
Returns the result of a number raised to a power.
=POWER(2,10)1024
PRODUCTPRODUCT(number1, [number2], ...)
Multiplies all the numbers given as arguments.
=PRODUCT(2,3,4)24
QUOTIENTQUOTIENT(numerator, denominator)
Returns the integer portion of a division.
=QUOTIENT(-7,2)-3
RADIANSRADIANS(angle)
Converts degrees to radians.
=ROUND(RADIANS(180),5)3.14159
RANDRAND()
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
RANDARRAYRANDARRAY([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
RANDBETWEENRANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify.
=RANDBETWEEN(3,3)3
ROMANROMAN(number, [form])
Converts an Arabic numeral to Roman, as text.
=ROMAN(2026)MMXXVI
ROUNDROUND(number, num_digits)
Rounds a number to a specified number of digits.
=ROUND(2.345,2)2.35
ROUNDDOWNROUNDDOWN(number, num_digits)
Rounds a number down, toward zero.
=ROUNDDOWN(-3.789,1)-3.7
ROUNDUPROUNDUP(number, num_digits)
Rounds a number up, away from zero.
=ROUNDUP(3.211,2)3.22
SECSEC(number)
Returns the secant of an angle.
=ROUND(SEC(PI()/3),6)2
SECHSECH(number)
Returns the hyperbolic secant of an angle.
=ROUND(SECH(1),6)0.648054
SEQUENCESEQUENCE(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
SERIESSUMSERIESSUM(x, n, m, coefficients)
Returns the sum of a power series.
=SERIESSUM(2,1,1,{1,2,3})34
SIGNSIGN(number)
Returns the sign of a number: 1 if positive, zero if zero, -1 if negative.
=SIGN(-12)-1
SINSIN(number)
Returns the sine of an angle.
=ROUND(SIN(PI()/6),6)0.5
SINHSINH(number)
Returns the hyperbolic sine of a number.
=ROUND(SINH(1),6)1.175201
SQRTSQRT(number)
Returns the square root of a number.
=SQRT(144)12
SQRTPISQRTPI(number)
Returns the square root of (number * Pi).
=ROUND(SQRTPI(2),6)2.506628
SUBTOTALSUBTOTAL(function_num, ref1, [ref2], ...)
Returns a subtotal in a list or database, leaving out the rows a filter hides.
=SUBTOTAL(9,C2:C6)610
SUMSUM(number1, [number2], ...)
Adds all the numbers in a range of cells.
=SUM(C2:C6)610
SUMIFSUMIF(range, criteria, [sum_range])
Adds the cells specified by a given condition or criteria.
=SUMIF(B2:B6,"North",C2:C6)270
SUMIFSSUMIFS(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
SUMPRODUCTSUMPRODUCT(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
SUMSQSUMSQ(number1, [number2], ...)
Returns the sum of the squares of the arguments.
=SUMSQ(3,4)25
SUMX2MY2SUMX2MY2(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
SUMX2PY2SUMX2PY2(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
SUMXMY2SUMXMY2(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
TANTAN(number)
Returns the tangent of an angle.
=ROUND(TAN(PI()/4),6)1
TANHTANH(number)
Returns the hyperbolic tangent of a number.
=ROUND(TANH(0.5),6)0.462117
TRUNCTRUNC(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.

FunctionWhat it takes and doesExampleGives
AVEDEVAVEDEV(number1, [number2], ...)
Returns the average of the absolute deviations of data points from their mean.
=AVEDEV(C2:C6)50.4
AVERAGEAVERAGE(number1, [number2], ...)
Returns the average (arithmetic mean) of its arguments.
=AVERAGE(C2:C6)122
AVERAGEAAVERAGEA(value1, [value2], ...)
Returns the average of its arguments, evaluating text as 0 and TRUE as 1.
=AVERAGEA(B2,C2)60
AVERAGEIFAVERAGEIF(range, criteria, [average_range])
Finds the average of the cells specified by a given condition or criteria.
=AVERAGEIF(B2:B6,"South",C2:C6)150
AVERAGEIFSAVERAGEIFS(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.DISTBETA.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.INVBETA.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.DISTBINOM.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.RANGEBINOM.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.INVBINOM.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.DISTCHISQ.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.RTCHISQ.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.INVCHISQ.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.RTCHISQ.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.TESTCHISQ.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.NORMCONFIDENCE.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.TCONFIDENCE.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
CORRELCORREL(array1, array2)
Returns the correlation coefficient between two data sets.
=ROUND(CORREL(C2:C6,E2:E6),6)-0.920575
COUNTCOUNT(value1, [value2], ...)
Counts the number of cells in a range that contain numbers.
=COUNT(A1:C6)5
COUNTACOUNTA(value1, [value2], ...)
Counts the number of cells in a range that are not empty.
=COUNTA(A1:A6)6
COUNTBLANKCOUNTBLANK(range)
Counts the number of empty cells in a specified range of cells.
=COUNTBLANK(H1:H6)6
COUNTIFCOUNTIF(range, criteria)
Counts the number of cells within a range that meet the given condition.
=COUNTIF(C2:C6,">100")3
COUNTIFSCOUNTIFS(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
COUNTUNIQUECOUNTUNIQUE(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.PCOVARIANCE.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.SCOVARIANCE.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
DEVSQDEVSQ(number1, [number2], ...)
Returns the sum of squares of deviations of data points from their sample mean.
=DEVSQ(C2:C6)18880
EXPON.DISTEXPON.DIST(x, lambda, cumulative)
Returns the exponential distribution.
=ROUND(EXPON.DIST(0.2,10,TRUE),6)0.864665
F.DISTF.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.RTF.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.INVF.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.RTF.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.TESTF.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
FISHERFISHER(x)
Returns the Fisher transformation.
=ROUND(FISHER(0.75),6)0.972955
FISHERINVFISHERINV(y)
Returns the inverse of the Fisher transformation.
=ROUND(FISHERINV(0.972955),4)0.75
FORECAST.ETSFORECAST.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.CONFINTFORECAST.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.SEASONALITYFORECAST.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.STATFORECAST.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.LINEARFORECAST.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
FREQUENCYFREQUENCY(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
GAMMAGAMMA(x)
Returns the Gamma function value.
=GAMMA(5)24
GAMMA.DISTGAMMA.DIST(x, alpha, beta, cumulative)
Returns the gamma distribution.
=ROUND(GAMMA.DIST(10,9,2,TRUE),6)0.068094
GAMMA.INVGAMMA.INV(probability, alpha, beta)
Returns the inverse of the gamma cumulative distribution.
=ROUND(GAMMA.INV(0.068094,9,2),3)10
GAMMALNGAMMALN(x)
Returns the natural logarithm of the gamma function.
=ROUND(GAMMALN(4.5),6)2.453737
GAMMALN.PRECISEGAMMALN.PRECISE(x)
Returns the natural logarithm of the gamma function.
=ROUND(GAMMALN.PRECISE(4.5),6)2.453737
GAUSSGAUSS(x)
Returns 0.5 less than the standard normal cumulative distribution.
=ROUND(GAUSS(2),6)0.47725
GEOMEANGEOMEAN(number1, [number2], ...)
Returns the geometric mean of an array or range of positive numeric data.
=ROUND(GEOMEAN(E2:E6),6)3.063887
GROWTHGROWTH(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
HARMEANHARMEAN(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.DISTHYPGEOM.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
INTERCEPTINTERCEPT(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
KURTKURT(number1, [number2], ...)
Returns the kurtosis of a data set.
=ROUND(KURT(C2:C6),6)-0.078911
LARGELARGE(array, k)
Returns the k-th largest value in a data set.
=LARGE(C2:C6,2)150
LINESTLINEST(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.DISTLOGNORM.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.INVLOGNORM.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
MAXMAX(number1, [number2], ...)
Returns the largest value in a set of values.
=MAX(C2:C6)220
MAXAMAXA(value1, [value2], ...)
Returns the largest value in a set of values. Does not ignore logical values and text.
=MAXA(F2,B2)0
MAXIFSMAXIFS(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
MEDIANMEDIAN(number1, [number2], ...)
Returns the median, or the number in the middle of the set of given numbers.
=MEDIAN(C2:C6)120
MINMIN(number1, [number2], ...)
Returns the smallest number in a set of values.
=MIN(C2:C6)40
MINAMINA(value1, [value2], ...)
Returns the smallest value in a set of values. Does not ignore logical values and text.
=MINA(C2:C6,B2)0
MINIFSMINIFS(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
MODEMODE(number1, [number2], ...)
Returns the most frequently occurring value in a range of data.
=MODE(3,5,5,7)5
MODE.MULTMODE.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.SNGLMODE.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.DISTNEGBINOM.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.DISTNORM.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.INVNORM.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.DISTNORM.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.INVNORM.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
PEARSONPEARSON(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
PERCENTILEPERCENTILE(array, k)
Returns the k-th percentile of values in a range.
=PERCENTILE(C2:C6,0.25)80
PERCENTILE.EXCPERCENTILE.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.INCPERCENTILE.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.EXCPERCENTRANK.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.INCPERCENTRANK.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
PERMUTPERMUT(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
PERMUTATIONAPERMUTATIONA(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
PHIPHI(x)
Returns the value of the density function for a standard normal distribution.
=ROUND(PHI(0.75),6)0.301137
POISSON.DISTPOISSON.DIST(x, mean, cumulative)
Returns the Poisson distribution.
=ROUND(POISSON.DIST(2,5,TRUE),6)0.124652
PROBPROB(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
QUARTILEQUARTILE(array, quart)
Returns the quartile of a data set.
=QUARTILE(C2:C6,3)150
QUARTILE.EXCQUARTILE.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.INCQUARTILE.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
RANKRANK(number, ref, [order])
Returns the rank of a number in a list of numbers.
=RANK(150,C2:C6)2
RANK.AVGRANK.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.EQRANK.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
RSQRSQ(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
SKEWSKEW(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.PSKEW.P(number1, [number2], ...)
Returns the skewness of a distribution based on a population.
=ROUND(SKEW.P(C2:C6),6)0.291063
SLOPESLOPE(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
SMALLSMALL(array, k)
Returns the k-th smallest value in a data set.
=SMALL(C2:C6,2)80
STANDARDIZESTANDARDIZE(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
STDEVSTDEV(number1, [number2], ...)
Estimates standard deviation based on a sample.
=ROUND(STDEV(C2:C6),4)68.7023
STDEV.PSTDEV.P(number1, [number2], ...)
Calculates standard deviation based on the entire population given as arguments.
=ROUND(STDEV.P(C2:C6),4)61.4492
STDEV.SSTDEV.S(number1, [number2], ...)
Estimates standard deviation based on a sample.
=ROUND(STDEV.S(C2:C6),4)68.7023
STDEVASTDEVA(value1, [value2], ...)
Estimates standard deviation based on a sample, including logical values and text.
=ROUND(STDEVA(E2:E6,TRUE),6)3.082207
STDEVPSTDEVP(number1, [number2], ...)
Calculates standard deviation based on the entire population given as arguments.
=ROUND(STDEVP(C2:C6),4)61.4492
STDEVPASTDEVPA(value1, [value2], ...)
Calculates standard deviation based on an entire population, including logical values and text.
=ROUND(STDEVPA(E2:E6,TRUE),6)2.813657
STEYXSTEYX(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.DISTT.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.2TT.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.RTT.DIST.RT(x, deg_freedom)
Returns the right-tailed Student’s t-distribution.
=ROUND(T.DIST.RT(1.96,60),6)0.027322
T.INVT.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.2TT.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.TESTT.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
TRENDTREND(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
TRIMMEANTRIMMEAN(array, percent)
Returns the mean of the interior portion of a set of data values.
=ROUND(TRIMMEAN(C2:C6,0.4),4)116.6667
VARVAR(number1, [number2], ...)
Estimates variance based on a sample.
=VAR(C2:C6)4720
VAR.PVAR.P(number1, [number2], ...)
Calculates variance based on the entire population.
=VAR.P(C2:C6)3776
VAR.SVAR.S(number1, [number2], ...)
Estimates variance based on a sample.
=VAR.S(C2:C6)4720
VARAVARA(value1, [value2], ...)
Estimates variance based on a sample, including logical values and text.
=ROUND(VARA(E2:E6,TRUE),6)9.5
VARPVARP(number1, [number2], ...)
Calculates variance based on the entire population.
=VARP(C2:C6)3776
VARPAVARPA(value1, [value2], ...)
Calculates variance based on the entire population, including logical values and text.
=ROUND(VARPA(E2:E6,TRUE),6)7.916667
WEIBULL.DISTWEIBULL.DIST(x, alpha, beta, cumulative)
Returns the Weibull distribution.
=ROUND(WEIBULL.DIST(105,20,100,TRUE),6)0.929581
Z.TESTZ.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.

FunctionWhat it takes and doesExampleGives
ADDRESSADDRESS(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
ARRAYFORMULAARRAYFORMULA(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
CHOOSECHOOSE(index_num, value1, [value2], ...)
Chooses a value from a list of values, based on an index number.
=CHOOSE(2,"red","amber","green")amber
CHOOSECOLSCHOOSECOLS(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
CHOOSEROWSCHOOSEROWS(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
COLUMNCOLUMN([reference])
Returns the column number of a reference.
=COLUMN(D1:D6)4
COLUMNSCOLUMNS(array)
Returns the number of columns in an array or reference.
=COLUMNS(A1:D6)4
DROPDROP(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
EXPANDEXPAND(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
FILTERFILTER(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
FORMULATEXTFORMULATEXT(reference)
Returns a formula as a string.
=FORMULATEXT(G2)=C2*E2
GROUPBYGROUPBY(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
HLOOKUPHLOOKUP(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
HSTACKHSTACK(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
IMAGEIMAGE(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!
INDEXINDEX(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
INDIRECTINDIRECT(ref_text, [a1])
Returns the reference specified by a text string.
=INDIRECT("C"&4)150
LOOKUPLOOKUP(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
MATCHMATCH(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
OFFSETOFFSET(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
PIVOTBYPIVOTBY(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
ROWROW([reference])
Returns the row number of a reference.
=ROW(C4)4
ROWSROWS(array)
Returns the number of rows in a reference or an array.
=ROWS(A2:C6)5
SORTSORT(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
SORTBYSORTBY(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
SPARKLINESPARKLINE(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)
TAKETAKE(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
TOCOLTOCOL(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
TOROWTOROW(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
TRANSPOSETRANSPOSE(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
TRIMRANGETRIMRANGE(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
UNIQUEUNIQUE(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
VLOOKUPVLOOKUP(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
VSTACKVSTACK(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
WRAPCOLSWRAPCOLS(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
WRAPROWSWRAPROWS(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
XLOOKUPXLOOKUP(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
XMATCHXMATCH(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.

FunctionWhat it takes and doesExampleGives
ANDAND(logical1, [logical2], ...)
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.
=AND(C2>100,B2="North")TRUE
BYCOLBYCOL(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
BYROWBYROW(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
FALSEFALSE()
Returns the logical value FALSE.
=FALSE()FALSE
IFIF(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
IFERRORIFERROR(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
IFNAIFNA(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
IFSIFS(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
LAMBDALAMBDA([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
LETLET(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
MAKEARRAYMAKEARRAY(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
MAPMAP(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
NOTNOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE.
=NOT(C5>100)TRUE
OROR(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
REDUCEREDUCE([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
SCANSCAN([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
SWITCHSWITCH(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
TRUETRUE()
Returns the logical value TRUE.
=TRUE()TRUE
XORXOR(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.

FunctionWhat it takes and doesExampleGives
ARRAYTOTEXTARRAYTOTEXT(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"}
CHARCHAR(number)
Returns the character specified by the code number.
=CHAR(65)A
CLEANCLEAN(text)
Removes all nonprintable characters from text.
=LEN(CLEAN("a"&CHAR(9)&"b"))2
CODECODE(text)
Returns a numeric code for the first character in a text string.
=CODE("A")65
CONCATCONCAT(text1, [text2], ...)
Joins a list or range of text strings.
=CONCAT(A2,"-",B2)Pens-North
CONCATENATECONCATENATE(text1, [text2], ...)
Joins several text strings into one text string.
=CONCATENATE("Q",1," ",2026)Q1 2026
ENCODEURLENCODEURL(text)
Returns a URL-encoded string.
=ENCODEURL("a b&c")a%20b%26c
EXACTEXACT(text1, text2)
Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive.
=EXACT("Ink","ink")FALSE
FINDFIND(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
FIXEDFIXED(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
LEFTLEFT(text, [num_chars])
Returns the specified number of characters from the start of a text string.
=LEFT("Stapler",4)Stap
LENLEN(text)
Returns the number of characters in a text string.
=LEN("Folders")7
LOWERLOWER(text)
Converts all letters in a text string to lowercase.
=LOWER("NORTH")north
MIDMID(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
NUMBERVALUENUMBERVALUE(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
PROPERPROPER(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
REGEXEXTRACTREGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Extracts text from a string based on a supplied regular expression.
=REGEXEXTRACT("Order 4521 shipped","[0-9]+")4521
REGEXMATCHREGEXMATCH(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
REGEXREPLACEREGEXREPLACE(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
REGEXTESTREGEXTEST(text, pattern, [case_sensitivity])
Determines whether any part of text matches the pattern.
=REGEXTEST(A3,"^P")TRUE
REPLACEREPLACE(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
REPTREPT(text, number_times)
Repeats text a given number of times.
=REPT("-",5)-----
RIGHTRIGHT(text, [num_chars])
Returns the specified number of characters from the end of a text string.
=RIGHT("INV-1042",4)1042
SPLITSPLIT(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
SUBSTITUTESUBSTITUTE(text, old_text, new_text, [instance_num])
Replaces existing text with new text in a text string.
=SUBSTITUTE("07700 900 123"," ","")07700900123
TT(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
TEXTTEXT(value, format_text)
Converts a value to text in a specific number format.
=TEXT(D5,"dddd d mmmm yyyy")Thursday 5 March 2026
TEXTAFTERTEXTAFTER(text, delimiter, [instance_num])
Returns the text that comes after a delimiter.
=TEXTAFTER("invoice-2026-0042","-")2026-0042
TEXTBEFORETEXTBEFORE(text, delimiter, [instance_num])
Returns the text that comes before a delimiter.
=TEXTBEFORE("invoice-2026-0042","-",2)invoice-2026
TEXTJOINTEXTJOIN(delimiter, ignore_empty, text1, ...)
Joins a list or range of text strings using a delimiter.
=TEXTJOIN(", ",TRUE,A2:A4)Pens, Paper, Ink
TEXTSPLITTEXTSPLIT(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
TRIMTRIM(text)
Removes all spaces from a text string except for single spaces between words.
=TRIM(" two spaces ")two spaces
UNICHARUNICHAR(number)
Returns the Unicode character referenced by the given numeric value.
=UNICHAR(8364)€
UNICODEUNICODE(text)
Returns the number (code point) corresponding to the first character of the text.
=UNICODE("€")8364
UPPERUPPER(text)
Converts a text string to all uppercase letters.
=UPPER("south")SOUTH
VALUEVALUE(text)
Converts a text string that represents a number to a number.
=VALUE("1,250.50")1250.5
VALUETOTEXTVALUETOTEXT(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.

FunctionWhat it takes and doesExampleGives
DATEDATE(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
DATEDIFDATEDIF(start_date, end_date, unit)
Returns the number of days, months or years between two dates.
=DATEDIF(D2,D6,"M")2
DATEVALUEDATEVALUE(date_text)
Converts a date in the form of text to a number that represents the date.
=DATEVALUE("2026-03-09")46090
DAYDAY(serial_number)
Returns the day of the month, a number from 1 to 31.
=DAY(D6)31
DAYSDAYS(end_date, start_date)
Returns the number of days between the two dates.
=DAYS(D6,D2)75
DAYS360DAYS360(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
EDATEEDATE(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
EOMONTHEOMONTH(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
HOURHOUR(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
ISOWEEKNUMISOWEEKNUM(date)
Returns the ISO week number in the year for a given date.
=ISOWEEKNUM("2027-01-01")53
MINUTEMINUTE(serial_number)
Returns the minute, a number from 0 to 59.
=MINUTE(875/1440)35
MONTHMONTH(serial_number)
Returns the month, a number from 1 (January) to 12 (December).
=MONTH(D3)2
NETWORKDAYSNETWORKDAYS(start_date, end_date, [holidays])
Returns the number of whole workdays between two dates.
=NETWORKDAYS("2026-09-28","2026-10-09")10
NETWORKDAYS.INTLNETWORKDAYS.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
NOWNOW()
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())>=2026TRUE
SECONDSECOND(serial_number)
Returns the second, a number from 0 to 59.
=SECOND(43209/86400)9
TIMETIME(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
TIMEVALUETIMEVALUE(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
TODAYTODAY()
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())>=2026TRUE
WEEKDAYWEEKDAY(serial_number, [return_type])
Returns a number from 1 to 7 identifying the day of the week of a date.
=WEEKDAY(D5,2)4
WEEKNUMWEEKNUM(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
WORKDAYWORKDAY(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.INTLWORKDAY.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
YEARYEAR(serial_number)
Returns the year of a date, an integer in the range 1900 - 9999.
=YEAR(D2)2026
YEARFRACYEARFRAC(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.

FunctionWhat it takes and doesExampleGives
ACCRINTACCRINT(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
ACCRINTMACCRINTM(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
COUPDAYBSCOUPDAYBS(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
COUPDAYSCOUPDAYS(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
COUPDAYSNCCOUPDAYSNC(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
COUPNCDCOUPNCD(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
COUPNUMCOUPNUM(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
COUPPCDCOUPPCD(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
CUMIPMTCUMIPMT(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
CUMPRINCCUMPRINC(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
DBDB(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
DDBDDB(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
DISCDISC(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
DOLLARDEDOLLARDE(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
DOLLARFRDOLLARFR(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
DURATIONDURATION(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
EFFECTEFFECT(nominal_rate, npery)
Returns the effective annual interest rate.
=ROUND(EFFECT(0.0525,4),6)0.053543
FVFV(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
FVSCHEDULEFVSCHEDULE(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
INTRATEINTRATE(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
IPMTIPMT(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
IRRIRR(values, [guess])
Returns the internal rate of return for a series of cash flows.
=ROUND(IRR(F2:F5),4)0.097
ISPMTISPMT(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
MDURATIONMDURATION(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
MIRRMIRR(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
NOMINALNOMINAL(effect_rate, npery)
Returns the annual nominal interest rate.
=ROUND(NOMINAL(0.053543,4),4)0.0525
NPERNPER(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
NPVNPV(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
ODDFPRICEODDFPRICE(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
ODDFYIELDODDFYIELD(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
ODDLPRICEODDLPRICE(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
ODDLYIELDODDLYIELD(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
PDURATIONPDURATION(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
PMTPMT(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
PPMTPPMT(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
PRICEPRICE(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
PRICEDISCPRICEDISC(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
PRICEMATPRICEMAT(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
PVPV(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
RATERATE(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
RECEIVEDRECEIVED(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
RRIRRI(nper, pv, fv)
Returns an equivalent interest rate for the growth of an investment.
=ROUND(RRI(96,10000,11000),6)0.000993
SLNSLN(cost, salvage, life)
Returns the straight-line depreciation of an asset for one period.
=SLN(30000,7500,10)2250
SYDSYD(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
TBILLEQTBILLEQ(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
TBILLPRICETBILLPRICE(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
TBILLYIELDTBILLYIELD(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
XIRRXIRR(values, dates, [guess])
Returns the internal rate of return for a schedule of cash flows.
=ROUND(XIRR(F2:F5,D2:D5),4)6.2777
XNPVXNPV(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
YIELDYIELD(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
YIELDDISCYIELDDISC(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
YIELDMATYIELDMAT(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.

FunctionWhat it takes and doesExampleGives
ERROR.TYPEERROR.TYPE(error_val)
Returns a number matching an error value.
=ERROR.TYPE(1/0)2
ISBLANKISBLANK(value)
Checks whether a reference is to an empty cell, and returns TRUE or FALSE.
=ISBLANK(H2)TRUE
ISERRISERR(value)
Checks whether a value is an error other than #N/A, and returns TRUE or FALSE.
=ISERR(NA())FALSE
ISERRORISERROR(value)
Checks whether a value is an error, and returns TRUE or FALSE.
=ISERROR(1/0)TRUE
ISEVENISEVEN(number)
Returns TRUE if the number is even.
=ISEVEN(C2)TRUE
ISFORMULAISFORMULA(reference)
Checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE.
=ISFORMULA(G2)TRUE
ISLOGICALISLOGICAL(value)
Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE.
=ISLOGICAL(C2>100)TRUE
ISNAISNA(value)
Checks whether a value is #N/A, and returns TRUE or FALSE.
=ISNA(NA())TRUE
ISNONTEXTISNONTEXT(value)
Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE.
=ISNONTEXT(A2)FALSE
ISNUMBERISNUMBER(value)
Checks whether a value is a number, and returns TRUE or FALSE.
=ISNUMBER(C2)TRUE
ISODDISODD(number)
Returns TRUE if the number is odd.
=ISODD(3)TRUE
ISREFISREF(value)
Checks whether a value is a reference, and returns TRUE or FALSE.
=ISREF(C2)TRUE
ISTEXTISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE.
=ISTEXT(A2)TRUE
NN(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
NANA()
Returns the error value #N/A (value not available).
=NA()#N/A
TYPETYPE(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”.

FunctionWhat it takes and doesExampleGives
BIN2DECBIN2DEC(number)
Converts a binary number to decimal.
=BIN2DEC("1100100")100
BIN2HEXBIN2HEX(number, [places])
Converts a binary number to hexadecimal.
=BIN2HEX("11111011",4)00FB
BIN2OCTBIN2OCT(number, [places])
Converts a binary number to octal.
=BIN2OCT("1001",3)011
BITANDBITAND(number1, number2)
Returns a bitwise And of two numbers.
=BITAND(13,25)9
BITLSHIFTBITLSHIFT(number, shift_amount)
Returns a number shifted left by shift_amount bits.
=BITLSHIFT(4,2)16
BITORBITOR(number1, number2)
Returns a bitwise Or of two numbers.
=BITOR(23,10)31
BITRSHIFTBITRSHIFT(number, shift_amount)
Returns a number shifted right by shift_amount bits.
=BITRSHIFT(13,2)3
BITXORBITXOR(number1, number2)
Returns a bitwise Exclusive Or of two numbers.
=BITXOR(5,3)6
COMPLEXCOMPLEX(real_num, i_num, [suffix])
Converts real and imaginary coefficients into a complex number.
=COMPLEX(3,4)3+4i
CONVERTCONVERT(number, from_unit, to_unit)
Converts a number from one measurement system to another.
=ROUND(CONVERT(26.2,"mi","km"),2)42.16
DEC2BINDEC2BIN(number, [places])
Converts a decimal number to binary.
=DEC2BIN(9,4)1001
DEC2HEXDEC2HEX(number, [places])
Converts a decimal number to hexadecimal.
=DEC2HEX(255)FF
DEC2OCTDEC2OCT(number, [places])
Converts a decimal number to octal.
=DEC2OCT(58,3)072
DELTADELTA(number1, [number2])
Tests whether two numbers are equal.
=DELTA(5,4)0
ERFERF(lower_limit, [upper_limit])
Returns the error function.
=ROUND(ERF(0.745),6)0.707929
ERF.PRECISEERF.PRECISE(x)
Returns the error function.
=ROUND(ERF.PRECISE(1),6)0.842701
ERFCERFC(x)
Returns the complementary error function.
=ROUND(ERFC(1),6)0.157299
ERFC.PRECISEERFC.PRECISE(x)
Returns the complementary error function.
=ROUND(ERFC.PRECISE(1),6)0.157299
GESTEPGESTEP(number, [step])
Tests whether a number is greater than a threshold value.
=GESTEP(5,4)1
HEX2BINHEX2BIN(number, [places])
Converts a hexadecimal number to binary.
=HEX2BIN("F",8)00001111
HEX2DECHEX2DEC(number)
Converts a hexadecimal number to decimal.
=HEX2DEC("A5")165
HEX2OCTHEX2OCT(number, [places])
Converts a hexadecimal number to octal.
=HEX2OCT("F",3)017
IMABSIMABS(inumber)
Returns the absolute value (modulus) of a complex number.
=IMABS("3+4i")5
IMAGINARYIMAGINARY(inumber)
Returns the imaginary coefficient of a complex number.
=IMAGINARY("3+4i")4
IMARGUMENTIMARGUMENT(inumber)
Returns the argument theta, an angle expressed in radians.
=ROUND(IMARGUMENT("3+4i"),6)0.927295
IMCONJUGATEIMCONJUGATE(inumber)
Returns the complex conjugate of a complex number.
=IMCONJUGATE("3+4i")3-4i
IMCOSIMCOS(inumber)
Returns the cosine of a complex number.
=IMCOS("1+i")0.833730025131149-0.988897705762865i
IMCOSHIMCOSH(inumber)
Returns the hyperbolic cosine of a complex number.
=IMCOSH("4+3i")-27.0349456030742+3.85115333481178i
IMCOTIMCOT(inumber)
Returns the cotangent of a complex number.
=ROUND(IMREAL(IMCOT("4+3i")),6)0.004901
IMCSCIMCSC(inumber)
Returns the cosecant of a complex number.
=IMCSC("4+3i")-0.0754898329158637+0.0648774713706355i
IMCSCHIMCSCH(inumber)
Returns the hyperbolic cosecant of a complex number.
=ROUND(IMREAL(IMCSCH("4+3i")),6)-0.036276
IMDIVIMDIV(inumber1, inumber2)
Returns the quotient of two complex numbers.
=IMDIV("-238+240i","10+24i")5+12i
IMEXPIMEXP(inumber)
Returns the exponential of a complex number.
=IMEXP("1+i")1.46869393991589+2.28735528717884i
IMLNIMLN(inumber)
Returns the natural logarithm of a complex number.
=IMLN("3+4i")1.6094379124341+0.927295218001612i
IMLOG10IMLOG10(inumber)
Returns the base-10 logarithm of a complex number.
=IMLOG10("3+4i")0.698970004336019+0.402719196273373i
IMLOG2IMLOG2(inumber)
Returns the base-2 logarithm of a complex number.
=IMLOG2("3+4i")2.32192809488736+1.33780421245098i
IMPOWERIMPOWER(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
IMPRODUCTIMPRODUCT(inumber1, [inumber2], ...)
Returns the product of complex numbers.
=IMPRODUCT("3+4i","5-3i")27+11i
IMREALIMREAL(inumber)
Returns the real coefficient of a complex number.
=IMREAL("6-9i")6
IMSECIMSEC(inumber)
Returns the secant of a complex number.
=IMSEC("4+3i")-0.0652940278579471-0.0752249603027732i
IMSECHIMSECH(inumber)
Returns the hyperbolic secant of a complex number.
=IMSECH("4+3i")-0.0362534969158689-0.00516434460775318i
IMSINIMSIN(inumber)
Returns the sine of a complex number.
=IMSIN("4+3i")-7.61923172032141-6.548120040911i
IMSINHIMSINH(inumber)
Returns the hyperbolic sine of a complex number.
=IMSINH("4+3i")-27.0168132580039+3.85373803791938i
IMSQRTIMSQRT(inumber)
Returns the square root of a complex number.
=IMSQRT("1+i")1.09868411346781+0.455089860562227i
IMSUBIMSUB(inumber1, inumber2)
Returns the difference between two complex numbers.
=IMSUB("13+4i","5+3i")8+i
IMSUMIMSUM(inumber1, [inumber2], ...)
Returns the sum of complex numbers.
=IMSUM("3+4i","5-3i")8+i
IMTANIMTAN(inumber)
Returns the tangent of a complex number.
=IMTAN("4+3i")0.00490825806749606+1.00070953606723i
OCT2BINOCT2BIN(number, [places])
Converts an octal number to binary.
=OCT2BIN("3",3)011
OCT2DECOCT2DEC(number)
Converts an octal number to decimal.
=OCT2DEC("54")44
OCT2HEXOCT2HEX(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.

FunctionWhat it takes and doesExampleGives
DAVERAGEDAVERAGE(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
DCOUNTDCOUNT(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
DCOUNTADCOUNTA(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
DGETDGET(database, field, criteria)
Extracts from a database the single record that matches the conditions you specify.
=DGET(A1:F6,"Amount",A1:A2)120
DMAXDMAX(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
DMINDMIN(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
DPRODUCTDPRODUCT(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
DSTDEVDSTDEV(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
DSTDEVPDSTDEVP(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
DSUMDSUM(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
DVARDVAR(database, field, criteria)
Estimates variance based on a sample from the selected database entries.
=ROUND(DVAR(A1:F6,"Amount",B1:B3),2)3491.67
DVARPDVARP(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.

FunctionWhat it takes and doesExampleGives
BETADISTBETADIST(x, alpha, beta, [A], [B])
Returns the cumulative beta probability density function.
=ROUND(BETADIST(2,8,10,1,3),6)0.685471
BETAINVBETAINV(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
BINOMDISTBINOMDIST(number_s, trials, probability_s, cumulative)
Returns the individual term binomial distribution probability.
=ROUND(BINOMDIST(6,10,0.5,TRUE),6)0.828125
CHIDISTCHIDIST(x, deg_freedom)
Returns the right-tailed probability of the chi-squared distribution.
=ROUND(CHIDIST(18.307,10),6)0.050001
CHIINVCHIINV(probability, deg_freedom)
Returns the inverse of the right-tailed probability of the chi-squared distribution.
=ROUND(CHIINV(0.05,10),4)18.307
CHITESTCHITEST(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
CONFIDENCECONFIDENCE(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
COVARCOVAR(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
CRITBINOMCRITBINOM(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
EXPONDISTEXPONDIST(x, lambda, cumulative)
Returns the exponential distribution.
=ROUND(EXPONDIST(0.2,10,FALSE),6)1.353353
FDISTFDIST(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
FINVFINV(probability, deg_freedom1, deg_freedom2)
Returns the inverse of the (right-tailed) F probability distribution.
=ROUND(FINV(0.01,6,4),4)15.2069
FORECASTFORECAST(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
FTESTFTEST(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
GAMMADISTGAMMADIST(x, alpha, beta, cumulative)
Returns the gamma distribution.
=ROUND(GAMMADIST(10,9,2,FALSE),6)0.032639
GAMMAINVGAMMAINV(probability, alpha, beta)
Returns the inverse of the gamma cumulative distribution.
=ROUND(GAMMAINV(0.068094,9,2),3)10
HYPGEOMDISTHYPGEOMDIST(sample_s, number_sample, population_s, number_pop)
Returns the hypergeometric distribution.
=ROUND(HYPGEOMDIST(1,4,8,20),6)0.363261
LOGINVLOGINV(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
LOGNORMDISTLOGNORMDIST(x, mean, standard_dev)
Returns the cumulative lognormal distribution of x.
=ROUND(LOGNORMDIST(4,3.5,1.2),6)0.039084
NEGBINOMDISTNEGBINOMDIST(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
NORMDISTNORMDIST(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
NORMINVNORMINV(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
NORMSDISTNORMSDIST(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
NORMSINVNORMSINV(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
PERCENTRANKPERCENTRANK(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
POISSONPOISSON(x, mean, cumulative)
Returns the Poisson distribution.
=ROUND(POISSON(2,5,FALSE),6)0.084224
TDISTTDIST(x, deg_freedom, tails)
Returns the Student’s t-distribution.
=ROUND(TDIST(1.96,60,2),6)0.054645
TINVTINV(probability, deg_freedom)
Returns the two-tailed inverse of the Student’s t-distribution.
=ROUND(TINV(0.05,60),6)2.000298
TTESTTTEST(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
WEIBULLWEIBULL(x, alpha, beta, cumulative)
Returns the Weibull distribution.
=ROUND(WEIBULL(105,20,100,FALSE),6)0.035589
ZTESTZTEST(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.