MSDN Community

Analysis Services Tutorials

DAX Functions Reference

This document provides a comprehensive reference for Data Analysis Expressions (DAX) functions, essential for building powerful analytical models in Analysis Services and Power BI.

Table of Contents

Aggregation Functions

These functions perform calculations across entire columns or expressions.

AVERAGE()

Calculates the average of numbers in a column.

Syntax: AVERAGE()

AVERAGEA()

Calculates the average of numbers, text, and logical values in a column.

Syntax: AVERAGEA()

COUNT()

Counts the number of cells that contain numbers.

Syntax: COUNT()

COUNTA()

Counts the number of non-blank cells.

Syntax: COUNTA()

MAX()

Returns the largest value in a column.

Syntax: MAX()

MIN()

Returns the smallest value in a column.

Syntax: MIN()

SUM()

Calculates the sum of numbers in a column.

Syntax: SUM()

Date and Time Functions

These functions manipulate date and time values.

DATE(, , )

Creates a date value from year, month, and day numbers.

Syntax: DATE(, , )

YEAR()

Returns the year part of a date.

Syntax: YEAR()

MONTH()

Returns the month part of a date.

Syntax: MONTH()

DAY()

Returns the day part of a date.

Syntax: DAY()

TODAY()

Returns the current date.

Syntax: TODAY()

Filter Functions

These functions are used to manipulate the context in which expressions are evaluated.

ALL( [, ]...)

Removes all filters from a table or columns.

Syntax: ALL( [, ]...)

ALLEXCEPT(, [, ]...)

Removes all filters from a table except for the specified columns.

Syntax: ALLEXCEPT(, [, ]...)

FILTER(, )

Returns a table that has been filtered.

Syntax: FILTER(, )

RELATED()

Returns a related value from another table. Requires a relationship between tables.

Syntax: RELATED()

RELATEDTABLE()

Returns a table of related rows from another table. Requires a relationship between tables.

Syntax: RELATEDTABLE()

Logical Functions

These functions perform logical operations.

AND(, )

Returns TRUE if all arguments are TRUE.

Syntax: AND(, )

IF(, , )

Returns one value if a logical expression is TRUE and another value if it is FALSE.

Syntax: IF(, , )

OR(, )

Returns TRUE if any argument is TRUE.

Syntax: OR(, )

NOT()

Reverses the logical value of its argument.

Syntax: NOT()

Mathematical and Trig Functions

These functions perform mathematical calculations.

ABS()

Returns the absolute value of a number.

Syntax: ABS()

DIVIDE(, [, ])

Divides two numbers and returns the result, or an optional alternative result if division by zero occurs.

Syntax: DIVIDE(, [, ])

INT()

Rounds a number down to the nearest integer.

Syntax: INT()

POWER(, )

Returns the result of a number raised to a power.

Syntax: POWER(, )

ROUND(, )

Rounds a number to a specified number of digits.

Syntax: ROUND(, )

String Functions

These functions manipulate text strings.

CONCATENATE(, [, ...])

Joins two or more text strings.

Syntax: CONCATENATE(, [, ...])

LEN()

Returns the number of characters in a text string.

Syntax: LEN()

LEFT(, )

Returns the leftmost characters from a text string.

Syntax: LEFT(, )

RIGHT(, )

Returns the rightmost characters from a text string.

Syntax: RIGHT(, )

SUBSTITUTE(, , [, ])

Replaces existing text with new text in a text string.

Syntax: SUBSTITUTE(, , [, ])

Time Intelligence Functions

These functions are specifically designed for time-based analysis.

CALCULATE( [, ]...)

Evaluates an expression in a modified filter context.

Syntax: CALCULATE( [, ]...)

DATESBETWEEN(, , )

Returns a table containing a column of dates between two dates.

Syntax: DATESBETWEEN(, , )

DATESYTD()

Returns a table containing a column of dates for the year to date.

Syntax: DATESYTD()

PREVIOUSMONTH()

Returns a table containing a column of dates for the previous month.

Syntax: PREVIOUSMONTH()

SAMEPERIODLASTYEAR()

Returns a table containing a column of dates for the same period in the previous year.

Syntax: SAMEPERIODLASTYEAR()

All Functions (Alphabetical)

A comprehensive list of all available DAX functions.

ABS()

Returns the absolute value of a number.

Syntax: ABS()

ADDRESS(, [, ]...)

Creates a cell reference as text.

Syntax: ADDRESS(, [, ]...)

AGGREGATE(, , [[,...]])

Returns an aggregate value based on a specified function.

Syntax: AGGREGATE(, , [[,...]])

ALL( [, ]...)

Removes all filters from a table or columns.

Syntax: ALL( [, ]...)

ALLEXCEPT(, [, ]...)

Removes all filters from a table except for the specified columns.

Syntax: ALLEXCEPT(, [, ]...)

ALLNOBLANKROW( [, ]...)

Removes all filters from a table or columns, excluding blank rows.

Syntax: ALLNOBLANKROW( [, ]...)

AND(, )

Returns TRUE if all arguments are TRUE.

Syntax: AND(, )

APPLYUPPER()

Applies the UPPERCASE function to a text string.

Syntax: APPLYUPPER()

ARABIC()

Converts a Roman numeral string to an integer.

