# 250+ Functions

With powerful new functions and built‑in help, formulas in Numbers couldn’t be clearer.

Learn more about Numbers for Mac## Browse all functions by category

# Date & Time

## DATE

Combines separate values for year, month and day and returns a date/time value.

## DATEDIF

Returns the number of days, months or years between two dates.

## DATEVALUE

Converts a date text string and returns a date/time value. This function is provided for compatibility with other spreadsheet programs.

## DAY

Returns the day of the month for a given date/time value.

## DAYNAME

Returns the name of the day of the week from a date/time value or a number. Day 1 is Sunday.

## DAYS360

Returns the number of days between two dates based on twelve 30‑day months and a 360‑day year.

## EDATE

Returns a date that is some number of months before or after a given date.

## EOMONTH

Returns a date that is the last day of the month some number of months before or after a given date.

## HOUR

Returns the hour for a given date/time value.

## MINUTE

Returns the minutes for a given date/time value.

## MONTH

Returns the month for a given date/time value.

## MONTHNAME

Returns the name of the month from a number. Month 1 is January.

## NETWORKDAYS

Returns the number of working days between two dates. Working days exclude weekends and any other specified dates.

## NOW

Returns the current date/time value from the system clock.

## SECOND

Returns the seconds for a given date/time value.

## TIME

Converts separate values for hours, minutes and seconds into a date/time value.

## TIMEVALUE

Returns the time as a decimal fraction of a 24‑hour day from a given date/time value or from a text string.

## TODAY

Returns the current system date. The time is set to 12:00 a.m.

## WEEKDAY

Returns a number that is the day of the week for a given date.

## WEEKNUM

Returns the number of the week within the year for a given date.

## WORKDAY

Returns the date that is the given number of working days before or after a given date. Working days exclude weekends and any other dates specifically excluded.

## YEAR

Returns the year for a given date/time value.

## YEARFRAC

Finds the fraction of a year represented by the number of whole days between two dates.

# Duration

## DUR2DAYS

Converts a duration value to a number of days.

## DUR2HOURS

Converts a duration value to a number of hours.

## DUR2MILLISECONDS

Converts a duration value to a number of milliseconds.

## DUR2MINUTES

Converts a duration value to a number of minutes.

## DUR2SECONDS

Converts a duration value to a number of seconds.

## DUR2WEEKS

Converts a duration value to a number of weeks.

## DURATION

Combines separate values for weeks, days, hours, minutes, seconds and milliseconds and returns a duration value.

## STRIPDURATION

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 applications.

# Engineering

## BASETONUM

Converts a number of the specified base into a number in base 10.

## BESSELJ

Returns the integer Bessel function Jn(x).

## BESSELY

Returns the integer Bessel function Yn(x).

## BIN2DEC

Converts a binary number to the corresponding decimal number.

## BIN2HEX

Converts a binary number to the corresponding hexadecimal number.

## BIN2OCT

Converts a binary number to the corresponding octal number.

## CONVERT

Converts a number from one measurement system to its corresponding value in another measurement system.

## DEC2BIN

Converts a decimal number to the corresponding binary number.

## DEC2HEX

Converts a decimal number to the corresponding hexadecimal number.

## DEC2OCT

Converts a decimal number to the corresponding octal number.

## DELTA

Determines whether two values are exactly equal.

## ERF

Returns the error function integrated between two values.

## ERFC

Returns the complementary ERF function integrated between a given lower bound and infinity.

## GESTEP

Determines if one value is greater than or exactly equal to another value.

## HEX2BIN

Converts a hexadecimal number to the corresponding binary number.

## HEX2DEC

Converts a hexadecimal number to the corresponding decimal number.

## HEX2OCT

Converts a hexadecimal number to the corresponding octal number.

## NUMTOBASE

Converts a number from base 10 into a number in the specified base.

## OCT2BIN

Converts an octal number to the corresponding binary number.

## OCT2DEC

Converts an octal number to the corresponding decimal number.

## OCT2HEX

Converts an octal number to the corresponding hexadecimal number.

# Financial

## ACCRINT

Calculates the accrued interest added to the purchase price of a security and paid to the seller when the security pays periodic interest.

## ACCRINTM

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.

## BONDDURATION

Calculates the weighted average of the present value of the cash flows for an assumed par value of $100.

## BONDMDURATION

Calculates the modified weighted average of the present value of the cash flows for an assumed par value of $100.

## COUPDAYBS

Returns the number of days between the beginning of the coupon period in which settlement occurs and the settlement date.

