All Functions

Calculates accrued interest for a security that pays periodic interest.

Calculates accrued interest for a security that pays interest at maturity.

Calculates depreciation for a specific period using the French declining balance method.

Calculates depreciation for a specific period using the French straight-line method.

Calculates the number of days from the beginning of the coupon period to the settlement date.

Calculates the number of days in the coupon period that contains the settlement date.

Calculates the number of days from the settlement date to the next coupon date.

Calculates the next coupon date after the settlement date.

Calculates the number of coupons payable between the settlement date and maturity date.

Calculates the previous coupon date before the settlement date.

Calculates the cumulative interest paid on a loan between two periods.

Calculates the cumulative principal paid on a loan between two periods.

Calculates declining balance depreciation for a specified period.

Calculates double-declining balance depreciation for a specified period.

Calculates the discount rate for a security.

Converts a dollar price expressed as a fraction into a decimal number.

Converts a decimal dollar price into a fractional dollar price.

Calculates the Macaulay duration of a bond.

Calculates the effective annual interest rate from a nominal rate.

Calculates the future value of a series of equal payments and/or a lump sum.

Calculates the future value of a principal after applying a series of compound interest rates.

Calculates the interest rate for a fully invested security.

Finds the interest portion of a loan payment in a given period.

IRR tells you the annual return rate of an investment given its cash flows (initial cost + periodic returns). If IRR exceeds your required return, the investment is worthwhile.

Calculates the interest paid during a specific period of a loan with equal principal payments.

Calculates the modified duration of a bond.

Calculates the modified internal rate of return for a series of cash flows.

Calculates the nominal annual interest rate from an effective rate.

Calculates the number of periods for a loan or investment.

Finds the present value of future cash flows minus the initial investment.

Calculates the price of a security with an odd first coupon period.

Calculates the yield of a security with an odd first coupon period.

Calculates the price of a security with an odd last coupon period.

Calculates the yield of a security with an odd last coupon period.

Calculates the number of periods required for an investment to reach a specified value.

PMT tells you how much each payment will be on a fixed-rate loan. Give it the interest rate, number of payments, and loan amount — it returns the payment per period (monthly, yearly, etc.).

Finds the principal portion of a loan payment in a given period.

Calculates the price of a security that pays periodic interest.

Calculates the price of a discounted security.

Calculates the price of a security that pays interest at maturity.

Calculates the present value of a series of future payments or a future lump sum.

Calculates the interest rate per period of a loan or investment.

Calculates the amount received at maturity for a fully invested security.

Calculates the equivalent compound interest rate for an investment.

Calculates straight-line depreciation of an asset for one period.

Calculates sum-of-years-digits depreciation for a specified period.

Calculates the bond-equivalent yield for a Treasury bill.

Calculates the price of a Treasury bill per $100 face value.

Calculates the yield for a Treasury bill.

Calculates variable declining balance depreciation for any period you specify.

Calculates the internal rate of return for a schedule of cash flows that is not necessarily periodic.

Calculates the net present value for a schedule of cash flows that is not necessarily periodic.

Calculates the yield to maturity of a security that pays periodic interest.

Calculates the annual yield of a discounted security.

Calculates the annual yield of a security that pays interest at maturity.

Calculates the mean of the provided numbers.

Averages values that satisfy one condition.

Averages values that meet all of the specified conditions.

Pearson correlation coefficient between two arrays.

COUNT returns the count of cells containing numbers in a range.

COUNTA returns the count of all non-empty cells in a range, including text, numbers, and logical values.

COUNTBLANK returns the count of completely blank cells in a range.

COUNTIF counts cells that match a condition. Give it a range and a criterion (like '>100' or 'East') and it tells you how many cells pass the test.

Counts cells that meet all of the specified conditions.

Predicts a y-value from a linear regression at a given x (Excel 2016+).

Predicts a y-value from a linear regression at a given x.

Fits an exponential curve and returns predicted y-values (array).

Returns regression statistics (slope, intercept, and more) as an array.

MAX finds the highest value in a range.

Finds the largest value where multiple conditions are all true.

Finds the central (50th percentile) value of a dataset.

MIN finds the lowest value in a range.

Finds the smallest value where multiple conditions are all true.

Returns every value tied for the highest frequency as an array.