Syntax: ARABIC()

AVERAGE()

Calculates the average of numbers in a column.

Syntax: AVERAGE()

AVERAGEA()

Calculates the average of numbers, text, and logical values in a column.

Syntax: AVERAGEA()

AVERAGEIF(, [, ])

Calculates the average of cells that meet a single criterion.

Syntax: AVERAGEIF(, [, ])

AVERAGEIFS(, , [, , ]...)

Calculates the average of cells that meet multiple criteria.

Syntax: AVERAGEIFS(, , [, , ]...)

BASE(, , [])

Converts a number from one base to another.

Syntax: BASE(, , [])

BINOM.DIST(, , , )

Returns the binomial distribution.

Syntax: BINOM.DIST(, , , )

BINOM.DIST.RANGE(, , [, ])

Returns the probability that a trial will produce a result between two occurrences.

Syntax: BINOM.DIST.RANGE(, , [, ])

BINOM.INV(, , )

Returns the largest value for which the binomial cumulative distribution is less than or equal to a specified alpha.

Syntax: BINOM.INV(, , )

BLANK()

Returns a blank value.

Syntax: BLANK()

BOOLEAN()

Converts a value to a boolean.

Syntax: BOOLEAN()

CALENDAR(, )

Returns a table of dates between two dates.

Syntax: CALENDAR(, )

CALENDARAUTO([fiscalYearEndMonth])

Returns a table of dates for all dates in the model, based on a fiscal year end month.

Syntax: CALENDARAUTO([fiscalYearEndMonth])

CALCULATE( [, ]...)

Evaluates an expression in a modified filter context.

Syntax: CALCULATE( [, ]...)

CATALOG()

Returns the current database name.

Syntax: CATALOG()

CELL(, )

Returns information about the formatting, location, or content of a cell.

Syntax: CELL(, )

CEILING( [, ])

Rounds a number up to the nearest integer or multiple of significance.

Syntax: CEILING( [, ])

CHAR()

Returns the character indicated by a number.

Syntax: CHAR()

CHOOSE(, [, ]...)

Selects a value or action to perform from a list of values.

Syntax: CHOOSE(, [, ]...)

CLEAN()

Removes all nonprintable characters from text.

Syntax: CLEAN()

CLIENT()

Returns client information for a specific connection.

Syntax: CLIENT()

CODE()

Returns the numeric code for the first character in a text string.

Syntax: CODE()

COLUMN()

Returns the column number of a cell reference.

Syntax: COLUMN()

COLUMNS()

Returns the number of columns in a table.

Syntax: COLUMNS()

COMBIN(, )

Returns the number of combinations for a given number of items.

Syntax: COMBIN(, )

COMPLETEDTIME(, )

Calculates the duration between two time values.

Syntax: COMPLETEDTIME(, )

CONCATENATE(, [, ...])

Joins two or more text strings.

Syntax: CONCATENATE(, [, ...])

CONCAT(, [, ...])

Joins two or more text strings. Similar to CONCATENATE.

Syntax: CONCAT(, [, ...])

CONTINUE_IF(, , )

Conditional logic for continuations.

Syntax: CONTINUE_IF(, , )

COS()

Returns the cosine of an angle.

Syntax: COS()

COSH()

Returns the hyperbolic cosine of a number.

Syntax: COSH()

COUNT()

Counts the number of cells that contain numbers.

Syntax: COUNT()

COUNTA()

Counts the number of non-blank cells.

Syntax: COUNTA()

COUNTBLANK()

Counts the number of blank cells in a range.

Syntax: COUNTBLANK()

COUNTROWS()

Counts the number of rows in a table.

Syntax: COUNTROWS()

COUNTX(, )

Counts rows that contain a number or expression.

Syntax: COUNTX(, )

COVARIANCE.P(, )

Returns the population covariance.

Syntax: COVARIANCE.P(, )

COVARIANCE.S(, )

Returns the sample covariance.

Syntax: COVARIANCE.S(, )

CROSSAPPLY(, )

Returns a table with columns from both tables, effectively a cross join.

Syntax: CROSSAPPLY(, )

CUBEKPIVALUE(, [, ...])

Returns a KPI value from a cube.

Syntax: CUBEKPIVALUE(, [, ...])

CUBEMEMBER(, [, ...])

Returns a member from a cube.

Syntax: CUBEMEMBER(, [, ...])

CUBEMONEYVALUE(, [, ...])

Returns a money value from a cube.

Syntax: CUBEMONEYVALUE(, [, ...])

CUBENUMBERVALUE(, [, ...])

Returns a number value from a cube.

Syntax: CUBENUMBERVALUE(, [, ...])

CUBEVALUE(, [, ...])

Returns a value from a cube.

Syntax: CUBEVALUE(, [, ...])

CURRENCY()

Converts a number to a currency type.

Syntax: CURRENCY()

CURRENCYVALUE()

Converts a text string representing currency to a number.

Syntax: CURRENCYVALUE()

CURVEEST(, , , )

Returns the coefficient for an exponential curve that best fits data.

Syntax: CURVEEST(, , , )

DATE(, , )

Creates a date value from year, month, and day numbers.

Syntax: DATE(, , )

DATESBETWEEN(, , )

Returns a table containing a column of dates between two dates.

