All 510 formulas, explained

The absolute (unsigned) value of a number.

Accrued interest for a security that pays periodic interest.

Accrued interest for a security that pays at maturity.

Arccosine — the angle whose cosine is the number.

Inverse hyperbolic cosine.

Arccotangent of a number.

Inverse hyperbolic cotangent.

Build a cell address as text from row and column numbers.

Like SUBTOTAL but can also ignore error cells and do LARGE/SMALL/PERCENTILE.

Depreciation for an accounting period (French, with coefficient).

Depreciation for an accounting period (French, linear).

TRUE only if every condition is TRUE.

Convert Roman numerals back to a number.

The number of areas in a reference.

Turn an array/range into a single text string.

Convert full-width (double-byte) letters to half-width.

Arcsine — the angle whose sine is the number.

Inverse hyperbolic sine.

Arctangent — the angle whose tangent is the number.

Arctangent from x and y coordinates (full circle).

Inverse hyperbolic tangent.

Average of the absolute deviations from the mean.

The arithmetic mean of numbers (blanks and text ignored).

AVERAGE that counts text as 0 and TRUE/FALSE as 1/0.

Average the cells that meet one condition.

Average cells meeting several conditions.

Spell a number as Thai baht text.

Convert a number to a text representation in a given base.

The modified Bessel function Iₙ(x).

The Bessel function Jₙ(x).

The modified Bessel function Kₙ(x).

The Bessel function Yₙ(x).

Beta distribution for a value.

Inverse of the beta cumulative distribution.

Pre-2010 name for BETA.DIST.

Pre-2010 name for BETA.INV.

Convert binary text to a decimal number.

Convert binary text to hexadecimal text.

Convert binary text to octal text.

Binomial probability of k successes in n trials.

Probability of a range of binomial outcomes.

Smallest k where the binomial CDF ≥ a criterion.

Pre-2010 name for BINOM.DIST.

Bitwise AND of two numbers.

Shift a number left by a number of bits.

Bitwise OR of two numbers.

Shift a number right by a number of bits.

Bitwise exclusive OR of two numbers.

Apply a LAMBDA to each column; one result per column.

Apply a LAMBDA to each row and return one result per row.

Round up to the nearest multiple of significance.

Round up to a multiple, with a mode for negatives.

Round up to a multiple, ignoring the sign.

Information about a cell's formatting, location or contents.

The character for a code number (e.g. 10 = line break).

Pre-2010 name for CHISQ.DIST.RT.

Pre-2010 name for CHISQ.INV.RT.

Chi-squared distribution (left tail).

Chi-squared distribution (right tail).

Inverse of the left-tail chi-squared distribution.

Inverse of the right-tail chi-squared distribution.

Chi-squared test of independence.

Pre-2010 name for CHISQ.TEST.

Pick the Nth item from a list of values.

Return chosen columns from an array in any order.

Return chosen rows from an array in any order.

Strip non-printing control characters (e.g. line breaks from imports).

The numeric code of the first character of a string.

The column number of a reference (or of the formula cell).

Count the columns in a range or array.

Number of combinations of n items taken k at a time.

Combinations with repetitions allowed.

Build a complex number from real and imaginary parts.

Join text from ranges or values into one string.

Legacy function to join several text values (use CONCAT / TEXTJOIN).

Pre-2010 name for CONFIDENCE.NORM.

Confidence interval half-width (normal).