Returns the single most frequent value, choosing the lowest on a tie.

Returns the single most frequently occurring value in a set of numbers.

Pearson correlation coefficient between two arrays (same as CORREL).

Returns the value at percentile k (0 < k < 1) using the exclusive method.

Returns the value at percentile k (0 <= k <= 1) using the inclusive method.

Returns the value at a given percentile (k from 0 to 1, inclusive).

Returns quartile 1, 2, or 3 using the exclusive method (endpoints excluded).

Returns the chosen quartile (0=min to 4=max) using the inclusive method.

Returns the specified quartile (0=min, 1, 2=median, 3, 4=max) of a dataset.

Ranks a value; ties receive the average of the ranks they occupy.

Ranks a value in a list; ties share a rank and the next rank is skipped.

Gives the rank of a value in a dataset, with ties sharing a rank.

Sample standard deviation (n-1) of a set of numbers.

Sample standard deviation that also counts text and logicals as numbers.

Population standard deviation (n) of a set of numbers.

Population standard deviation that also counts text and logicals as numbers.

SUM calculates the sum of numeric values in a range.

Fits a linear trend and returns predicted y-values (array).

Sample variance (n-1) of a set of numbers.

Sample variance that also counts text and logicals as numbers.

Population variance (n) of a set of numbers.

Population variance that also counts text and logicals as numbers.

ABS converts negative numbers to positive, leaving positive numbers unchanged.

Returns the angle (in radians) whose cosine is the supplied number.

Returns the angle (in radians) whose sine is the supplied number.

Returns the angle (in radians) whose tangent is the supplied number.

Returns the angle in radians to a point given by its x and y coordinates.

Rounds up to a multiple of significance, with a mode for negative numbers.

Rounds a number up to the nearest multiple of significance.

Returns the cosine of an angle given in radians.

Converts radians to degrees.

Returns e raised to a power.

Rounds down to a multiple of significance, with a mode for negative numbers.

Rounds a number down to the nearest multiple of significance.

Rounds a number down to the nearest integer.

Returns the natural (base-e) logarithm of a number.

Returns the logarithm of a number to a given base (default 10).

Returns the base-10 logarithm of a number.

Returns the remainder after division, with the sign of the divisor.

Rounds a number to the nearest multiple of a given value.

Returns the constant π (about 3.14159).

POWER calculates number^power, raising a base number to an exponent.

Multiplies together all supplied numbers.

Converts degrees to radians.

ROUND rounds numbers to a given number of decimal places, following standard rounding rules.

ROUNDDOWN always rounds numbers down, never up, to the specified decimal places.

ROUNDUP always rounds numbers up, never down, to the specified decimal places.

Returns 1, 0, or -1 indicating a number's sign.

Returns the sine of an angle given in radians.

SQRT calculates the square root of a number.

SUMIF adds values that match a condition. Give it a range to check, a condition to test, and a range of numbers to sum — it only adds the ones that pass the test.

Adds numbers that meet all of the specified conditions.

Sums the products of corresponding array elements; great for conditional calculations.

Returns the tangent of an angle given in radians.

Truncates a number to an integer or set number of digits (no rounding).

Strips non-printable control characters from text.

CONCAT combines multiple text strings into a single string.

Joins text values together (legacy function).

Finds the position of text within another text string, case-sensitive.

LEFT extracts a specified number of characters from the left side of a text string.

LEN counts the number of characters in a text string.

Returns the byte length of text.

Makes every letter lowercase.

MID extracts characters from the middle of a text string at a specified starting position.

Title-cases each word in a string.

Replaces characters by position.

Repeats text a given number of times.

RIGHT extracts a specified number of characters from the right side of a text string.

Finds the position of text within another text string, case-insensitive.

SUBSTITUTE replaces specific text within a string with new text.

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

Joins text with a delimiter, optionally skipping blanks.

TRIM cleans up text by removing leading, trailing, and extra spaces between words.

Makes every letter uppercase.

Turns number-like text into an actual number.

Assembles a date from year, month, and day values.

Returns the difference between two dates in years, months, or days.

DAY returns the day number (1-31) from a date.

Counts the days from start_date to end_date.

Shifts a date by a number of months.

Returns the last day of the month offset by n months.

Returns the hour portion (0-23) of a time value.