Syntax: DATESBETWEEN(, , )

DATESINPERIOD(, , , )

Returns a table containing a column of dates that are within the specified period.

Syntax: DATESINPERIOD(, , , )

DATESMTD()

Returns a table containing a column of dates for the month to date.

Syntax: DATESMTD()

DATESQTD()

Returns a table containing a column of dates for the quarter to date.

Syntax: DATESQTD()

DATESYTD()

Returns a table containing a column of dates for the year to date.

Syntax: DATESYTD()

DAY()

Returns the day part of a date.

Syntax: DAY()

DAYS(, )

Returns the number of days between two dates.

Syntax: DAYS(, )

DAY.360(, [, ])

Returns the number of days between two dates based on a 360-day year.

Syntax: DAY.360(, [, ])

DEGREES()

Converts radians to degrees.

Syntax: DEGREES()

DELTA(, )

Calculates the difference between two values.

Syntax: DELTA(, )

DET()

Returns the determinant of a matrix.

Syntax: DET()

DIFFERENCE(, )

Calculates the difference between two measures.

Syntax: DIFFERENCE(, )

DISC(, , )

Returns the discount rate for a security that pays periodic interest.

Syntax: DISC(, , )

DISTINCTCOUNT()

Counts the number of distinct values in a column.

Syntax: DISTINCTCOUNT()

DISTINCTCOUNTNOBLANK()

Counts the number of distinct non-blank values in a column.

Syntax: DISTINCTCOUNTNOBLANK()

DIVIDE(, [, ])

Divides two numbers and returns the result, or an optional alternative result if division by zero occurs.

Syntax: DIVIDE(, [, ])

DPRODUCT(, )

Multiplies the values in a column of array or range that match criteria.

Syntax: DPRODUCT(, )

EDATE(, )

Returns a date that is the specified number of months before or after a date.

Syntax: EDATE(, )

EFFECT(, )

Returns the effective annual interest rate.

Syntax: EFFECT(, )

EOMONTH(, )

Returns the last day of the month that is the indicated number of months before or after the start date.

Syntax: EOMONTH(, )

ERF( [, ])

Returns the error function.

Syntax: ERF( [, ])

ERF.PRECISE()

Returns the error function.

Syntax: ERF.PRECISE()

ERFC()

Returns the complementary error function.

Syntax: ERFC()

ERFC.PRECISE()

Returns the complementary error function.

Syntax: ERFC.PRECISE()

EVEN()

Rounds a number up to the nearest even integer.

Syntax: EVEN()

EXP()

Returns e raised to the power of a number.

Syntax: EXP()

EXPON.DIST(, , )

Returns the exponential distribution.

Syntax: EXPON.DIST(, , )

FALSE()

Returns the logical value FALSE.

Syntax: FALSE()

FILTER(, )

Returns a table that has been filtered.

Syntax: FILTER(, )

FIND(, [, ])

Finds one text string within another (case-sensitive).

Syntax: FIND(, [, ])

FISHER()

Returns Fisher's transformation.

Syntax: FISHER()

FISHERINV()

Returns the inverse of Fisher's transformation.

Syntax: FISHERINV()

FIXED( [, ] [, ])

Rounds a number and formats it as text with a fixed number of decimal points.

Syntax: FIXED( [, ] [, ])

FLOOR( [, ])

Rounds a number down to the nearest integer or multiple of significance.

Syntax: FLOOR( [, ])

FORECAST.ETS(, , [, ] [, ] [, ] [, ] [, ] )

Calculates a future value based on existing values using the AAA version of the Exponential Smoothing (ETS) algorithm.

Syntax: FORECAST.ETS(...)

FORECAST.ETS.CONFINT(, , [, ] [, ] [, ] )

Returns a confidence interval for the forecast value.

Syntax: FORECAST.ETS.CONFINT(...)

FORECAST.ETS.SEASONALITY(, [, ] )

Detects the seasonality pattern of a time series.

Syntax: FORECAST.ETS.SEASONALITY(...)

FORECAST.ETS.STAT(, , [, ] )

Returns a statistical value from a time series forecast.

Syntax: FORECAST.ETS.STAT(...)

FORECAST.LINEAR(, , )

Calculates a future value using the linear regression method.

Syntax: FORECAST.LINEAR(, , )

FREQUENCY(, )

Returns an array of frequencies with which values occur within a range of values.

Syntax: FREQUENCY(, )

FV(, , [, ] [, ])

Returns the future value of an investment.

Syntax: FV(...)

GAMMA()

Returns the gamma function.

Syntax: GAMMA()

GAMMA.DIST(, , , )

Returns the gamma distribution.

Syntax: GAMMA.DIST(...)

GAMMA.INV(, , )

Returns the inverse of the gamma cumulative distribution.

Syntax: GAMMA.INV(...)

GAMMA.Ln()

Returns the natural logarithm of the gamma function.

Syntax: GAMMA.Ln()

GCD(, [, ...])

Returns the greatest common divisor.

Syntax: GCD(...)

GEOMEAN()

Returns the geometric mean.

Syntax: GEOMEAN()

GETPIVOTDATA(, [, , ]...)

Returns data from a pivot table.

Syntax: GETPIVOTDATA(...)

GROUPBY(, , , [, , ]...)