## COUPDAYS

Returns the number of days in the coupon period in which settlement occurs.

## COUPDAYSNC

Returns the number of days between the settlement date and the end of the coupon period in which settlement occurs.

## COUPNUM

Returns the number of coupons remaining to be paid between the settlement date and the maturity date.

## CUMIPMT

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.

## CUMPRINC

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.

## CURRENCY

Returns data from the previous market day’s close about the exchange rate between two currencies, retrieved remotely via the internet.

## CURRENCYCODE

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.

## CURRENCYCONVERT

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.

## CURRENCYH

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).

## DB

Returns the amount of depreciation of an asset for a specified period using the fixed‑declining balance method.

## DDB

Returns the amount of depreciation of an asset based on a specified depreciation rate.

## DISC

Returns the annual discount rate of a security that pays no interest and is sold at a discount to its redemption value.

## EFFECT

Returns the effective annual interest rate from the nominal annual interest rate based on the number of compounding periods per year.

## FV

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.

## INTRATE

Returns the effective annual interest rate for a security that pays interest only at maturity.

## IPMT

Returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed interest rate.

## IRR

Returns the internal rate of return for an investment that is based on a series of potentially irregular cash flows that occur at regular time intervals.

## ISPMT

Returns the interest portion of a specified loan or annuity payment based on fixed, periodic payments and a fixed interest rate. This function is provided for compatibility with tables imported from other spreadsheet applications.

## MIRR

Returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows that occur at regular time intervals. The rate earned on positive cash flows and the rate paid to finance negative cash flows can differ.

## NOMINAL

Returns the nominal annual interest rate from the effective annual interest rate based on the number of compounding periods per year.

## NPER

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.

## NPV

Returns the net present value of an investment based on a series of potentially irregular cash flows that occur at regular time intervals.

## PMT

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.

## PPMT

Returns the principal portion of a specified loan or annuity payment based on fixed periodic payments and a fixed interest rate.

## PRICE

Returns the price of a security that pays periodic interest per $100 of redemption (par) value.

## PRICEDISC

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.

## PRICEMAT

Returns the price of a security that pays interest only at maturity per $100 of redemption (par) value.

## PV

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.

## 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.

## RECEIVED

Returns the maturity value for a security that pays interest only at maturity.

## SLN

Returns the amount of depreciation of an asset for a single period using the straight‑line method.

## STOCK

Returns data from the previous market day’s close about a given stock, retrieved remotely via the internet.

## STOCKH

Returns historical price information about a stock for a given date, retrieved remotely via the internet.

## SYD

Returns the amount of depreciation of an asset for a specified period using the sum‑of‑the‑years‑digits method.

## VDB

Returns the amount of depreciation of an asset over a chosen time interval, based on a specified depreciation rate.

## XIRR

Returns the internal rate of return for an investment that is based on a series of irregularly spaced cash flows.

## XNPV

Returns the present value of an investment or annuity based on a series of irregularly spaced cash flows and at a discount interest rate.

## YIELD

Returns the effective annual interest rate for a security that pays regular periodic interest.

## YIELDDISC

Returns the effective annual interest rate for a security that is sold at a discount to redemption value and pays no interest.

## YIELDMAT

Returns the effective annual interest rate for a security that pays interest only at maturity.

# Logical & Information

## AND

Returns TRUE if all arguments are true; otherwise it returns FALSE.

## FALSE

Returns the Boolean value FALSE. This function is included for compatibility with tables imported from other spreadsheet applications.

## IF

Returns one of two values depending on whether a specified expression evaluates to a Boolean value of TRUE or FALSE.

## IFERROR

Returns a value that you specify if a given value evaluates to an error; otherwise it returns the given value.

## IFS

Checks specified expressions and returns a value based on the first condition that evaluates to a boolean value of TRUE.

## ISBLANK

Returns TRUE if the specified cell is empty; otherwise it returns FALSE.

## ISDATE

Returns the boolean value TRUE if the given expression evaluates to a date and the boolean value FALSE otherwise.

## ISERROR

Returns TRUE if a given expression evaluates to an error; otherwise it returns FALSE.

## ISEVEN

Returns TRUE if the value is even (leaves no remainder when divided by 2); otherwise it returns FALSE.

## ISNUMBER

Returns the boolean value TRUE if the given expression evaluates to a number and the boolean value FALSE otherwise.

## ISODD

Returns TRUE if the value is odd (leaves a remainder when divided by 2); otherwise it returns FALSE.