Confidence interval half-width (Student's t).

Convert a number between measurement units.

The correlation coefficient between two data sets (−1 to 1).

Cosine of an angle given in radians.

Hyperbolic cosine of a number.

Cotangent of an angle in radians.

Hyperbolic cotangent.

Count how many cells contain numbers.

Count cells that are not empty (numbers, text, errors, anything).

Count empty cells in a range.

Count the cells that meet one condition.

Count cells meeting several conditions.

Days from the coupon period start to settlement.

Days in the coupon period containing settlement.

Days from settlement to the next coupon date.

The next coupon date after settlement.

Number of coupons payable between settlement and maturity.

The previous coupon date before settlement.

Pre-2010 name for COVARIANCE.P.

Population covariance of two data sets.

Sample covariance of two data sets.

Pre-2010 name for BINOM.INV.

Cosecant of an angle in radians.

Hyperbolic cosecant.

A key performance indicator property from the cube.

A member or tuple from the cube.

A property value of a cube member.

The nth member of a cube set.

A calculated set of members from the cube.

The number of items in a cube set.

An aggregated value from the OLAP / data model cube.

Total interest paid between two loan periods.

Total principal repaid between two periods.

Build a date from year, month and day numbers.

Whole years / months / days between two dates.

Hidden helper that formats a serial as a date string.

Convert a date written as text into a real date.

Average a field of records matching the criteria.

The day-of-month of a date as 1–31.

The number of days between two dates.

Days between dates on a 360-day (12×30) year.

Fixed-declining-balance depreciation.

Convert half-width letters to full-width (double-byte).

Count records with a numeric field that match the criteria.

Count non-blank field values in matching records.

Double-declining-balance depreciation.

Convert a decimal number to binary text.

Convert a decimal number to hexadecimal text.

Convert a decimal number to octal text.

Convert a text number in a given base to base 10.

Convert radians to degrees.

1 if two numbers are equal, else 0 (Kronecker delta).

Sum of the squared deviations from the mean.

Return the single value from a field where exactly one record matches.

The discount rate for a security.

Largest value of a field among matching records.

Smallest value of a field among matching records.

Format a number as currency text.

Convert a fractional-notation price to decimal.

Convert a decimal price to fractional notation.

Multiply a field across matching records.

Remove rows/columns from the start or end of an array.

Sample standard deviation of matching records.

Population standard deviation of matching records.

Sum a field of the records that match a criteria range.

Macaulay duration of a bond.

Sample variance of matching records.

Population variance of matching records.

The same day of the month, N months away.

Effective annual rate from a nominal rate.

URL-encode a string.

The last day of the month, N months from a date.

The error function integrated between limits.

The error function between 0 and a limit.

The complementary error function.

The complementary error function (precise).

A number identifying which error a cell has.

Round up to the nearest even integer.

TRUE only if two strings match exactly, including case.

e raised to a power.

Pad an array to a given size with a fill value.

Exponential distribution.

Pre-2010 name for EXPON.DIST.

F probability distribution (left tail).

F probability distribution (right tail).

Inverse of the left-tail F distribution.

Inverse of the right-tail F distribution.

Two-tailed F-test comparing two variances.

Factorial of a number (n!).

The double factorial of a number (n!!).

The logical value FALSE.

Pre-2010 name for F.DIST.RT.

Return only the rows of a range that meet a condition — results spill.

Return data from XML using an XPath expression.

Position of one string inside another — case-sensitive, no wildcards.

FIND counted in bytes.

Pre-2010 name for F.INV.RT.

Fisher transformation of a correlation.

Inverse of the Fisher transformation.

Format a number as text with fixed decimals and commas.

Round down to the nearest multiple of significance.

Round down to a multiple, with a mode for negatives.

Round down to a multiple, ignoring the sign.

Pre-2016 name for FORECAST.LINEAR.

Forecast a future value using exponential smoothing.

Confidence interval for an ETS forecast.

Length of the repeating pattern ETS detected.

A statistic from the ETS forecasting model.

Predict a y-value for a new x using linear regression.

Show the formula in a cell as text — useful for documentation.

Count how many values fall into each bin (spills).

Pre-2010 name for F.TEST.

The future value of an investment with regular deposits.

Future value with a series of varying interest rates.

The gamma function value Γ(x).

Gamma distribution for a value.

Inverse of the gamma cumulative distribution.

Pre-2010 name for GAMMA.DIST.

Pre-2010 name for GAMMA.INV.

Natural log of the gamma function.

Natural log of the gamma function (precise).

Probability a value is within z SDs of the mean.

Greatest common divisor of the numbers.

Geometric mean — the right average for growth rates.

1 if a number is ≥ a threshold, else 0.

Pull a specific value out of a PivotTable.

Aggregate a table by one or more grouping columns.

Predicted exponential growth values (spills).

Harmonic mean — the right average for rates like speed.

Convert hexadecimal text to binary text.

Convert hexadecimal text to a decimal number.

Convert hexadecimal text to octal text.

Like VLOOKUP but searches the first row and returns from a row below.

The hour of a time value (0–23).

Join arrays side by side into one wider array.

Create a clickable link from a formula.

Hypergeometric distribution (sampling without replacement).

Pre-2010 name for HYPGEOM.DIST.

Return one value if a test is TRUE, another if FALSE.

Return a fallback when a formula errors, otherwise the result.

Return a fallback only for #N/A, letting other errors through.

Check several conditions in order and return the first match.

The absolute value (modulus) of a complex number.

The imaginary coefficient of a complex number.

The argument θ of a complex number (radians).

The complex conjugate.

The cosine of a complex number.

The hyperbolic cosine of a complex number.

The cotangent of a complex number.

The cosecant of a complex number.

The hyperbolic cosecant of a complex number.

The quotient of two complex numbers.

The exponential of a complex number.

The natural log of a complex number.

The base-10 log of a complex number.

The base-2 log of a complex number.

A complex number raised to a power.

The product of complex numbers.

The real coefficient of a complex number.

The secant of a complex number.

The hyperbolic secant of a complex number.

The sine of a complex number.

The hyperbolic sine of a complex number.

The square root of a complex number.

The difference of two complex numbers.

The sum of complex numbers.

The tangent of a complex number.

Return the value at a given row/column position in a range.

Turn a text string into a cell/range reference.

Information about the operating environment.

Round down to the nearest integer.

Where the regression line crosses the y-axis.

Interest rate for a fully invested security.

The interest portion of a specific loan payment.

The internal rate of return of periodic cash flows.

TRUE if a cell is truly empty.

TRUE for any error except #N/A.

TRUE if the value is any error.

TRUE if a number is even.

TRUE if a cell contains a formula.

TRUE if the value is TRUE or FALSE.

TRUE only for the #N/A error.

TRUE if the value is not text (blank counts as not text).

TRUE if the value is a number.

ISO version of CEILING.PRECISE.

TRUE if a number is odd.

TRUE if a LAMBDA argument was left out.

The ISO 8601 week number of a date.

Interest paid during a period of a straight-line loan.

TRUE if the value is a reference.

TRUE if the value is text.

Convert half-width characters to full-width (Japanese).

Kurtosis of a data set.

Define a reusable custom function from a formula, no VBA.

The k-th largest value (1 = max, 2 = second largest…).

Least common multiple of the numbers.

The first N characters of a string.

LEFT counted in bytes (for double-byte character sets).

Number of characters in a string.

LEN counted in bytes.

Name intermediate results inside a formula — readable and faster.

Least-squares regression coefficients (array).

Natural logarithm (base e).

Logarithm to a given base (default 10).

Base-10 logarithm.

Exponential regression coefficients (array).

Pre-2010 name for LOGNORM.INV.

Lognormal distribution for a value.

Inverse of the lognormal distribution.

Pre-2010 name for LOGNORM.DIST.

Legacy approximate-match lookup over a sorted vector.

Convert text to lowercase.

Build an array by calling a LAMBDA for each cell.

Transform every element of one or more arrays with a LAMBDA.

Return the position of a value within a row or column.

The largest number in a set.

MAX that also counts text (as 0) and logicals.

The largest value among cells meeting conditions.

The matrix determinant of an array.

Modified Macaulay duration of a bond.

The middle value — half the data is above it, half below.

A run of characters from the middle of a string.

MID counted in bytes.

The smallest number in a set.

MIN that also counts text (as 0) and logicals.

The smallest value among cells meeting conditions.

The minute of a time value (0–59).

The inverse matrix of an array.

Modified internal rate of return (separate finance / reinvest rates).

The matrix product of two arrays.

The remainder after division.

Pre-2010 name for MODE.SNGL.

All values tied for most frequent (spills).

The value that appears most often.

The month of a date as 1–12.

Round to the nearest multiple.

Ratio of the factorial of a sum to the product of factorials.

The identity matrix of a given dimension.

Convert a value to a number (dates stay, text → 0).

Return the #N/A error on purpose (e.g. to leave a gap in a chart).

Negative binomial distribution.

Pre-2010 name for NEGBINOM.DIST.

Count working days between two dates, skipping weekends and holidays.

Working days between dates with a custom weekend.

Nominal annual rate from an effective rate.

Normal distribution for a value (PDF or CDF).

Inverse of the normal cumulative distribution.

Standard normal distribution.

Inverse of the standard normal distribution.

Pre-2010 name for NORM.DIST.

Pre-2010 name for NORM.INV.

Pre-2010 name for NORM.S.DIST.

Pre-2010 name for NORM.S.INV.

Reverse TRUE ↔ FALSE.

The current date and time.

How many periods to pay off a loan or reach a savings goal.

Net present value of a series of evenly-spaced cash flows.

Convert text to a number with explicit decimal / thousands separators.

Convert octal text to binary text.

Convert octal text to a decimal number.

Convert octal text to hexadecimal text.

Round up to the nearest odd integer.

Price of a security with an odd first period.

Yield of a security with an odd first period.

Price of a security with an odd last period.

Yield of a security with an odd last period.

Return a range a given number of rows/columns from a starting cell.

TRUE if at least one condition is TRUE.

Periods for an investment to reach a value.

Pearson product-moment correlation coefficient.

Pre-2010 name for PERCENTILE.INC.

Percentile, exclusive of 0 and 1.

The value at a given percentile (0–1 inclusive).

Pre-2010 name for PERCENTRANK.INC.

Percentile rank, exclusive.

The percentile rank of a value within a set.

Number of ordered arrangements (permutations).

Permutations with repetitions allowed.

Density of the standard normal distribution.

Extract the furigana (phonetic) characters from text.

The constant π (3.14159…).

Cross-tabulate a table by row and column fields.

The fixed periodic payment for a loan (EMI).

Pre-2010 name for POISSON.DIST.

Poisson distribution for a number of events.

Raise a number to a power (same as the ^ operator).

The principal portion of a specific loan payment.

Price per ₹100 face value of a periodic-interest bond.

Price per ₹100 face value of a discounted security.

Price per ₹100 of a security that pays interest at maturity.

Probability that values are within a range.

Multiply all the numbers given.

Capitalise the first letter of each word.

The present value of a future sum or an annuity.

Pre-2010 name for QUARTILE.INC.

Quartile based on the exclusive percentile.

The value at quartile 0–4 of a data set.

The integer part of a division (no remainder).

Convert degrees to radians.

A random decimal ≥ 0 and < 1, recalculated on every change.

A spilled array of random numbers.

A random whole number between two bounds (inclusive).

Pre-2010 name; use RANK.EQ or RANK.AVG.

Rank of a number; ties get the average rank.

The rank of a number in a list; equal values share the top rank.

The interest rate per period of a loan or investment.

Amount received at maturity for a fully invested security.

Fold an array down to a single accumulated value.

Overwrite characters at a fixed position with new text.

REPLACE counted in bytes.

Repeat a text string a number of times.

The last N characters of a string.

RIGHT counted in bytes.

Convert a number to Roman numerals (as text).

Round a number to a set number of decimal places.

Round toward zero to a set number of digits.

Round away from zero to a set number of digits.

The row number of a reference (or of the formula cell).

Count the rows in a range or array.

Equivalent interest rate for an investment's growth.

The r² of the linear regression through the points.

Real-time data from a COM automation server.

Like REDUCE but returns every intermediate total.

Like FIND, but case-insensitive and allows ? and * wildcards.

SEARCH counted in bytes.

Secant of an angle in radians.

Hyperbolic secant.

The second of a time value (0–59).

Generate a spilled array of consecutive numbers.

The sum of a power series.

The sheet number of a reference.

The number of sheets in a reference.

-1, 0 or 1 for the sign of a number.

Sine of an angle given in radians.

Hyperbolic sine of a number.

Skewness of a distribution (sample).

Skewness based on the whole population.

Straight-line depreciation for one period.

Slope of the linear regression line through the points.

The k-th smallest value.

Return a range sorted — results spill, source untouched.

Sort one array by the values of one or more other arrays.

The positive square root.

Square root of (number × π).

The z-score of a value for a given mean and SD.

Pre-2010 name for STDEV.S.

Standard deviation of an entire population.

Standard deviation estimated from a sample.

Sample standard deviation, counting text and logicals.

Pre-2010 name for STDEV.P.

Population standard deviation, counting text and logicals.

Standard error of the predicted y for each x.

Replace every (or the Nth) occurrence of a piece of text.

Aggregate a range while ignoring other SUBTOTALs and (optionally) hidden rows.

Add up numbers, cells and ranges.

Add the cells that meet a single condition.

Add cells that meet several conditions at once.

Multiply arrays element-by-element and sum the result — a Swiss-army knife.

Sum of the squares of the numbers.

Sum of (x² − y²) across two arrays.

Sum of (x² + y²) across two arrays.

Sum of (x − y)² across two arrays.

Compare one expression against a list of values and return the match.

Sum-of-years'-digits depreciation.

Return the value if it is text, else "".

Student's t distribution (left tail).

Two-tailed Student's t distribution.

Right-tailed Student's t distribution.

Inverse of the left-tail t distribution.

Inverse of the two-tailed t distribution.

Probability from a Student's t-test.

Keep a number of rows/columns from the start or end of an array.

Tangent of an angle given in radians.

Hyperbolic tangent of a number.

Bond-equivalent yield of a Treasury bill.

Price per ₹100 face value of a Treasury bill.

Yield of a Treasury bill.

Pre-2010 name for T.DIST.2T / T.DIST.RT.

Format a number or date as text using a format code.

The text that comes after a given delimiter.

The text that comes before a given delimiter.

Join a list with a delimiter, optionally skipping blanks.

Split text into a spilled array by a column and/or row delimiter.

Build a time value from hours, minutes, seconds.

Convert a time written as text into a time value.

Pre-2010 name for T.INV.2T.

Flatten an array into a single column.

Today's date, refreshed each time the sheet recalculates.

Flatten an array into a single row.

Flip a vertical range to horizontal or vice versa.

Fit a line to known data and extend it (spills).

Remove leading, trailing and repeated inner spaces.

Mean after discarding a percentage of the extremes.

The logical value TRUE.

Cut off decimals without rounding.

Pre-2010 name for T.TEST.

A code for the data type of a value (1 number, 2 text, 4 logical, 16 error, 64 array).

The character for a Unicode code point.

The Unicode code point of the first character.

Return the distinct values from a range.

Convert text to UPPERCASE.

Convert a text string that looks like a number into a real number.

Text representation of any value.

Pre-2010 name for VAR.S (sample variance).

Variance of an entire population.

Variance based on a sample.

Sample variance, counting text and logicals.

Pre-2010 name for VAR.P (population variance).

Population variance, counting text and logicals.

Declining-balance depreciation over a partial period.

Find a value in the first column of a table, return a value from a column to its right.

Stack arrays on top of each other into one taller array.

Return data from a web service (URL).

The day of the week of a date, as a number.

The week number of the year for a date.

Pre-2010 name for WEIBULL.DIST.

Weibull distribution (reliability analysis).

The date N working days after a start date.

Date N working days away with a custom weekend.

Wrap a vector into columns of a fixed height.

Wrap a vector into rows of a fixed width.

Internal rate of return for cash flows on specific (uneven) dates.

Modern lookup — any direction, exact by default, built-in not-found value.

Modern MATCH — exact by default, reverse search, wildcard and binary modes.

Net present value for cash flows on specific dates.

TRUE if an odd number of conditions are TRUE (exclusive or).

The year of a date as a 4-digit number.

The fraction of a year between two dates (for interest, tenure, age).

Yield of a security that pays periodic interest.

Annual yield of a discounted security.

Annual yield of a security paying interest at maturity.

One-tailed probability value of a z-test.

Pre-2010 name for Z.TEST.