Groups rows of a table by one or more columns and computes aggregate values.

Syntax: GROUPBY(...)

HAIKU()

Returns a haiku based on the input text.

Syntax: HAIKU()

HARMean()

Returns the harmonic mean.

Syntax: HARMean()

HASH()

Returns a hash value of the input text.

Syntax: HASH()

HEX2BIN( [, ])

Converts a hexadecimal number to binary.

Syntax: HEX2BIN(...)

HEX2DEC()

Converts a hexadecimal number to decimal.

Syntax: HEX2DEC()

HEX2OCT( [, ])

Converts a hexadecimal number to octal.

Syntax: HEX2OCT(...)

HLOOKUP(, , [, ])

Searches a table row by row and returns a value in the same column from a specified row.

Syntax: HLOOKUP(...)

HORIZONTALTOTALS(, ...)

Calculates horizontal totals for specified columns.

Syntax: HORIZONTALTOTALS(...)

HOUR()

Returns the hour of a datetime value.

Syntax: HOUR()

HTML()

Returns the input text as HTML.

Syntax: HTML()

HYPERLINK( [, ])

Creates a shortcut that jumps to another location within the current workbook, or to a document on a network server, on the World Wide Web, or on an intranet.

Syntax: HYPERLINK(...)

IF(, , )

Returns one value if a logical expression is TRUE and another value if it is FALSE.

Syntax: IF(, , )

IFERROR(, )

Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.

Syntax: IFERROR(, )

IFNA(, )

Returns a specified value if a formula evaluates to the #N/A error; otherwise, returns the result of the formula.

Syntax: IFNA(, )

INDEX(, [, ])

Returns a value or the reference to a value from within a table or range.

Syntax: INDEX(...)

INFO()

Returns information about the current operating environment.

Syntax: INFO()

INHOMOGENEOUS()

Checks if a matrix is inhomogeneous.

Syntax: INHOMOGENEOUS()

INSERT(, , , )

Replaces part of a text string with a different text string.

Syntax: INSERT(...)

INT()

Rounds a number down to the nearest integer.

Syntax: INT()

INVERSE()

Returns the inverse of a matrix.

Syntax: INVERSE()

INVERSEC()

Returns the inverse secant of a number.

Syntax: INVERSEC()

INVERSEC.2T()

Returns the inverse secant of a number.

Syntax: INVERSEC.2T()

INVERSCOS()

Returns the inverse cosine of a number.

Syntax: INVERSCOS()

INVERSCOT()

Returns the inverse cotangent of a number.

Syntax: INVERSCOT()

INVERSSEC()

Returns the inverse secant of a number.

Syntax: INVERSSEC()

INVERSSIN()

Returns the inverse sine of a number.

Syntax: INVERSSIN()

INVERSTAN()

Returns the inverse tangent of a number.

Syntax: INVERSTAN()

INVERTIBLE()

Checks if a matrix is invertible.

Syntax: INVERTIBLE()

IPMT(, , [, ] [, ])

Returns the interest payment for a given period of an investment.

Syntax: IPMT(...)

IR<(>expression)

Implements "Immediate Refresh" for an expression.

Syntax: IR()

ISAFTER(, )

Checks if date1 is after date2.

Syntax: ISAFTER(, )

ISBEFORE(, )

Checks if date1 is before date2.

Syntax: ISBEFORE(, )

ISBLANK()

Checks whether a value is blank.

Syntax: ISBLANK()

ISCELL()

Checks if the input is a cell reference.

Syntax: ISCELL()

ISDATE()

Checks whether a value is a date.

Syntax: ISDATE()

ISEMPTY()

Checks whether a value is empty.

Syntax: ISEMPTY()

ISERR()

Checks whether a value is any error value except #N/A.

Syntax: ISERR()

ISERROR()

Checks whether a value is any error value.

Syntax: ISERROR()

ISFORMULA()

Checks whether a reference refers to a cell that contains a formula.

Syntax: ISFORMULA()

ISFULL()

Checks whether a value is not empty.

Syntax: ISFULL()

ISLOGICAL()

Checks whether a value is a logical value.

Syntax: ISLOGICAL()

ISNA()

Checks whether a value is the #N/A error value.

Syntax: ISNA()

ISNONTEXT()

Checks whether a value is not text.

Syntax: ISNONTEXT()

ISNUMBER()

Checks whether a value is a number.

Syntax: ISNUMBER()

ISONE()

Checks if a value is exactly 1.

Syntax: ISONE()

ISRANGE()

Checks if the input is a range reference.

Syntax: ISRANGE()

ISSCHEMA()

Checks if a schema name is valid.

Syntax: ISSCHEMA()

ISTEXT()

Checks whether a value is text.

Syntax: ISTEXT()

ISTEXTONLY()

Checks if a value contains only text characters.

Syntax: ISTEXTONLY()

ISUPPER()

Checks whether all letters in a text string are uppercase.

Syntax: ISUPPER()

ISZERO()

Checks if a value is exactly 0.

Syntax: ISZERO()

JIS()

Converts a single-byte character string to a double-byte character string.

Syntax: JIS()

JSONPARSE()

Parses a JSON string into a tabular format.

Syntax: JSONPARSE()

KICK()

Kicks a specified connection.