## ISTEXT

Returns the boolean value TRUE if the given expression evaluates to a string and the boolean value FALSE otherwise.

## NOT

Returns the opposite of the Boolean value of a specified expression.

## OR

Returns TRUE if any argument is true; otherwise it returns FALSE.

## TRUE

Returns the Boolean value TRUE. This function is included for compatibility with tables imported from other spreadsheet applications.

# Numeric

## ABS

Returns the absolute value of a number or duration.

## CEILING

Rounds a number away from zero to the nearest multiple of the specified factor.

## COMBIN

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.

## EVEN

Rounds a number away from zero to the next even number.

## EXP

Returns e (the base of natural logarithms) raised to the specified power.

## FACT

Returns the factorial of a number.

## FACTDOUBLE

Returns the double factorial of a number.

## FLOOR

Rounds a number toward zero to the nearest multiple of the specified factor.

## GCD

Returns the greatest common divisor of the specified numbers.

## INT

Returns the nearest integer that is less than or equal to the number.

## LCM

Returns the least common multiple of the specified numbers.

## LN

Returns the natural logarithm of a number, the power to which e must be raised to result in the number.

## LOG

Returns the logarithm of a number using a specified base.

## LOG10

Returns the base‑10 logarithm of a number.

## MOD

Returns the remainder from a division.

## MROUND

Rounds a number to the nearest multiple of a specified factor.

## MULTINOMIAL

Returns the closed form of the multinomial coefficient of the given numbers.

## ODD

Rounds a number away from zero to the next odd number.

## PI

Returns the approximate value of π (pi), the ratio of a circle’s circumference to its diameter.

## POLYNOMIAL

Evaluates a polynomial at a given point.

## POWER

Returns a number raised to a power.

## PRODUCT

Returns the product of one or more numbers.

## QUOTIENT

Returns the integer quotient of two numbers.

## RAND

Returns a random number that is greater than or equal to 0 and less than 1.

## RANDBETWEEN

Returns a random integer within the specified range.

## ROMAN

Converts a number to Roman numerals.

## ROUND

Returns a number rounded to the specified number of places.

## ROUNDDOWN

Returns a number rounded toward zero (rounded down) to the specified number of places.

## ROUNDUP

Returns a number rounded away from zero (rounded up) to the specified number of places.

## SERIESSUM

Computes and returns the sum of a power series.

## SIGN

Returns 1 when a given number is positive, –1 when it is negative and 0 when it is zero.

## SQRT

Returns the square root of a number.

## SQRTPI

Returns the square root of a number multiplied by π (pi).

## SUM

Returns the sum of a collection of numbers.

## SUMIF

Returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.

## SUMIFS

Returns the sum of the cells in a collection where the test values meet the given conditions.

## SUMPRODUCT

Returns the sum of the products of corresponding numbers in one or more ranges.

## SUMSQ

Returns the sum of the squares of a collection of numbers.

## SUMX2MY2

Returns the sum of the difference of the squares of corresponding values in two collections.

## SUMX2PY2

Returns the sum of the squares of corresponding values in two collections.

## SUMXMY2

Returns the sum of the squares of the differences between corresponding values in two collections.

## TRUNC

Truncates a number to the specified number of digits.

# Reference

## ADDRESS

Constructs a cell address string from separate row, column and table identifiers.

## AREAS

Returns the number of ranges the function references.

## CHOOSE

Returns a value from a collection of values based on a specified index value.

## COLUMN

Returns the column number of the column containing a specified cell.

## COLUMNS

Returns the number of columns included in a specified range of cells.

## HLOOKUP

Returns a value from a range of rows by using the top row of values to pick a column and a row number to pick a row within that column.

## INDEX

Returns the value in the cell located at the intersection of the specified row and column within a range of cells.

## INDIRECT

Returns the contents of a cell or range referenced by an address specified as a string.

## INTERSECT.RANGES

Returns a range that is the intersection of the specified ranges.

## LOOKUP

Finds a match for a given search value in one range, then returns the value in the cell with the same relative position in a second range.

## MATCH

Returns the position of a value within a range.

## OFFSET

Returns a range of cells that is the specified number of rows and columns away from the specified base cell.

## ROW

Returns the row number of the row containing a specified cell.

## ROWS

Returns the number of rows included in a specified range of cells.

## TRANSPOSE

Returns a vertical range of cells as a horizontal range of cells, or vice versa.

## UNION.RANGES

Returns a range that represents a range representing the union of the specified ranges.

