![](https://help.apple.com/assets/6467B9B3CF36103D3A318861/6467B9B8CF36103D3A318879/en_AU/784be3dfed809d4b6bb7cc427d8c5f01.png)
List of functions by category
All the functions covered in Formulas and Functions Help are listed below by category. You can also browse the functions by clicking Table of Contents at the top of the page.
Date and time functions
The date and time functions help you work with dates and times to solve problems such as finding the number of working days between two dates or finding the name of the day of the week a date will fall on.
Combines separate values for year, month and day, and returns a date/time value. Although dates can usually be entered directly as strings (for example, "31/12/2010"), using the DATE function ensures the date will be interpreted consistently, regardless of the date format specified in your Date & Time settings. | |
Returns the number of days, months or years between two dates. | |
Returns a date/time value for a given date string. This function is provided for compatibility with other spreadsheet apps. | |
Returns the day of the month for a given date/time value. | |
Returns the name of the day of the week for a date/time value or a number. Day 1 is Sunday. | |
Returns the number of days between two dates based on twelve 30-day months and a 360-day year. | |
Returns a date that is some number of months before or after a given date. | |
Returns a date that is the last day of the month some number of months before or after a given date. | |
Returns the hour for a given date/time value. | |
Returns the ISO week number for a specific date. | |
Returns the minutes for a given date/time value. | |
Returns the month for a given date/time value. | |
Returns the name of the month for a number. Month 1 is January. | |
Returns the number of working days between two dates. Working days exclude weekends and any other specified dates. | |
Returns a date/time value based on the current time shown on your computer’s clock. | |
Returns the seconds for a given date/time value. | |
Converts separate values for hours, minutes and seconds into a date/time value. | |
Returns the time as a decimal fraction of a 24-hour day for a given date/time value or time string. | |
Returns the current date based on your computer’s clock. The time is set to 12:00 a.m. | |
Returns a number that is the day of the week for a given date. | |
Returns the number of the week within the year for a given date. | |
Returns the date that is the given number of working days before or after a given date. Working days exclude weekends and any other specified dates. | |
Returns the year for a given date/time value. | |
Returns the fraction of a year represented by the number of whole days between two dates. |
Duration functions
The duration functions help you work with periods of time (durations) by converting between different time periods, such as hours, days and weeks.
Converts a duration value to a number of days. | |
Converts a duration value to a number of hours. | |
Converts a duration value to a number of milliseconds. | |
Converts a duration value to a number of minutes. | |
Converts a duration value to a number of seconds. | |
Converts a duration value to a number of weeks. | |
Combines separate values for weeks, days, hours, minutes, seconds and milliseconds, and returns a duration value. | |
Evaluates a given value and returns either the number of days represented, if a duration value, or the given value. This function is included for compatibility with other spreadsheet apps. |
Engineering functions
The engineering functions help you calculate some common engineering values and convert between different numeric bases.
Converts a number of the specified base into a number in base 10. | |
Returns the integer Bessel function Jn(x). | |
Returns the integer Bessel function Yn(x). | |
Converts a binary number to the corresponding decimal number. | |
Converts a binary number to the corresponding hexadecimal number. | |
Converts a binary number to the corresponding octal number. | |
Returns the bitwise AND of two numbers. | |
Returns the bitwise OR of two numbers. | |
Returns the bitwise XOR of two numbers. | |
Returns the bitwise LSHIFT of two numbers. | |
Returns the bitwise RSHIFT of two numbers. | |
Converts a number from one measurement system to its corresponding value in another measurement system. | |
Converts a decimal number to the corresponding binary number. | |
Converts a decimal number to the corresponding hexadecimal number. | |
Converts a decimal number to the corresponding octal number. | |
Determines whether two values are exactly equal. This function uses exact equality. By comparison, the = comparison operator uses string-based equality. | |
Returns the error function integrated between two values. | |
Returns the complementary ERF function integrated between a given lower bound and infinity. | |
Determines if one value is greater than or exactly equal to another value. This function uses exact equality. By comparison, the = comparison operator uses string-based equality. | |
Converts a hexadecimal number to the corresponding binary number. | |
Converts a hexadecimal number to the corresponding decimal number. | |
Converts a hexadecimal number to the corresponding octal number. | |
Converts a number from base 10 into a number in the specified base. | |
Converts an octal number to the corresponding binary number. | |
Converts an octal number to the corresponding decimal number. | |
Converts an octal number to the corresponding hexadecimal number. |
Financial functions
The financial functions help you work with cash flows, depreciable assets, annuities and investments by solving problems such as the amount of annual depreciation of an asset, the interest earned on an investment and the current market price of a bond.
Note: The currency shown in most function results depends on your Language & Region settings (in System Preferences in macOS 12 and earlier, System Settings in macOS 13 and later, and Settings in iOS and iPadOS, and in Time Zone/Region in iCloud Settings).
Returns the accrued interest on a security that pays periodic interest. The amount returned is the total interest accrued since the issue date, not the amount accrued since the last coupon payment. | |
Calculates the total accrued interest added to the purchase price of a security and paid to the seller when the security pays interest only at maturity. | |
Returns the weighted average of the present value of the cash flows for an assumed par value of $100. | |
Returns the modified weighted average of the present value of the cash flows for an assumed par value of $100. | |
Returns the number of days between the beginning of the coupon period in which settlement occurs and the settlement date. | |
Returns the number of days in the coupon period in which settlement occurs. | |
Returns the number of days between the settlement date and the end of the coupon period in which settlement occurs. | |
Returns the number of coupons remaining to be paid between the settlement date and the maturity date. | |
Returns the total interest included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate. | |
Returns the total principal included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate. | |
Returns data from the previous market day’s close about the exchange rate between two currencies, retrieved remotely via the Internet. | |
Returns the ISO currency code of a given currency value, or the currency code for the language and region set for the computer or for the current document. | |
Returns the price of a given currency value in a different currency, using exchange rate data from the previous market day’s close, retrieved remotely via the Internet. | |
Returns historical data on the exchange rate between two currencies for a given date. The value returned is in the target currency (the currency to which you’re converting). | |
Returns the amount of depreciation of an asset for a specified period using the fixed-declining balance method. | |
Returns the amount of depreciation of an asset based on a specified depreciation rate. | |
Returns the annual discount rate of a security that pays no interest and is sold at a discount to its redemption value. | |
Returns the effective annual interest rate from the nominal annual interest rate based on the number of compounding periods per year. | |
Returns the future value of an investment based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. | |
Returns the effective annual interest rate for a security that pays interest only at maturity. | |
Returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed interest rate. | |
Returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. | |
Returns the periodic interest portion for a fixed-rate loan where an equal principal reduction occurs at the beginning of each period and an interest payment on the outstanding balance is made at the end of each period. This function is provided primarily for compatibility with tables imported from other spreadsheet apps. | |
Returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. The rate earned on positive cash flows and the rate paid to finance negative cash flows can differ. | |
Returns the nominal annual interest rate from the effective annual interest rate based on the number of compounding periods per year. | |
Returns the number of payment periods for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. | |
Returns the net present value of an investment based on a series of potentially irregular cash flows that occur at regular time intervals. | |
Returns the fixed periodic payment for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. | |
Returns the principal portion of a specified loan or annuity payment based on fixed periodic payments and a fixed interest rate. | |
Returns the price of a security that pays periodic interest per $100 of redemption (par) value. | |
Returns the price of a security that is sold at a discount to redemption value and does not pay interest per $100 of redemption (par) value. | |
Returns the price of a security that pays interest only at maturity per $100 of redemption (par) value. | |
Returns the present value of an investment or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. | |
Returns the interest rate of an investment, loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate. | |
Returns the maturity value for a security that pays interest only at maturity. | |
Returns the depreciation of an asset for a single period using the straight-line method. | |
Returns data from the previous market day’s close about a given stock, retrieved remotely via the Internet. | |
Returns historical price information about a stock for a given date, retrieved remotely via the Internet. | |
Returns the amount of depreciation of an asset for a specified period using the sum-of-the-years-digits method. | |
Returns the amount of depreciation of an asset over a chosen time interval, based on a specified depreciation rate. | |
Returns the internal rate of return for an investment that is based on a series of irregularly spaced cash flows. | |
Returns the present value of an investment or annuity based on a series of irregularly spaced cash flows and at a discount interest rate. | |
Returns the effective annual interest rate for a security that pays regular periodic interest. | |
Returns the effective annual interest rate for a security that is sold at a discount to redemption value and pays no interest. | |
Returns the effective annual interest rate for a security that only pays interest at maturity. |
Logical and information functions
The logical and information functions help you evaluate the contents of cells and help to determine how to work with cell contents or formula results.
Returns boolean value TRUE if all arguments are true and the boolean value FALSE otherwise. | |
Returns the boolean value FALSE. This function is included for compatibility with tables imported from other spreadsheet apps. | |
Returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE. | |
Returns a value that you specify if a given value evaluates to an error; otherwise it returns the given value. | |
Checks specified expressions and returns a value based on the first condition that evaluates to a boolean value of TRUE. | |
Returns the boolean value TRUE if the specified cell is empty and the boolean value FALSE otherwise. | |
Returns the boolean value TRUE if the given expression evaluates to a date and the boolean value FALSE otherwise. | |
Returns the boolean value TRUE if the given number is even (leaves no remainder when divided by 2); otherwise it returns the boolean value FALSE. | |
Returns the boolean value TRUE if the given expression evaluates to a number and the boolean value FALSE otherwise. | |
Returns the boolean value TRUE if the given number is odd (leaves a remainder when divided by 2); otherwise it returns the boolean value FALSE. | |
Returns the boolean value TRUE if the given expression evaluates to a string and the boolean value FALSE otherwise. | |
Returns the opposite of the boolean value of a specified expression. | |
Returns the boolean value TRUE if any argument is true; otherwise it returns the boolean value FALSE. | |
Evaluates a value against a list, and returns a result that corresponds with either the first matching value or an optional value if there is no match. | |
Returns the boolean value TRUE. This function is included for compatibility with tables imported from other spreadsheet apps. |
Numeric functions
The numeric functions help you calculate commonly used mathematical values.
Returns the absolute value of a number or duration. | |
Rounds a number away from 0 to the nearest multiple of the specified factor. | |
Returns the number of different ways you can combine a number of items into groups of a specific size, ignoring the order within the groups. | |
Rounds a number away from 0 to the next even number. | |
Returns e (the base of natural logarithms) raised to the specified power. | |
Returns the factorial of a number. | |
Returns the double factorial of a number. | |
Rounds a number towards 0 to the nearest multiple of the specified factor. | |
Returns the greatest common divisor of a collection of number values. | |
Returns the nearest integer that is less than or equal to the number. | |
Returns the least common multiple of a collection of number values. | |
Returns the natural logarithm of a number (the power to which e must be raised to result in the number). | |
Returns the logarithm of a number using a specified base. | |
Returns the base-10 logarithm of a number. | |
Returns the remainder from a division. | |
Rounds a number to the nearest multiple of a specified factor. | |
Returns the closed form of the multinomial coefficient of the given numbers. | |
Rounds a number away from 0 to the next odd number. | |
Returns the approximate value of π (pi), the ratio of a circle’s circumference to its diameter. | |
Evaluates a polynomial at a given point. | |
Returns a number raised to a power. | |
Returns the product of a collection of number values. | |
Returns the integer quotient of two numbers. | |
Returns a random number that is greater than or equal to 0 and less than 1. | |
Returns a random integer within the specified range. | |
Converts a number to Roman numerals. | |
Returns a number rounded to the specified number of places. | |
Returns a number rounded towards 0 (rounded down) to the specified number of places. | |
Returns a number rounded away from 0 (rounded up) to the specified number of places. | |
Computes and returns the sum of a power series. The coefficients are for successive powers of a given value incremented by a given step value. | |
Returns 1 when a given number is positive, –1 when it is negative and 0 when it is zero. | |
Returns the square root of a number value. | |
Returns the square root of a number value after it has been multiplied by π (pi). | |
Returns a subtotal for a range of cells. | |
Returns the sum of a collection of numbers. | |
Returns the sum of a collection of numbers, including only numbers that satisfy a specified condition. | |
Returns the sum of the cells in a collection where the test values meet the given conditions. | |
Returns the sum of the products of corresponding numbers in one or more collections. | |
Returns the sum of the squares of a collection of number values. | |
Returns the sum of the difference of the squares of corresponding values in two collections. | |
Returns the sum of the squares of corresponding values in two collections. | |
Returns the sum of the squares of the differences between corresponding values in two collections. | |
Truncates a number to the specified number of digits. |
Reference functions
The reference functions help you find data within tables and retrieve data from cells.
Constructs a cell address string from separate row, column and table identifiers. | |
Returns the number of ranges the function references. | |
Returns a value from a collection of values based on a specified index value. | |
Returns the column number of the column containing a specified cell. | |
Returns the number of columns included in a specified collection of cells. | |
Returns a formula as a text string. | |
Returns aggregate data from a pivot table. | |
Returns a value from a collection of rows by using the top row of values to pick a column and a row number to pick a row within that column. | |
Creates a clickable link that opens a web page or new email message. | |
Returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function. | |
Returns the contents of a cell or range referenced by an address specified as a string value. | |
Returns a single value or an array of values contained at the intersection of the specified collections. | |
Finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection. | |
Returns the position of a value within a collection. | |
Returns an array of cells that is the specified number of rows and columns away from the specified base cell. | |
Returns a string representation of the given cell reference. | |
Returns the row number of the row containing a specified cell. | |
Returns the number of rows included in a specified collection of cells. | |
Returns a vertical collection of cells as a horizontal array of cells, or vice versa. | |
Returns an array that represents a collection representing the union of the specified ranges. | |
Returns a value from a collection of columns by using the left column of values to pick a row and a column number to pick a column in that row. | |
Searches a range for a specified value and returns the value from the same row in another column. | |
Returns a specified item’s relative position in a range of cells. |
Statistical functions
The statistical functions help you manipulate and analyse collections of data using a variety of measures and statistical techniques.
Returns the average of the difference of a set of numbers from their average (arithmetic mean). | |
Returns the average (arithmetic mean) of a set of numbers. | |
Returns the average (arithmetic mean) of a set containing any value. | |
Returns the average (arithmetic mean) of the cells in a set that meet a given condition. | |
Returns the average (arithmetic mean) of the cells in a given set where one or more sets meet one or more related conditions. | |
Returns the cumulative beta distribution probability value. | |
Returns the inverse of the given cumulative beta distribution probability value. | |
Returns the individual term binomial distribution probability of the specified form. | |
Returns the one-tailed probability of the chi-square distribution. | |
Returns the inverse of the one-tailed probability of the chi-square distribution. | |
Returns the value from the chi-square distribution for the given data. | |
Returns a value for creating a statistical confidence interval for a sample from a population with a known standard deviation. | |
Returns the correlation between two sets using linear regression analysis. | |
Returns the number of its arguments that contain numbers, numeric expressions or dates. | |
Returns the number of its arguments that are not empty. | |
Returns the number of cells in a collection that are empty. | |
Returns the number of cells in a collection that satisfy a given condition. | |
Returns the number of cells in one or more collections that satisfy given conditions (one condition per collection). | |
Returns the covariance of two sets of number values. | |
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a given value. | |
Returns the sum of the squares of deviations of a set of numbers from their average (arithmetic mean). | |
Returns the exponential distribution of the specified form. | |
Returns the F probability distribution. | |
Returns the inverse of the F probability distribution. | |
Returns the forecasted y value for a given x based on sample values using linear regression analysis. | |
Returns an array of how often data values occur within a collection of interval values. | |
Returns the gamma distribution in the specified form. | |
Returns the inverse gamma cumulative distribution. | |
Returns the natural logarithm of the gamma function, G(x). | |
Returns the geometric mean. | |
Returns the harmonic mean. | |
Returns the y-intercept of the best-fit line for the collection using linear regression analysis. | |
Returns the nth-largest value within a collection. The largest value is ranked number 1. | |
Returns an array of the statistics for a straight line that best fits the given data using the "least squares" method. | |
Returns the inverse of the log-normal cumulative distribution function of x. | |
Returns the log-normal distribution. | |
Returns the largest numeric value in a set of number values, date/time values or duration values. | |
Returns the largest number in a set containing any values. | |
Returns the largest numeric value in a range of cells, determined by a set of criteria. | |
Returns the median value of a set of numeric values. The median is the value where half the values in the set are less than the median and half are greater. | |
Returns the smallest numeric value in a set of number values, date/time values or duration values. | |
Returns the smallest numeric value in a set containing any values. | |
Returns the smallest numeric value in a range of cells, determined by a set of criteria. | |
Returns the most frequently occurring value in a set of numeric values. | |
Returns the negative binomial distribution. | |
Returns the normal distribution of the specified function form. | |
Returns the inverse of the cumulative normal distribution. | |
Returns the standard normal distribution. | |
Returns the inverse of the cumulative standard normal distribution. | |
Returns the value within a set of numeric values that corresponds to a particular percentile. | |
Returns the rank of a value within a set of numeric values as a percentage of the collection. | |
Returns the number of permutations for a given number of objects that can be selected from a total number of objects. | |
Returns the probability that a specific number of events will occur using the Poisson distribution. | |
Returns the probability of a collection of values if you know the probabilities of the individual values. | |
Returns the value for the specified quartile from a given set of numbers. | |
Returns the rank of a number within a set of numeric values. | |
Returns the slope of the best-fit line for the set using linear regression analysis. | |
Returns the nth-smallest value within a set of values. The smallest value is ranked number 1. | |
Returns a normalised value from a distribution characterised by a given mean and standard deviation. | |
Returns the standard deviation — a measure of dispersion — of a set of numeric values based on their sample (unbiased) variance. | |
Returns the standard deviation — a measure of dispersion — of a set of any values, based on the sample (unbiased) variance. | |
Returns the standard deviation — a measure of dispersion — of a set of numeric values based on their population (true) variance. | |
Returns the standard deviation — a measure of dispersion — of a set of any values, based on the population (true) variance. | |
Returns the probability from the student’s t-distribution. | |
Returns the t value (a function of the probability and degrees of freedom) from the student’s t-distribution. | |
Returns the probability associated with a student’s t-test, based on the t-distribution function. | |
Returns the sample (unbiased) variance — a measure of dispersion — of a set of numeric values. | |
Returns the sample (unbiased) variance — a measure of dispersion — of a set of any values. | |
Returns the population (true) variance — a measure of dispersion — of a set of numeric values. | |
Returns the sample (unbiased) variance — a measure of dispersion — of a set of any values. | |
Returns the Weibull distribution. The Weibull distribution is one of the continuous probability distributions. | |
Returns the one-tailed probability value of the Z-test. |
Text functions
The text functions help you work with strings of characters.
Returns the character that corresponds to a decimal Unicode character code. | |
Removes most common non-printing characters (Unicode character codes 0–31) from text. | |
Returns the decimal Unicode number of the first character in a specified string. | |
Joins (concatenates) values or the contents of cell ranges or strings. | |
Joins (concatenates) strings. | |
Returns a number value representing the number of times a given substring appears in the original string value. | |
Returns a string formatted as currency from a given number value. | |
Returns TRUE if the argument strings are identical in case and content. | |
Returns the starting position of one string within another. | |
Rounds a number value to the specified number of decimal places and then returns the result as a string value. | |
Returns a string value consisting of the specified number of characters from the left end of a given string value. | |
Returns the number of characters in a string value. | |
Returns a string value that is entirely lowercase, regardless of the case of the characters in the specified string value. | |
Returns a string value consisting of the given number of characters from a string value starting at the specified position. | |
Returns a string value stripped of any rich text attributes in the input value. | |
Returns a string value where the first letter of each word is uppercase and all remaining characters are lowercase, regardless of the case of the characters in the specified string value. | |
Enables usage of regular expressions in other text and conditional functions. It can be used with all functions that expect a condition (IF, COUNTIF, …) or a string match (SUBSTITUTE, TEXTBEFORE, …). When not used as a condition or to match text, REGEX returns the underlying regular expression as a string value. | |
Returns the matches or capture groups in a match of a given regular expression in a source string. | |
Returns a string value where a specified number of characters of a given string value have been replaced with a new string value. | |
Returns a string value that contains a given string value repeated a specified number of times. | |
Returns a string value consisting of the specified number of characters from the right end of a given string value. | |
Returns the starting position of one string value within another, ignoring case and allowing wild cards. | |
Returns a string value where the specified characters of a given string value have been replaced with a new string value. | |
Returns the text contained in a cell. This function is included for compatibility with tables imported from other spreadsheet apps. | |
Returns a string value consisting of all characters that appear after a given substring in the original string value. | |
Returns a string value consisting of all characters that appear before a given substring in the original string value. | |
Returns a string value consisting of all characters that appear between two given substrings in the original string value. | |
Combines the contents of cell ranges or strings with a specified delimiter between each value. | |
Returns a string value based on a given string value, after removing extra spaces. | |
Returns a string value that is entirely uppercase, regardless of the case of the characters in the specified string value. | |
Returns a number value, even if the argument is formatted as text. This function is included for compatibility with tables imported from other spreadsheet apps. |
Trigonometric functions
The trigonometric functions help you work with angles and their components.
Returns the inverse cosine (arc cosine) of a number. | |
Returns the inverse hyperbolic cosine (hyperbolic arc cosine) of a number. | |
Returns the arc sine (the inverse sine) of a number. | |
Returns the inverse hyperbolic sine of a number. | |
Returns the inverse tangent (arc tangent) of a number. | |
Returns the angle, relative to the positive x-axis, of the line passing through the origin and the specified point. | |
Returns the inverse hyperbolic tangent of a number. | |
Returns the cosine of an angle that is expressed in radians. | |
Returns the hyperbolic cosine of a number. | |
Returns the number of degrees in an angle expressed in radians. | |
Returns the number of radians in an angle expressed in degrees. | |
Returns the sine of an angle that is expressed in radians. | |
Returns the hyperbolic sine of the specified number. | |
Returns the tangent of an angle that is expressed in radians. | |
Returns the hyperbolic tangent of the specified number. |