Syntax: KICK()

LARGE(, )

Returns the k-th largest value in a data set.

Syntax: LARGE(, )

LASTDATE()

Returns the last date in the current context.

Syntax: LASTDATE()

LASTNONBLANK(, )

Finds the last non-blank value in a column.

Syntax: LASTNONBLANK(, )

LCM(, [, ...])

Returns the least common multiple.

Syntax: LCM(...)

LEAD(, , )

Returns a value from a row that is a specified number of rows ahead of the current row.

Syntax: LEAD(...)

LEAST(, [, ...])

Returns the smallest value among the arguments.

Syntax: LEAST(...)

LEFT(, )

Returns the leftmost characters from a text string.

Syntax: LEFT(, )

LEN()

Returns the number of characters in a text string.

Syntax: LEN()

LN()

Returns the natural logarithm of a number.

Syntax: LN()

LOG( [, ])

Returns the logarithm of a number to a specified base.

Syntax: LOG( [, ])

LOG10()

Returns the base-10 logarithm of a number.

Syntax: LOG10()

LOGEST( [, ] [, ] [, ])

Returns an array of statistics about an exponential trend.

Syntax: LOGEST(...)

LOGINV(, )

Returns the logarithm of the inverse of the logistic function.

Syntax: LOGINV(...)

LOWER()

Converts text to lowercase.

Syntax: LOWER()

MAX()

Returns the largest value in a column.

Syntax: MAX()

MAXA()

Returns the largest value in a column, including text and logical values.

Syntax: MAXA()

MAXFORMULA(, , []...)

Returns the maximum value from a set of columns.

Syntax: MAXFORMULA(...)

MAXRANK(, )

Returns the rank of a value within a specified range.

Syntax: MAXRANK(, )

MEDIAN()

Returns the median of numbers in a column.

Syntax: MEDIAN()

MEDIANIF(, [, ])

Calculates the median of cells that meet a single criterion.

Syntax: MEDIANIF(...)

MEDIANIFS(, , [, , ]...)

Calculates the median of cells that meet multiple criteria.

Syntax: MEDIANIFS(...)

MID(, , )

Returns a specific number of characters from a text string, starting at the position you specify.

Syntax: MID(...)

MIN()

Returns the smallest value in a column.

Syntax: MIN()

MINA()

Returns the smallest value in a column, including text and logical values.

Syntax: MINA()

MINFORMULA(, , []...)

Returns the minimum value from a set of columns.

Syntax: MINFORMULA(...)

MINUTE()

Returns the minute of a datetime value.

Syntax: MINUTE()

MINIFS(, , [, , ]...)

Returns the minimum value among cells specified by a given set of conditions or criteria.

Syntax: MINIFS(...)

MIRR(, , )

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

Syntax: MIRR(...)

MONTH()

Returns the month part of a date.

Syntax: MONTH()

MROUND(, )

Rounds a number to the nearest specified multiple.

Syntax: MROUND(, )

Multiplication(, )

Performs multiplication of two numbers.

Syntax: Multiplication(, )

NAME()

Returns the name of a connection.

Syntax: NAME()

NAND(, )

Returns the NAND of its arguments.

Syntax: NAND(...)

NEXTDAY(, )

Returns the date of the next specified day of the week.

Syntax: NEXTDAY(, )

NOCASE()

Converts text to case-insensitive comparison.

Syntax: NOCASE()

NOMINAL(, )

Returns the nominal annual interest rate.

Syntax: NOMINAL(...)

NORM.DIST(, , , )

Returns the normal distribution.

Syntax: NORM.DIST(...)

NORM.INV(, , )

Returns the inverse of the normal cumulative distribution.

Syntax: NORM.INV(...)

NORM.S.DIST(, )

Returns the standard normal distribution.

Syntax: NORM.S.DIST(...)

NORM.S.INV()

Returns the inverse of the standard normal cumulative distribution.

Syntax: NORM.S.INV(...)

NOT()

Reverses the logical value of its argument.

Syntax: NOT()

NOW()

Returns the current date and time.

Syntax: NOW()

NPER(, , [, ] [, ])

Returns the number of periods for an investment.

Syntax: NPER(...)

OCT2BIN( [, ])

Converts an octal number to binary.

Syntax: OCT2BIN(...)

OCT2DEC()

Converts an octal number to decimal.

Syntax: OCT2DEC(...)

OCT2HEX( [, ])

Converts an octal number to hexadecimal.

Syntax: OCT2HEX(...)

ODDFPRICE(, , , , , )

Returns the price per $100 face value of a discounted security that pays periodic interest.

Syntax: ODDFPRICE(...)

ODDFYIELD(, , , , , )

Returns the yield for a discounted security that has an odd first coupon period.

Syntax: ODDFYIELD(...)

ODDLPRICE(, , , , , )

Returns the price per $100 face value of a discounted security that has an odd last coupon period.

Syntax: ODDLPRICE(...)

ODDLYIELD(, , , , , )

Returns the yield for a discounted security that has an odd last coupon period.

Syntax: ODDLYIELD(...)

ODDPOINT(, , , , , )

Returns the price per $100 face value of a security with an odd coupon period.

Syntax: ODDPOINT(...)

ODDYIELD(, , , , , )

Returns the yield for a security with an odd coupon period.