## VLOOKUP

Returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.

## XLOOKUP

Searches a range for a specified value and returns the value from the same row in another column.

# Statistical

## AVEDEV

Returns the average of the difference of a collection of numbers from their average (arithmetic mean).

## AVERAGE

Returns the average (arithmetic mean) of a collection of numbers.

## AVERAGEA

Returns the average (arithmetic mean) of a collection of values, including text and Boolean values.

## AVERAGEIF

Returns the average (arithmetic mean) of the cells in a range that meet a given condition.

## AVERAGEIFS

Returns the average (arithmetic mean) of the cells in a collection that meet all the given conditions.

## BETADIST

Returns the cumulative beta distribution probability value.

## BETAINV

Returns the inverse of the given cumulative beta distribution probability value.

## BINOMDIST

Returns the individual term binomial distribution probability of the specified form.

## CHIDIST

Returns the one‑tailed probability of the chi‑square distribution.

## CHIINV

Returns the inverse of the one‑tailed probability of the chi‑square distribution.

## CHITEST

Returns the value from the chi‑square distribution for the given data.

## CONFIDENCE

Returns a value for creating a statistical confidence interval for a sample from a population with a known standard deviation.

## CORREL

Returns the correlation between two collections using linear regression analysis.

## COUNT

Returns the number of its arguments that contain numbers, numeric expressions or dates.

## COUNTA

Returns the number of its arguments that are not empty.

## COUNTBLANK

Returns the number of cells in a range that are empty.

## COUNTIF

Returns the number of cells in a range that satisfy a given condition.

## COUNTIFS

Returns the number of cells in one or more ranges that satisfy given conditions (one condition per range).

## COVAR

Returns the covariance of two collections.

## CRITBINOM

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a given value.

## DEVSQ

Returns the sum of the squares of deviations of a collection of numbers from their average (arithmetic mean).

## EXPONDIST

Returns the exponential distribution of the specified form.

## FDIST

Returns the F probability distribution.

## FINV

Returns the inverse of the F probability distribution.

## FORECAST

Returns the forecasted y value for a given x value based on sample values using linear regression analysis.

## FREQUENCY

Returns an array of how often data values occur within a range of interval values.

## GAMMADIST

Returns the gamma distribution in the specified form.

## GAMMAINV

Returns the inverse gamma cumulative distribution.

## GAMMALN

Returns the natural logarithm of the gamma function, G(x).

## GEOMEAN

Returns the geometric mean.

## HARMEAN

Returns the harmonic mean.

## INTERCEPT

Returns the y‑intercept of the best‑fit line for the collection using linear regression analysis.

## LARGE

Returns the nth‑largest value within a collection. The largest value is ranked number 1.

## LINEST

Returns an array of the statistics for a straight line that best fits the given data using the least squares method.

## LOGINV

Returns the inverse of the log‑normal cumulative distribution function of x.

## LOGNORMDIST

Returns the log‑normal distribution.

## MAX

Returns the largest number in a collection.

## MAXA

Returns the largest number in a collection of values that may include text and Boolean values.

## MEDIAN

Returns the median value in a collection of numbers. The median is the value where half the numbers in the collection are less than the median and half are greater.

## MIN

Returns the smallest number in a collection.

## MINA

Returns the smallest number in a collection of values that may include text and Boolean values.

## MODE

Returns the most frequently occurring value in a collection of numbers.

## NEGBINOMDIST

Returns the negative binomial distribution.

## NORMDIST

Returns the normal distribution of the specified function form.

## NORMINV

Returns the inverse of the cumulative normal distribution.

## NORMSDIST

Returns the standard normal distribution.

## NORMSINV

Returns the inverse of the cumulative standard normal distribution.

## PERCENTILE

Returns the value within a collection that corresponds to a particular percentile.

## PERCENTRANK

Returns the rank of a value in a collection as a percentage of the collection.

## PERMUT

Returns the number of permutations for a given number of objects that can be selected from a total number of objects.

## POISSON

Returns the probability that a specific number of events will occur using the Poisson distribution.

## PROB

Returns the probability of a range of values if you know the probabilities of the individual values.

## QUARTILE

Returns the value for the specified quartile of a given collection.

## RANK

Returns the rank of a number within a range of numbers.

## SLOPE

Returns the slope of the best‑fit line for the collection using linear regression analysis.

## SMALL

Returns the nth‑smallest value within a range. The smallest value is ranked number 1.

## STANDARDIZE