Returns the minute portion (0-59) of a time value.

MONTH returns the month (1-12) from a date.

Counts workdays between two dates with a custom weekend.

Counts workdays between two dates.

NOW displays the current date and time, updating automatically.

Returns the second portion (0-59) of a time value.

Converts hour, minute, and second into a time fraction of a day.

TODAY displays today's date, updating automatically when the workbook opens or recalculates.

Returns the day-of-week number for a date.

Returns the week-of-year number for a date.

Returns a date n workdays away with a custom weekend.

Returns a date n workdays from a start date.

YEAR returns the year component of a date.

D-functions calculate on database rows that match a criteria range, using a shared (database, field, criteria) signature.

Averages the values in a database column for rows that meet the criteria.

Counts numeric values in a database column for rows that meet the criteria.

Counts non-blank values in a database column for rows that meet the criteria.

Pulls one value from a database column when exactly one row meets the criteria.

Returns the maximum value in a database column for rows that meet the criteria.

Returns the minimum value in a database column for rows that meet the criteria.

Multiplies the values in a database column for rows that meet the criteria.

Sample standard deviation of a database column for rows that meet the criteria.

Population standard deviation of a database column for rows that meet the criteria.

Sums the values in a database column for rows that meet the criteria.

Sample variance of a database column for rows that meet the criteria.

Population variance of a database column for rows that meet the criteria.

Returns the nth value from a list.

Looks up a value across rows (horizontal search).

INDEX retrieves a value from a specific row and column in a range.

Turns text that looks like a cell reference into an actual reference.

MATCH finds the position of a value in a range.

Returns a range shifted by a given number of rows and columns from a starting point.

Flips rows and columns of an array.

VLOOKUP searches for a value in the first column of a table array and returns a matching value from another column in the same row.

XLOOKUP is the upgraded VLOOKUP — it can search left or right, doesn't require a column index number, handles missing values with a default result, and supports nested arrays. Available in Excel 365 and Excel 2021+.

AND checks if ALL conditions are TRUE.

Returns the logical value FALSE.

IF checks a condition and returns different values based on whether it is true or false.

IFERROR catches errors and returns a custom value instead.

Replaces #N/A errors with a fallback value.

A simpler way to write multiple IF statements without nesting.

NOT flips TRUE to FALSE and FALSE to TRUE.

OR checks if ANY condition is TRUE.

Returns the logical value TRUE.

Returns TRUE when an odd number of arguments are TRUE.

FILTER pulls out matching rows from a table based on a condition. Unlike VLOOKUP (which returns one value), FILTER can return entire rows and multiple matches — all in one formula.

Generates an array of random numbers.

Generates a sequence of numbers in an array.

Sorts a range or array (spills the result).

Sorts an array using other arrays as keys.

Returns unique values (spills the result).

Modern lookup that returns a position (MATCH replacement).

Returns information about a cell (address, contents, format, etc.).

Returns a number code for an error type.

Tests whether a cell is truly empty.

Tests whether a value is any error.

Tests specifically for the #N/A error.

Tests whether a value is numeric.

Tests whether a value is a reference.

Tests whether a value is text.

Returns a number code for a value's type.

ARRAYFORMULA wraps a formula and applies it to every cell in a range at once, so you don't need to copy formulas down.

DETECTLANGUAGE tells you what language a piece of text is written in.

GOOGLEFINANCE pulls live stock prices, currency rates, and historical market data into your spreadsheet.

GOOGLETRANSLATE translates text in a cell from a source language to a target language.

IMAGE inserts a picture from a URL into a spreadsheet cell and lets you control its size and cropping.

IMPORTRANGE imports a range from another Google Sheets file by its spreadsheet URL and a range reference.

QUERY lets you run SQL-like commands on your spreadsheet data. Filter, sort, aggregate, pivot — all in one formula. It's the Swiss Army knife of Google Sheets data manipulation.

REGEXEXTRACT pulls out the part of a text that matches a regular expression pattern.

REGEXMATCH returns TRUE if the text matches a regular expression pattern, otherwise FALSE.

REGEXREPLACE searches text for a pattern and replaces matching parts with new text.

SPARKLINE draws a tiny chart in a cell from a range of numbers, with options for line, bar, column, and win/loss charts.