Syntax: ODDYIELD(...)

OR(, )

Returns TRUE if any argument is TRUE.

Syntax: OR(, )

ORDER(, )

Determines the order between two measures.

Syntax: ORDER(, )

PAGENUMBER()

Returns the current page number.

Syntax: PAGENUMBER()

PAIRS()

Checks if a matrix has pairs.

Syntax: PAIRS()

PAIRWISE_CORRELATION(, )

Calculates the pairwise correlation between two arrays.

Syntax: PAIRWISE_CORRELATION(...)

PARENT()

Returns the parent of the current row in a hierarchy.

Syntax: PARENT()

PARTNER()

Returns information about the partner in a connection.

Syntax: PARTNER()

PERCENTILE.INC(, )

Returns the k-th percentile of values in a range.

Syntax: PERCENTILE.INC(...)

PERCENTILE.EXC(, )

Returns the k-th percentile of values in a range, excluding the boundaries.

Syntax: PERCENTILE.EXC(...)

PERCENTRANK.INC(, [, ])

Returns the rank of a value in a data set as a percentage of the data set.

Syntax: PERCENTRANK.INC(...)

PERCENTRANK.EXC(, [, ])

Returns the rank of a value in a data set as a percentage of the data set, excluding the boundaries.

Syntax: PERCENTRANK.EXC(...)

PERMUT(, )

Returns the number of permutations for a given number of items.

Syntax: PERMUT(...)

PI()

Returns the value of pi.

Syntax: PI()

PMT(, , [, ] [, ])

Returns the periodic payment for an annuity.

Syntax: PMT(...)

POISSON.DIST(, , )

Returns the Poisson distribution.

Syntax: POISSON.DIST(...)

POWER(, )

Returns the result of a number raised to a power.

Syntax: POWER(, )

PREVIOUSDAY()

Returns a table containing a column of dates for the previous day.

Syntax: PREVIOUSDAY()

PREVIOUSMONTH()

Returns a table containing a column of dates for the previous month.

Syntax: PREVIOUSMONTH()

PREVIOUSQUARTER()

Returns a table containing a column of dates for the previous quarter.

Syntax: PREVIOUSQUARTER()

PREVIOUSYEAR()

Returns a table containing a column of dates for the previous year.

Syntax: PREVIOUSYEAR()

PRODUCT()

Multiplies all the numbers in a column.

Syntax: PRODUCT()

PRODUCTX(, )

Multiplies all numbers in a column resulting from an expression.

Syntax: PRODUCTX(, )

PROPER()

Converts the first letter of each word in a text string to uppercase.

Syntax: PROPER()

PV(, , [, ] [, ])

Returns the present value of an investment.

Syntax: PV(...)

QUARTER()

Returns the quarter of a date.

Syntax: QUARTER()

QUARTILE.INC(, )

Returns a quartile from a data set.

Syntax: QUARTILE.INC(...)

QUARTILE.EXC(, )

Returns a quartile from a data set, excluding boundaries.

Syntax: QUARTILE.EXC(...)

RADIANS()

Converts degrees to radians.

Syntax: RADIANS()

RAND()

Returns a random number between 0 and 1.

Syntax: RAND()

RANDBETWEEN(, )

Returns a random integer between two numbers.

Syntax: RANDBETWEEN(, )

RATE(, , [, ] [, ] [, ])

Returns the interest rate per period of an annuity.

Syntax: RATE(...)

RAW()

Executes a raw query against the data source.

Syntax: RAW()

REALIZEDGAIN(, , , , )

Returns the realized gain for a security that pays periodic interest.

Syntax: REALIZEDGAIN(...)

REPLACE(, , , )

Replaces part of a text string with a different text string.

Syntax: REPLACE(...)

REPORTTOSTRING()

Converts a report to a string.

Syntax: REPORTTOSTRING()

REQUEST()

Fetches content from a URL.

Syntax: REQUEST()

REVERSE()

Reverses a text string.

Syntax: REVERSE()

RIGHT(, )

Returns the rightmost characters from a text string.

Syntax: RIGHT(, )

ROMAN( [, ])

Converts an Arabic numeral to Roman numeral text.

Syntax: ROMAN(...)

ROUND(, )

Rounds a number to a specified number of digits.

Syntax: ROUND(, )

ROUNDDOWN(, )

Rounds a number down towards zero.

Syntax: ROUNDDOWN(...)

ROUNDUP(, )

Rounds a number up away from zero.

Syntax: ROUNDUP(...)

ROW()

Returns the row number of a cell reference.

Syntax: ROW()

ROWS()

Returns the number of rows in a table.

Syntax: ROWS()

RRI(, , )

Returns an equivalent interest rate for the growth of an investment.

Syntax: RRI(...)

RSQ(, )

Returns the square of the Pearson product moment correlation coefficient.

Syntax: RSQ(...)

S2X()

Converts a table into a string representation.

Syntax: S2X(
)

SALESFORCEID()

Converts a Salesforce ID to a standard format.

Syntax: SALESFORCEID()

SAMEDATE(, )

Checks if two dates fall on the same calendar day.

Syntax: SAMEDATE(, )

SAMEPERIODLASTYEAR()

Returns a table containing a column of dates for the same period in the previous year.