Returns a normalized value from a distribution characterized by a given mean and standard deviation.

## STDEV

Returns the standard deviation, a measure of dispersion, of a collection of values based on their sample (unbiased) variance.

## STDEVA

Returns the standard deviation, a measure of dispersion, of a collection of values that may include text and Boolean values, based on the sample (unbiased) variance.

## STDEVP

Returns the standard deviation, a measure of dispersion, of a collection of values based on their population (true) variance.

## STDEVPA

Returns the standard deviation, a measure of dispersion, of a collection of values that may include text and Boolean values, based on the population (true) variance.

## TDIST

Returns the probability from the Student’s t‑distribution.

## TINV

Returns the t value (a function of the probability and degrees of freedom) from the Student’s t‑distribution.

## TTEST

Returns the probability associated with a Student’s t‑test, based on the t‑distribution function.

## VAR

Returns the sample (unbiased) variance, a measure of dispersion, of a collection of values.

## VARA

Returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values.

## VARP

Returns the population (true) variance, a measure of dispersion, of a collection of values.

## VARPA

Returns the sample (unbiased) variance, a measure of dispersion, of a collection of values, including text and Boolean values.

## WEIBULL

Returns the values of the Weibull distribution.

## ZTEST

Returns the one‑tailed probability value of the Z‑test.

# Text

## CHAR

Returns the character that corresponds to a decimal Unicode character code.

## CLEAN

Removes most common nonprinting characters (Unicode character codes 0–31) from text.

## CODE

Returns the decimal Unicode number of the first character in a specified string.

## CONCATENATE

Joins (concatenates) strings.

## COUNTMATCHES

Returns a number value representing the number of times a given substring appears in the original string value.

## DOLLAR

Returns a string formatted as a dollar amount from a given number.

## EXACT

Returns TRUE if the argument strings are identical in case and content.

## FIND

Returns the starting position of one string within another.

## FIXED

Rounds a number to the specified number of decimal places and then returns the result as a string value.

## LEFT

Returns a string consisting of the specified number of characters from the left end of a given string.

## LEN

Returns the number of characters in a string.

## LOWER

Returns a string that is entirely lowercase, regardless of the case of the characters in the specified string.

## MID

Returns a string consisting of the given number of characters from a string starting at the specified position.

## PLAINTEXT

Returns a string value stripped of any rich text attributes in the input value.

## PROPER

Returns a string 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.

## REGEX

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.

## REGEX.EXTRACT

Returns the matches or capture groups in a match of a given regular expression in a source string.

## REPLACE

Returns a string where a specified number of characters of a given string have been replaced with a new string.

## REPT

Returns a string that contains a given string repeated a specified number of times.

## RIGHT

Returns a string consisting of the given number of characters from the right end of a specified string.

## SEARCH

Returns the starting position of one string within another, ignoring case and allowing wildcards.

## SUBSTITUTE

Returns a string where the specified characters of a given string have been replaced with a new string.

## TEXTAFTER

Returns a string value consisting of all characters that appear after a given substring in the original string value.

## TEXTBEFORE

Returns a string value consisting of all characters that appear before a given substring in the original string value.

## TEXTBETWEEN

Returns a string value consisting of all characters that appear between two given substrings in the original string value.

## T

Returns the text contained in a cell. This function is included for compatibility with tables imported from other spreadsheet applications.

## TRIM

Returns a string based on a given string, after removing extra spaces.

## UPPER

Returns a string 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.

# Trigonometric

## ACOS

Returns the inverse cosine (arccosine) of a number.

## ACOSH

Returns the inverse hyperbolic cosine (hyperbolic arccosine) of a number.

## ASIN

Returns the arcsine (the inverse sine) of a number.

## ASINH

Returns the inverse hyperbolic sine of a number.

## ATAN

Returns the inverse tangent (arctangent) of a number.

## ATAN2

Returns the angle, relative to the positive x‑axis, of the line passing through the origin and the specified point.

## ATANH

Returns the inverse hyperbolic tangent of a number.

## COS

Returns the cosine of an angle that is expressed in radians.

## COSH

Returns the hyperbolic cosine of a number.

## DEGREES

Returns the number of degrees in an angle expressed in radians.

## RADIANS

Returns the number of radians in an angle expressed in degrees.

## SIN

Returns the sine of an angle that is expressed in radians.

## SINH

Returns the hyperbolic sine of the specified number.

## TAN

Returns the tangent of an angle that is expressed in radians.

## TANH

Returns the hyperbolic tangent of the specified number.