Syntax: SAMEPERIODLASTYEAR()

SAVE(, )

Saves data to a specified path.

Syntax: SAVE(, )

SCALAR()

Returns a scalar value.

Syntax: SCALAR()

SEARCH(, [, ])

Finds one text string within another (case-insensitive).

Syntax: SEARCH(...)

SECOND()

Returns the second of a datetime value.

Syntax: SECOND()

SECURITY()

Returns information about a financial security.

Syntax: SECURITY()

SELECTCOLUMNS(, , [, , ]...)

Returns a table that has been selected from another table.

Syntax: SELECTCOLUMNS(...)

SENTENCE()

Returns the input text formatted as a sentence.

Syntax: SENTENCE()

SEQUENCE( [, ] [, ] [, ])

Generates a sequence of numbers.

Syntax: SEQUENCE(...)

SETCOLOR()

Sets the current text color.

Syntax: SETCOLOR()

SETFONT()

Sets the current font face.

Syntax: SETFONT()

SETSIZE()

Sets the current font size.

Syntax: SETSIZE()

SIGN()

Returns the sign of a number.

Syntax: SIGN()

SIN()

Returns the sine of an angle.

Syntax: SIN()

SINH()

Returns the hyperbolic sine of a number.

Syntax: SINH()

SKEW()

Returns the skewness of a distribution.

Syntax: SKEW()

SKEW.P()

Returns the skewness of a distribution, based on the population.

Syntax: SKEW.P()

SLOPE(, )

Returns the slope of the linear regression line.

Syntax: SLOPE(...)

SMALL(, )

Returns the k-th smallest value in a data set.

Syntax: SMALL(...)

SORT(, [, ]...)

Sorts a table by one or more columns.

Syntax: SORT(...)

SORTBY(, , [, , ]...)

Sorts a table based on the values in one or more columns.

Syntax: SORTBY(...)

SPARKLINE( [, ]...)

Creates a small chart within a cell.

Syntax: SPARKLINE(...)

SQRT()

Returns the positive square root of a number.

Syntax: SQRT()

SQRTPI()

Returns the square root of (number * pi).

Syntax: SQRTPI()

STANDARDPRICED(, , , , , )

Returns the standard price per $100 face value of a security with an odd coupon period.

Syntax: STANDARDPRICED(...)

STANDARDYIELD(, , , , , )

Returns the standard yield for a security with an odd coupon period.

Syntax: STANDARDYIELD(...)

STARTDATE()

Returns the start date of a financial year.

Syntax: STARTDATE()

STDEVA()

Returns the standard deviation of the sample population.

Syntax: STDEVA(...)

STDEV.P()

Returns the standard deviation of the population.

Syntax: STDEV.P(...)

STDEV.S()

Returns the standard deviation of the sample.

Syntax: STDEV.S(...)

STDEVP()

Returns the standard deviation of the population.

Syntax: STDEVP(...)

STOCKHISTORY(, , [, ] [, ])

Returns historical stock data.

Syntax: STOCKHISTORY(...)

SUBSTITUTE(, , [, ])

Replaces existing text with new text in a text string.

Syntax: SUBSTITUTE(...)

SUBTOTAL(, [, ]...)

Returns a subtotal for a list or database.

Syntax: SUBTOTAL(...)

SUM()

Calculates the sum of numbers in a column.

Syntax: SUM()

SUM2D(, , )

Calculates the sum of elements in a 2D array based on dimensions.

Syntax: SUM2D(...)

SUMA()

Sums the numbers, text, and logical values in a column.

Syntax: SUMA()

SUMARRAY()

Sums all the elements in an array.

Syntax: SUMARRAY()

SUMIF(, [, ])

Sums the cells in a range that meet a single criterion.

Syntax: SUMIF(...)

SUMIFS(, , [, , ]...)

Sums the cells in a range that meet multiple criteria.

Syntax: SUMIFS(...)

SUMPRODUCT( [, ]...)

Multiplies corresponding components in the given arrays and returns the sum of those products.

Syntax: SUMPRODUCT(...)

SUMSQ()

Returns the sum of the squares of its arguments.

Syntax: SUMSQ(...)

SUMX(, )

Multiplies the last row in the column of the table, and returns the total.

Syntax: SUMX(, )

SWITCH(, , [, , ]... [, ])

Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

Syntax: SWITCH(...)

SYMBOLS()

Returns the symbols present in a text string.

Syntax: SYMBOLS()

T()

Returns the text in the reference.

Syntax: T()

TAN()

Returns the tangent of an angle.

Syntax: TAN()

TANH()

Returns the hyperbolic tangent of a number.

Syntax: TANH()

TARGETVALUE(, )

Returns the value if it meets the target.

Syntax: TARGETVALUE(, )

TCELL()

Returns the text of a cell.

Syntax: TCELL()

TDIST(, , )

Returns the t-distribution.

Syntax: TDIST(...)

TEXT(, )

Formats a number and converts it to text in a specified format.

Syntax: TEXT(...)

TEXTJOIN(, , [, ]...)

Joins the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value.

Syntax: TEXTJOIN(...)

TIME(, , )

Creates a time value from hour, minute, and second numbers.

Syntax: TIME(...)

TIMEDIFF(, )

Calculates the difference between two times.

Syntax: TIMEDIFF(...)

TIMERECORD(, )

Records a time duration.

Syntax: TIMERECORD(...)

TIMEVALUE()

Converts a time in the text format to a serial number.

Syntax: TIMEVALUE(...)

TINT()

Returns the integer part of a number.

Syntax: TINT()

TIR(, [])

Returns the internal rate of return for a series of cash flows.

Syntax: TIR(...)

TODAY()

Returns the current date.

Syntax: TODAY()

TOPEND(, )

Returns the value at the top end of a period.

Syntax: TOPEND(, )

TOTAL()

Calculates the sum of values in a column.

Syntax: TOTAL()

TOTALBILLABLEHOURS(, , , )

Calculates total billable hours within a date range.

Syntax: TOTALBILLABLEHOURS(...)

TOTALCREDITBALANCE()

Calculates the total credit balance for an account.

Syntax: TOTALCREDITBALANCE(...)

TOTALDEBITS()

Calculates the total debits for an account.

Syntax: TOTALDEBITS(...)

TOTALDEBT(, , )

Calculates the total debt repayment amount.

Syntax: TOTALDEBT(...)

TOTALDRINKINGS(, )

Calculates total drinks consumed.

Syntax: TOTALDRINKINGS(...)

TOTALEXPENSES()

Calculates total expenses for a category.

Syntax: TOTALEXPENSES(...)

TOTALINCOME()

Calculates total income from a source.

Syntax: TOTALINCOME(...)

TOTALLEFTOVERHOURS(, )

Calculates total leftover hours for an employee on a given date.

Syntax: TOTALLEFTOVERHOURS(...)

TOTALORDERVALUE()

Calculates the total value of an order.

Syntax: TOTALORDERVALUE(...)

TOTALPRICE()

Calculates the total price of a product.

Syntax: TOTALPRICE(...)

TOTALPROJECTCOST()

Calculates the total cost of a project.

Syntax: TOTALPROJECTCOST(...)

TOTALSALES()

Calculates total sales for a region.

Syntax: TOTALSALES(...)

TOTALSHAREPRICE()

Calculates the total share price for a stock symbol.

Syntax: TOTALSHAREPRICE(...)

TOTALWEIGHT()

Calculates the total weight of an item.

Syntax: TOTALWEIGHT(...)

TRACKINGNUMBER()

Returns the tracking number for an order.

Syntax: TRACKINGNUMBER(...)

TRANSPOSE()

Returns the transpose of an array.

Syntax: TRANSPOSE(...)

TRIM()

Removes spaces from text.

Syntax: TRIM()

TRUE()

Returns the logical value TRUE.

Syntax: TRUE()

TRUNC( [, ])

Truncates a number to an integer or to a specified number of decimal places.

Syntax: TRUNC(...)

TTEST(, , , )

Returns the t-test for two samples.

Syntax: TTEST(...)

TWODIMS()

Checks if a matrix has two dimensions.

Syntax: TWODIMS()

TYPE()

Returns the data type of a value.

Syntax: TYPE()

UNICHAR()

Returns the Unicode character corresponding to a number.

Syntax: UNICHAR()

UNICODE()

Returns the Unicode character code of the first character in a text string.

Syntax: UNICODE()

UPPER()

Converts text to uppercase.

Syntax: UPPER()

USER()

Returns the username of a connection.

Syntax: USER()

VAL()

Converts text that represents a number to a number.

Syntax: VAL()

VALUE( [, ])

Converts a text string that represents a number, date, or time to a number, date, or time value.

Syntax: VALUE(...)

VAR.P()

Returns the variance of the population.

Syntax: VAR.P(...)

VAR.S()

Returns the variance of the sample.

Syntax: VAR.S(...)

VARA()

Returns the variance of the sample population.

Syntax: VARA(...)

VARP()

Returns the variance of the population.

Syntax: VARP(...)

VLOOKUP(, , [, ])

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

Syntax: VLOOKUP(...)

WEEKDAY( [, ])

Returns the day of the week corresponding to a date.

Syntax: WEEKDAY(...)

WEEKNUM( [, ])

Converts a date into a serial number representing the week of the year.

Syntax: WEEKNUM(...)

WEBSERVICE()

Fetches data from a web service.

Syntax: WEBSERVICE()

WHILE(,

)

Iterates over a table while a condition is met.

Syntax: WHILE(...)

XIRR(, [, ])

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

Syntax: XIRR(...)

XNPV(, , )

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

Syntax: XNPV(...)

XOR(, )

Returns the exclusive OR of its arguments.

Syntax: XOR(...)

XTAG()

Returns the value associated with a tag name.

Syntax: XTAG()

YEAR()

Returns the year part of a date.

Syntax: YEAR()

YEARFRAC(, [, ])

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

Syntax: YEARFRAC(...)

Z.TEST(, [, ])

Returns the p-value of a two-tailed z-test.

Syntax: Z.TEST(...)

Z_CORREL(, )

Calculates the correlation coefficient between two data sets.

Syntax: Z_CORREL(...)

Z_TEST(, )

Returns the probability that a z-test is two-tailed.

Syntax: Z_TEST(...)

ZIPCODE(
)

Extracts the ZIP code from an address string.

Syntax: ZIPCODE(
)