⚡YOU ARE VIEWING: ADVANCED EXCEL FUNCTIONS PAGE

fx⚡ Advanced Power Tools

188 Advanced Formulas

Advanced Excel Functions Guide

Master high-performance Excel formulas: XLOOKUP, INDEX & MATCH, SUMIFS, TAKE, DROP, CHOOSECOLS, and dynamic arrays for data analysis.

Popular Advanced Tags:

fx|

Filter Advanced Functions by Category:Showing 188 of 188 advanced functions

Display Style: cards View

🔎Lookup & Reference Advanced Functions

26 functions

TAKE

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365 / 2024

Returns a specified number of contiguous rows or columns from the start or end of an array.

Formula Syntax

=TAKE(array, rows, [columns])

Example:=TAKE(SORT(A2:C50, 3, -1), 5)

DROP

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365 / 2024

Excludes a specified number of rows or columns from the start or end of an array.

Formula Syntax

=DROP(array, rows, [columns])

Example:=DROP(A1:C50, 1)

CHOOSECOLS

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365 / 2024

Returns specified columns from an array or range.

Formula Syntax

=CHOOSECOLS(array, col_num1, [col_num2], ...)

Example:=CHOOSECOLS(A2:F100, 1, 3, 5)

CHOOSEROWS

🔎 Lookup & ReferenceExcel 365 / 2024

Returns specified rows from an array or range.

Formula Syntax

=CHOOSEROWS(array, row_num1, [row_num2], ...)

Example:=CHOOSEROWS(A2:F100, 1, 5, 10)

LAMBDA

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365

Creates custom, reusable functions in Excel without writing VBA code.

Formula Syntax

=LAMBDA([parameter1, ...], calculation)

Example:=LAMBDA(x, y, x*1.1 + y)

TOCOL

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365 / 2024

Transforms a 2D array or range into a single vertical column.

Formula Syntax

=TOCOL(array, [ignore], [scan_by_column])

Example:=TOCOL(A1:C10, 1)

TOROW

🔎 Lookup & ReferenceExcel 365 / 2024

Transforms a 2D array or range into a single horizontal row.

Formula Syntax

=TOROW(array, [ignore], [scan_by_column])

Example:=TOROW(A1:C10, 1)

WRAPROWS

🔎 Lookup & ReferenceExcel 365 / 2024

Wraps a 1D vector into a 2D array by rows after reaching a specified number of elements.

Formula Syntax

=WRAPROWS(vector, wrap_count, [pad_with])

Example:=WRAPROWS(A1:A12, 3)

WRAPCOLS

🔎 Lookup & ReferenceExcel 365 / 2024

Wraps a 1D vector into a 2D array by columns after reaching a specified number of elements.

Formula Syntax

=WRAPCOLS(vector, wrap_count, [pad_with])

Example:=WRAPCOLS(A1:A12, 4)

CELL

🔎 Lookup & ReferenceAll Versions

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

Formula Syntax

=CELL(info_type, [reference])

Example:=MAP(A1:A10, LAMBDA(x, x * 1.1))

REDUCE

🔎 Lookup & ReferenceExcel 365

Reduces an array to an accumulated value by applying a LAMBDA to each element.

Formula Syntax

=REDUCE([initial_value], array, lambda)

Example:=REDUCE(0, A1:A10, LAMBDA(acc, val, acc + val))

SCAN

🔎 Lookup & ReferenceExcel 365

Scans an array by applying a LAMBDA to accumulate intermediate calculation values.

Formula Syntax

=SCAN([initial_value], array, lambda)

Example:=SCAN(0, A1:A10, LAMBDA(acc, val, acc + val))

MAKEARRAY

🔎 Lookup & ReferenceExcel 365

Returns a calculated array of specified row and column size by applying a LAMBDA.

Formula Syntax

=MAKEARRAY(rows, cols, lambda)

Example:=MAKEARRAY(5, 5, LAMBDA(r, c, r * c))

BYROW

🔎 Lookup & ReferenceExcel 365

Applies a LAMBDA to each row in an array and returns an array of results.

Formula Syntax

=BYROW(array, lambda)

Example:=BYROW(A1:C10, LAMBDA(r, SUM(r)))

BYCOL

🔎 Lookup & ReferenceExcel 365

Applies a LAMBDA to each column in an array and returns an array of results.

Formula Syntax

=BYCOL(array, lambda)

Example:=BYCOL(A1:C10, LAMBDA(c, AVERAGE(c)))

ISOMITTED

🔎 Lookup & ReferenceExcel 365

Checks whether an optional argument in a custom LAMBDA function is missing.

Formula Syntax

=ISOMITTED(argument)

Example:=MAP(A1:A10, LAMBDA(x, x * 1.1))

XMATCH

★ Popular Advanced

🔎 Lookup & ReferenceExcel 365 / 2021+

Returns the relative position of an item in an array or range of cells. Modern replacement for MATCH.

Formula Syntax

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Example:=XMATCH(101, A2:A50, 0)

FORMULATEXT

🔎 Lookup & ReferenceExcel 2013+

Returns a formula as a text string from a given cell reference.

Formula Syntax

=FORMULATEXT(reference)

Example:=FORMULATEXT(A2)

GETPIVOTDATA

★ Popular Advanced

🔎 Lookup & ReferenceAll Versions

Extracts data stored in a PivotTable report based on specified structure.

Formula Syntax

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)

Example:=GETPIVOTDATA("Sales", A3, "Region", "North")

AREAS

🔎 Lookup & ReferenceAll Versions

Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.

Formula Syntax

=AREAS(reference)

Example:=AREAS((A1:B2, C3:D4))

SHEET

🔎 Lookup & ReferenceExcel 2013+

Returns the sheet number of the reference sheet.

Formula Syntax

=SHEET([value])

Example:=SHEET(Sheet2!A1)

SHEETS

🔎 Lookup & ReferenceExcel 2013+

Returns the number of sheets in a reference.

Formula Syntax

=SHEETS([reference])

Example:=SHEETS()

HYPERLINK

★ Popular Advanced

🔎 Lookup & ReferenceAll Versions

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.

Formula Syntax

=HYPERLINK(link_location, [friendly_name])

Example:=HYPERLINK("https://excel.rean.me", "Visit Site")

WEBSERVICE

★ Popular Advanced

🔎 Lookup & ReferenceExcel 2013+

Returns data from a Web service on the Internet or Intranet.

Formula Syntax

=WEBSERVICE(url)

Example:=WEBSERVICE("https://api.example.com/data")

FILTERXML

★ Popular Advanced

🔎 Lookup & ReferenceExcel 2013+

Returns specific data from XML content by using a specified XPath.

Formula Syntax

=FILTERXML(xml, xpath)

Example:=FILTERXML(A2, "//title")

ENCODEURL

🔎 Lookup & ReferenceExcel 2013+

Returns a URL-encoded string.

Formula Syntax

=ENCODEURL(text)

Example:=ENCODEURL("Hello World & Special!")

📊Statistical Advanced Functions

13 functions

STDEV.S

📊 StatisticalExcel 2010+

Estimates standard deviation based on a sample of numbers.

Formula Syntax

=STDEV.S(number1, [number2], ...)

Example:=STDEV.S(C2:C100)

VAR.S

📊 StatisticalExcel 2010+

Estimates variance based on a sample of numbers.

Formula Syntax

=VAR.S(number1, [number2], ...)

Example:=VAR.S(C2:C100)

PERCENTILE.INC

📊 StatisticalExcel 2010+

Returns the k-th percentile of values in a range, where k is in the range 0..1 inclusive.

Formula Syntax

=PERCENTILE.INC(array, k)

Example:=PERCENTILE.INC(C2:C100, 0.9)

QUARTILE.INC

📊 StatisticalExcel 2010+

Returns the quartile of a data set, based on percentile values from 0..1 inclusive.

Formula Syntax

=QUARTILE.INC(array, quart)

Example:=QUARTILE.INC(C2:C100, 3)

FREQUENCY

📊 StatisticalAll Versions

Calculates how often values occur within a range of values, returning a vertical array of numbers.

Formula Syntax

=FREQUENCY(data_array, bins_array)

Example:=FREQUENCY(A2:A50, C2:C5)

STDEV.P

📊 StatisticalExcel 2010+

Calculates standard deviation based on the entire population given as arguments.

Formula Syntax

=STDEV.P(number1, [number2], ...)

Example:=STDEV.P(C2:C100)

VAR.P

📊 StatisticalExcel 2010+

Calculates variance based on the entire population.

Formula Syntax

=VAR.P(number1, [number2], ...)

Example:=VAR.P(C2:C100)

RANK.AVG

📊 StatisticalExcel 2010+

Returns the rank of a number in a list of numbers; if more than one value has the same rank, the average rank is returned.

Formula Syntax

=RANK.AVG(number, ref, [order])

Example:=RANK.AVG(B2, B$2:B$100, 0)

PERCENTRANK.INC

📊 StatisticalExcel 2010+

Returns the rank of a value in a data set as a percentage (0..1 inclusive) of the data set.

Formula Syntax

=PERCENTRANK.INC(array, x, [significance])

Example:=PERCENTRANK.INC(C2:C100, B2)

CORREL

📊 StatisticalAll Versions

Returns the correlation coefficient between two cell ranges.

Formula Syntax

=CORREL(array1, array2)

Example:=CORREL(A2:A50, B2:B50)

COVARIANCE.S

📊 StatisticalExcel 2010+

Returns sample covariance, the average of the products of deviations for each data point pair in two data sets.

Formula Syntax

=COVARIANCE.S(array1, array2)

Example:=COVARIANCE.S(A2:A50, B2:B50)

GEOMEAN

📊 StatisticalAll Versions

Returns the geometric mean of an array or range of positive data.

Formula Syntax

=GEOMEAN(number1, [number2], ...)

Example:=GEOMEAN(B2:B10)

HARMEAN

📊 StatisticalAll Versions

Returns the harmonic mean of a data set.

Formula Syntax

=HARMEAN(number1, [number2], ...)

Example:=HARMEAN(B2:B10)

💰Financial Advanced Functions

25 functions

PV

💰 FinancialAll Versions

Returns the present value of an investment (total amount that a series of future payments is worth now).

Formula Syntax

=PV(rate, nper, pmt, [fv], [type])

Example:=PV(0.05/12, 60, -300)

IRR

★ Popular Advanced

💰 FinancialAll Versions

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

Formula Syntax

=IRR(values, [guess])

Example:=IRR(B2:B10)

RATE

💰 FinancialAll Versions

Returns the interest rate per period of an annuity or loan.

Formula Syntax

=RATE(nper, pmt, pv, [fv], [type], [guess])

Example:=RATE(36, -500, 15000)

IPMT

💰 FinancialAll Versions

Calculates the interest payment for an investment for a given period.

Formula Syntax

=IPMT(rate, per, nper, pv, [fv], [type])

Example:=IPMT(0.06/12, 1, 360, -200000)

PPMT

💰 FinancialAll Versions

Calculates the payment on the principal for an investment for a given period.

Formula Syntax

=PPMT(rate, per, nper, pv, [fv], [type])

Example:=PPMT(0.06/12, 1, 360, -200000)

XNPV

★ Popular Advanced

💰 FinancialAll Versions

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

Formula Syntax

=XNPV(rate, values, dates)

Example:=XNPV(0.08, B2:B10, A2:A10)

NPER

💰 FinancialAll Versions

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Formula Syntax

=NPER(rate, pmt, pv, [fv], [type])

Example:=NPER(0.06/12, -500, 10000)

CUMIPMT

💰 FinancialAll Versions

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

Formula Syntax

=CUMIPMT(rate, nper, pv, start_period, end_period, type)

Example:=CUMIPMT(0.05/12, 360, 200000, 1, 12, 0)

CUMPRINC

💰 FinancialAll Versions

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

Formula Syntax

=CUMPRINC(rate, nper, pv, start_period, end_period, type)

Example:=CUMPRINC(0.05/12, 360, 200000, 1, 12, 0)

EFFECT

💰 FinancialAll Versions

Calculates the effective annual interest rate given nominal rate and compounding periods per year.

Formula Syntax

=EFFECT(nominal_rate, npery)

Example:=EFFECT(0.05, 12)

NOMINAL

💰 FinancialAll Versions

Calculates the nominal annual interest rate given effective rate and compounding periods per year.

Formula Syntax

=NOMINAL(effect_rate, npery)

Example:=NOMINAL(0.0512, 12)

SLN

💰 FinancialAll Versions

Returns the straight-line depreciation of an asset for one period.

Formula Syntax

=SLN(cost, salvage, life)

Example:=SLN(30000, 7500, 10)

DDB

💰 FinancialAll Versions

Returns the depreciation of an asset for a specified period using the double-declining balance method.

Formula Syntax

=DDB(cost, salvage, life, period, [factor])

Example:=DDB(24000, 3000, 10, 1)

FV

★ Popular Advanced

💰 FinancialAll Versions

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Formula Syntax

=FV(rate, nper, pmt, [pv], [type])

Example:=FV(0.05/12, 60, -200, -1000)

PMT

★ Popular Advanced

💰 FinancialAll Versions

Calculates the payment for a loan based on constant payments and a constant interest rate.

Formula Syntax

=PMT(rate, nper, pv, [fv], [type])

Example:=PMT(0.06/12, 360, 200000)

NPV

★ Popular Advanced

💰 FinancialAll Versions

Calculates the net present value of an investment by using a discount rate and a series of future payments and income.

Formula Syntax

=NPV(rate, value1, [value2], ...)

Example:=NPV(0.08, C2:C10)

MIRR

💰 FinancialAll Versions

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

Formula Syntax

=MIRR(values, finance_rate, reinvest_rate)

Example:=MIRR(A2:A10, 0.1, 0.12)

XIRR

★ Popular Advanced

💰 FinancialAll Versions

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

Formula Syntax

=XIRR(values, dates, [guess])

Example:=XIRR(B2:B10, A2:A10)

RRI

💰 FinancialExcel 2013+

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

Formula Syntax

=RRI(nper, pv, fv)

Example:=RRI(8, 10000, 20000)

PDURATION

💰 FinancialExcel 2013+

Returns the number of periods required by an investment to reach a specified value.

Formula Syntax

=PDURATION(rate, pv, fv)

Example:=PDURATION(0.025, 2000, 4000)

SYD

💰 FinancialAll Versions

Returns the sum-of-years' digits depreciation of an asset for a specified period.

Formula Syntax

=SYD(cost, salvage, life, per)

Example:=SYD(30000, 7500, 10, 1)

DB

💰 FinancialAll Versions

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

Formula Syntax

=DB(cost, salvage, life, period, [month])

Example:=DB(100000, 10000, 5, 1)

VDB

💰 FinancialAll Versions

Returns the depreciation of an asset for any period using a variable declining balance method.

Formula Syntax

=VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

Example:=VDB(10000, 1000, 60, 0, 12)

AMORDEGRC

💰 FinancialAll Versions

Returns the depreciation for each accounting period using a depreciation coefficient.

Formula Syntax

=AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

Example:=AMORDEGRC(2400, DATE(2025,8,19), DATE(2025,12,31), 300, 1, 0.15, 1)

AMORLINC

💰 FinancialAll Versions

Returns the depreciation for each accounting period using French accounting rules.

Formula Syntax

=AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])

Example:=AMORLINC(2400, DATE(2025,8,19), DATE(2025,12,31), 300, 1, 0.15, 1)

🧮Math & Trig Advanced Functions

54 functions

CEILING

🧮 Math & TrigAll Versions

Rounds a number up, away from zero, to the nearest multiple of significance.

Formula Syntax

=CEILING(number, significance)

Example:=CEILING(2.5, 1)

FLOOR

🧮 Math & TrigAll Versions

Rounds a number down, toward zero, to the nearest multiple of significance.

Formula Syntax

=FLOOR(number, significance)

Example:=FLOOR(2.5, 1)

SUMSQ

🧮 Math & TrigAll Versions

Returns the sum of the squares of the arguments.

Formula Syntax

=SUMSQ(number1, [number2], ...)

Example:=SUMSQ(3, 4)

EXP

🧮 Math & TrigAll Versions

Returns e raised to the power of a given number.

Formula Syntax

=EXP(number)

Example:=EXP(2)

LN

🧮 Math & TrigAll Versions

Returns the natural logarithm of a number.

Formula Syntax

=LN(number)

Example:=LN(2.7182818)

LOG

🧮 Math & TrigAll Versions

Returns the logarithm of a number to a specified base.

Formula Syntax

=LOG(number, [base])

Example:=LOG(100, 10)

LOG10

🧮 Math & TrigAll Versions

Returns the base-10 logarithm of a number.

Formula Syntax

=LOG10(number)

Example:=LOG10(1000)

PI

🧮 Math & TrigAll Versions

Returns the mathematical constant Pi accurate to 15 digits (3.14159265358979).

Formula Syntax

=PI()

Example:=PI()

CEILING.MATH

🧮 Math & TrigExcel 2013+

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

Formula Syntax

=CEILING.MATH(number, [significance], [mode])

Example:=CEILING.MATH(4.3)

FLOOR.MATH

🧮 Math & TrigExcel 2013+

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

Formula Syntax

=FLOOR.MATH(number, [significance], [mode])

Example:=FLOOR.MATH(4.8)

ACOS

🧮 Math & TrigAll Versions

Returns the arccosine of a number, in radians (0 to PI).

Formula Syntax

=ACOS(number)

Example:=ACOS(-0.5)

ACOSH

🧮 Math & TrigAll Versions

Returns the inverse hyperbolic cosine of a number.

Formula Syntax

=ACOSH(number)

Example:=ACOSH(10)

ACOT

🧮 Math & TrigExcel 2013+

Returns the arccotangent of a number, in radians.

Formula Syntax

=ACOT(number)

Example:=ACOT(2)

ACOTH

🧮 Math & TrigExcel 2013+

Returns the inverse hyperbolic cotangent of a number.

Formula Syntax

=ACOTH(number)

Example:=ACOTH(6)

ASIN

🧮 Math & TrigAll Versions

Returns the arcsine of a number, in radians (-PI/2 to PI/2).

Formula Syntax

=ASIN(number)

Example:=ASIN(0.5)

ASINH

🧮 Math & TrigAll Versions

Returns the inverse hyperbolic sine of a number.

Formula Syntax

=ASINH(number)

Example:=ASINH(10)

ATAN

🧮 Math & TrigAll Versions

Returns the arctangent of a number, in radians (-PI/2 to PI/2).

Formula Syntax

=ATAN(number)

Example:=ATAN(1)

ATAN2

🧮 Math & TrigAll Versions

Returns the arctangent from x- and y-coordinates, in radians (-PI to PI).

Formula Syntax

=ATAN2(x_num, y_num)

Example:=ATAN2(1, 1)

ATANH

🧮 Math & TrigAll Versions

Returns the inverse hyperbolic tangent of a number.

Formula Syntax

=ATANH(number)

Example:=ATANH(0.5)

COS

🧮 Math & TrigAll Versions

Returns the cosine of the given angle in radians.

Formula Syntax

=COS(number)

Example:=COS(PI())

COSH

🧮 Math & TrigAll Versions

Returns the hyperbolic cosine of a number.

Formula Syntax

=COSH(number)

Example:=COSH(4)

COT

🧮 Math & TrigExcel 2013+

Returns the cotangent of an angle specified in radians.

Formula Syntax

=COT(number)

Example:=COT(0.75)

COTH

🧮 Math & TrigExcel 2013+

Returns the hyperbolic cotangent of a hyperbolic angle.

Formula Syntax

=COTH(number)

Example:=COTH(2)

CSC

🧮 Math & TrigExcel 2013+

Returns the cosecant of an angle specified in radians.

Formula Syntax

=CSC(number)

Example:=CSC(1.5)

CSCH

🧮 Math & TrigExcel 2013+

Returns the hyperbolic cosecant of an angle.

Formula Syntax

=CSCH(number)

Example:=CSCH(1.5)

SEC

🧮 Math & TrigExcel 2013+

Returns the secant of an angle specified in radians.

Formula Syntax

=SEC(number)

Example:=SEC(45)

SECH

🧮 Math & TrigExcel 2013+

Returns the hyperbolic secant of an angle.

Formula Syntax

=SECH(number)

Example:=SECH(45)

SIN

🧮 Math & TrigAll Versions

Returns the sine of the given angle in radians.

Formula Syntax

=SIN(number)

Example:=SIN(PI()/2)

SINH

🧮 Math & TrigAll Versions

Returns the hyperbolic sine of a number.

Formula Syntax

=SINH(number)

Example:=SINH(2)

TAN

🧮 Math & TrigAll Versions

Returns the tangent of the given angle in radians.

Formula Syntax

=TAN(number)

Example:=TAN(PI()/4)

TANH

🧮 Math & TrigAll Versions

Returns the hyperbolic tangent of a number.

Formula Syntax

=TANH(number)

Example:=TANH(0.5)

DEGREES

🧮 Math & TrigAll Versions

Converts radians into degrees.

Formula Syntax

=DEGREES(angle)

Example:=DEGREES(PI())

RADIANS

🧮 Math & TrigAll Versions

Converts degrees to radians.

Formula Syntax

=RADIANS(angle)

Example:=RADIANS(180)

GCD

🧮 Math & TrigAll Versions

Returns the greatest common divisor of two or more integers.

Formula Syntax

=GCD(number1, [number2], ...)

Example:=GCD(24, 36)

LCM

🧮 Math & TrigAll Versions

Returns the least common multiple of integers.

Formula Syntax

=LCM(number1, [number2], ...)

Example:=LCM(4, 6)

FACT

🧮 Math & TrigAll Versions

Returns the factorial of a number.

Formula Syntax

=FACT(number)

Example:=FACT(5)

FACTDOUBLE

🧮 Math & TrigAll Versions

Returns the double factorial of a number.

Formula Syntax

=FACTDOUBLE(number)

Example:=FACTDOUBLE(6)

COMBIN

🧮 Math & TrigAll Versions

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

Formula Syntax

=COMBIN(number, number_chosen)

Example:=COMBIN(8, 2)

COMBINA

🧮 Math & TrigExcel 2013+

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

Formula Syntax

=COMBINA(number, number_chosen)

Example:=COMBINA(4, 3)

PERMUT

🧮 Math & TrigAll Versions

Returns the number of permutations for a given number of objects selected.

Formula Syntax

=PERMUT(number, number_chosen)

Example:=PERMUT(6, 2)

PERMUTATIONA

🧮 Math & TrigExcel 2013+

Returns the number of permutations for a given number of objects with repetitions.

Formula Syntax

=PERMUTATIONA(number, number_chosen)

Example:=PERMUTATIONA(3, 2)

MULTINOMIAL

🧮 Math & TrigAll Versions

Returns the multinomial of a set of numbers.

Formula Syntax

=MULTINOMIAL(number1, [number2], ...)

Example:=MULTINOMIAL(2, 3, 4)

SUMX2MY2

🧮 Math & TrigAll Versions

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

Formula Syntax

=SUMX2MY2(array_x, array_y)

Example:=SUMX2MY2(A2:A10, B2:B10)

SUMX2PY2

🧮 Math & TrigAll Versions

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

Formula Syntax

=SUMX2PY2(array_x, array_y)

Example:=SUMX2PY2(A2:A10, B2:B10)

SUMXMY2

🧮 Math & TrigAll Versions

Returns the sum of squares of differences of corresponding values in two arrays.

Formula Syntax

=SUMXMY2(array_x, array_y)

Example:=SUMXMY2(A2:A10, B2:B10)

CEILING.PRECISE

🧮 Math & TrigExcel 2010+

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

Formula Syntax

=CEILING.PRECISE(number, [significance])

Example:=CEILING.PRECISE(-3.2, 1)

FLOOR.PRECISE

🧮 Math & TrigExcel 2010+

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

Formula Syntax

=FLOOR.PRECISE(number, [significance])

Example:=FLOOR.PRECISE(-3.2, 1)

ISO.CEILING

🧮 Math & TrigExcel 2010+

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

Formula Syntax

=ISO.CEILING(number, [significance])

Example:=ISO.CEILING(-3.2, 1)

EVEN

🧮 Math & TrigAll Versions

Rounds a number up to the nearest even integer.

Formula Syntax

=EVEN(number)

Example:=EVEN(3)

ODD

🧮 Math & TrigAll Versions

Rounds a number up to the nearest odd integer.

Formula Syntax

=ODD(number)

Example:=ODD(2)

ROMAN

🧮 Math & TrigAll Versions

Converts an Arabic numeral to Roman numeral as text.

Formula Syntax

=ROMAN(number, [form])

Example:=ROMAN(499)

ARABIC

🧮 Math & TrigExcel 2013+

Converts a Roman numeral text string to an Arabic number.

Formula Syntax

=ARABIC(text)

Example:=ARABIC(MCMXCIX)

BASE

🧮 Math & TrigExcel 2013+

Converts a number into a text representation with the given radix (base).

Formula Syntax

=BASE(number, radix, [min_length])

Example:=BASE(255, 16)

DECIMAL

🧮 Math & TrigExcel 2013+

Converts a text representation of a number in a given base into a decimal number.

Formula Syntax

=DECIMAL(text, radix)

Example:=DECIMAL(FF, 16)

🔤Text Advanced Functions

1 function

VSTACK

★ Popular Advanced

🔤 TextAll Versions

Returns the specified number of characters from the start of a text string.

Formula Syntax

=LEFT(text, [num_chars])

Example:=LEFT(A2, 3)

🧾Database Advanced Functions

18 functions

DSUM

🧾 DatabaseAll Versions

Adds the numbers in a column of records in a list or database that match conditions specified.

Formula Syntax

=DSUM(database, field, criteria)

Example:=DEC2HEX(255)

DAVERAGE

★ Popular Advanced

🧾 DatabaseAll Versions

Averaging the values in a column of a list or database that match conditions you specify.

Formula Syntax

=DAVERAGE(database, field, criteria)

Example:=DAVERAGE(A1:E20, "Profit", G1:H2)

DCOUNT

🧾 DatabaseAll Versions

Counts the cells that contain numbers in a column of a database that match conditions you specify.

Formula Syntax

=DCOUNT(database, field, criteria)

Example:=DCOUNT(A1:E20, "Age", G1:H2)

DCOUNTA

🧾 DatabaseAll Versions

Counts nonblank cells in a column of a database that match conditions you specify.

Formula Syntax

=DCOUNTA(database, field, criteria)

Example:=DCOUNTA(A1:E20, "Name", G1:H2)

DMAX

🧾 DatabaseAll Versions

Returns the maximum value in a column of a database that matches specified conditions.

Formula Syntax

=DMAX(database, field, criteria)

Example:=DMAX(A1:E20, "Sales", G1:H2)

DMIN

🧾 DatabaseAll Versions

Returns the minimum value in a column of a database that matches specified conditions.

Formula Syntax

=DMIN(database, field, criteria)

Example:=DMIN(A1:E20, "Sales", G1:H2)

DPRODUCT

🧾 DatabaseAll Versions

Multiplies the values in a column of a database that match specified conditions.

Formula Syntax

=DPRODUCT(database, field, criteria)

Example:=DPRODUCT(A1:E20, "Yield", G1:H2)

DSTDEV

🧾 DatabaseAll Versions

Estimates standard deviation based on a sample from selected database entries.

Formula Syntax

=DSTDEV(database, field, criteria)

Example:=DSTDEV(A1:E20, "Score", G1:H2)

DSTDEVP

🧾 DatabaseAll Versions

Calculates standard deviation based on the entire population of selected database entries.

Formula Syntax

=DSTDEVP(database, field, criteria)

Example:=DSTDEVP(A1:E20, "Score", G1:H2)

DVAR

🧾 DatabaseAll Versions

Estimates variance based on a sample from selected database entries.

Formula Syntax

=DVAR(database, field, criteria)

Example:=DVAR(A1:E20, "Yield", G1:H2)

DVARP

🧾 DatabaseAll Versions

Calculates variance based on the entire population of selected database entries.

Formula Syntax

=DVARP(database, field, criteria)

Example:=DVARP(A1:E20, "Yield", G1:H2)

CUBEMEMBER

🧾 DatabaseExcel 2007+

Returns a member or tuple in a cube hierarchy. Validates that the member exists in the cube.

Formula Syntax

=CUBEMEMBER(connection, member_expression, [caption])

Example:=CUBEMEMBER("Sales", "[Geography].[State].&[CA]")

CUBEMEMBERPROPERTY

🧾 DatabaseExcel 2007+

Returns the value of a member property in the cube. Used to validate that a member name exists.

Formula Syntax

=CUBEMEMBERPROPERTY(connection, member_expression, property)

Example:=CUBEMEMBERPROPERTY("Sales", "[Store].[Store Name].&[1]", "Store Type")

CUBERANKEDMEMBER

🧾 DatabaseExcel 2007+

Returns the n-th, or ranked, member in a set. Used to return one or more elements in a set.

Formula Syntax

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

Example:=CUBERANKEDMEMBER("Sales", "[Product].[All Products].Children", 1)

CUBESET

🧾 DatabaseExcel 2007+

Defines a calculated set of members or tuples by sending a set expression to the cube on the server.

Formula Syntax

=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

Example:=CUBESET("Finance", "[Accounts].[Type].Children", "Account Types")

CUBESETCOUNT

🧾 DatabaseExcel 2007+

Returns the number of items in a set.

Formula Syntax

=CUBESETCOUNT(set)

Example:=CUBESETCOUNT(A2)

CUBEVALUE

★ Popular Advanced

🧾 DatabaseExcel 2007+

Returns an aggregated value from an Online Analytical Processing (OLAP) cube.

Formula Syntax

=CUBEVALUE(connection, [member_expression1], ...)

Example:=CUBEVALUE("Sales", "[Measures].[Profit]", "[Time].[2025]")

CUBEKPIMEMBER

🧾 DatabaseExcel 2007+

Returns a Key Performance Indicator (KPI) property and displays the KPI name in the cell.

Formula Syntax

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

Example:=CUBEKPIMEMBER("Sales", "Growth", 1)

🧪Engineering Advanced Functions

43 functions

DEC2BIN

🧪 EngineeringAll Versions

Converts a decimal number to binary.

Formula Syntax

=DEC2BIN(number, [places])

Example:=DEC2BIN(9)

BITAND

🧪 EngineeringExcel 2013+

Returns a bitwise 'AND' of two numbers.

Formula Syntax

=BITAND(number1, number2)

Example:=BITAND(5, 3)

BITOR

🧪 EngineeringExcel 2013+

Returns a bitwise 'OR' of two numbers.

Formula Syntax

=BITOR(number1, number2)

Example:=BITOR(5, 3)

CONVERT

★ Popular Advanced

🧪 EngineeringAll Versions

Converts a number from one measurement system to another.

Formula Syntax

=CONVERT(number, from_unit, to_unit)

Example:=CONVERT(100, "F", "C")

BIN2DEC

🧪 EngineeringAll Versions

Converts a binary number to decimal.

Formula Syntax

=BIN2DEC(number)

Example:=BIN2DEC(1100)

BIN2HEX

🧪 EngineeringAll Versions

Converts a binary number to hexadecimal.

Formula Syntax

=BIN2HEX(number, [places])

Example:=BIN2HEX(1111)

BIN2OCT

🧪 EngineeringAll Versions

Converts a binary number to octal.

Formula Syntax

=BIN2OCT(number, [places])

Example:=BIN2OCT(1001)

DEC2HEX

🧪 EngineeringAll Versions

Converts a decimal number to hexadecimal.

Formula Syntax

=DEC2HEX(number, [places])

Example:=DEC2HEX(255)

DEC2OCT

🧪 EngineeringAll Versions

Converts a decimal number to octal.

Formula Syntax

=DEC2OCT(number, [places])

Example:=DEC2OCT(58)

HEX2BIN

🧪 EngineeringAll Versions

Converts a hexadecimal number to binary.

Formula Syntax

=HEX2BIN(number, [places])

Example:=HEX2BIN("F")

HEX2DEC

🧪 EngineeringAll Versions

Converts a hexadecimal number to decimal.

Formula Syntax

=HEX2DEC(number)

Example:=HEX2DEC("FF")

HEX2OCT

🧪 EngineeringAll Versions

Converts a hexadecimal number to octal.

Formula Syntax

=HEX2OCT(number, [places])

Example:=HEX2OCT("FF")

OCT2BIN

🧪 EngineeringAll Versions

Converts an octal number to binary.

Formula Syntax

=OCT2BIN(number, [places])

Example:=OCT2BIN(77)

OCT2DEC

🧪 EngineeringAll Versions

Converts an octal number to decimal.

Formula Syntax

=OCT2DEC(number)

Example:=OCT2DEC(77)

OCT2HEX

🧪 EngineeringAll Versions

Converts an octal number to hexadecimal.

Formula Syntax

=OCT2HEX(number, [places])

Example:=OCT2HEX(77)

COMPLEX

🧪 EngineeringAll Versions

Converts real and imaginary coefficients into a complex number.

Formula Syntax

=COMPLEX(real_num, i_num, [suffix])

Example:=COMPLEX(3, 4)

IMABS

🧪 EngineeringAll Versions

Returns the absolute value (modulus) of a complex number.

Formula Syntax

=IMABS(inumber)

Example:=IMABS("3+4i")

IMAGINARY

🧪 EngineeringAll Versions

Returns the imaginary coefficient of a complex number.

Formula Syntax

=IMAGINARY(inumber)

Example:=IMAGINARY("3+4i")

IMARGUMENT

🧪 EngineeringAll Versions

Returns the argument theta, an angle expressed in radians.

Formula Syntax

=IMARGUMENT(inumber)

Example:=IMARGUMENT("3+4i")

IMCONJUGATE

🧪 EngineeringAll Versions

Returns the complex conjugate of a complex number.

Formula Syntax

=IMCONJUGATE(inumber)

Example:=IMCONJUGATE("3+4i")

IMCOS

🧪 EngineeringAll Versions

Returns the cosine of a complex number.

Formula Syntax

=IMCOS(inumber)

Example:=IMCOS("1+i")

IMCOSH

🧪 EngineeringExcel 2013+

Returns the hyperbolic cosine of a complex number.

Formula Syntax

=IMCOSH(inumber)

Example:=IMCOSH("1+i")

IMCOT

🧪 EngineeringExcel 2013+

Returns the cotangent of a complex number.

Formula Syntax

=IMCOT(inumber)

Example:=IMCOT("1+i")

IMCSC

🧪 EngineeringExcel 2013+

Returns the cosecant of a complex number.

Formula Syntax

=IMCSC(inumber)

Example:=IMCSC("1+i")

IMCSCH

🧪 EngineeringExcel 2013+

Returns the hyperbolic cosecant of a complex number.

Formula Syntax

=IMCSCH(inumber)

Example:=IMCSCH("1+i")

IMDIV

🧪 EngineeringAll Versions

Returns the quotient of two complex numbers.

Formula Syntax

=IMDIV(inumber1, inumber2)

Example:=IMDIV("-2+3i", "1+2i")

IMEXP

🧪 EngineeringAll Versions

Returns the exponential of a complex number.

Formula Syntax

=IMEXP(inumber)

Example:=IMEXP("1+i")

IMLN

🧪 EngineeringAll Versions

Returns the natural logarithm of a complex number.

Formula Syntax

=IMLN(inumber)

Example:=IMLN("3+4i")

IMLOG10

🧪 EngineeringAll Versions

Returns the base-10 logarithm of a complex number.

Formula Syntax

=IMLOG10(inumber)

Example:=IMLOG10("3+4i")

IMLOG2

🧪 EngineeringAll Versions

Returns the base-2 logarithm of a complex number.

Formula Syntax

=IMLOG2(inumber)

Example:=IMLOG2("3+4i")

IMPOWER

🧪 EngineeringAll Versions

Returns a complex number raised to an integer power.

Formula Syntax

=IMPOWER(inumber, number)

Example:=IMPOWER("2+3i", 2)

IMPRODUCT

🧪 EngineeringAll Versions

Returns the product of complex numbers.

Formula Syntax

=IMPRODUCT(inumber1, [inumber2], ...)

Example:=IMPRODUCT("3+4i", "1-2i")

IMREAL

🧪 EngineeringAll Versions

Returns the real coefficient of a complex number.

Formula Syntax

=IMREAL(inumber)

Example:=IMREAL("3+4i")

IMSEC

🧪 EngineeringExcel 2013+

Returns the secant of a complex number.

Formula Syntax

=IMSEC(inumber)

Example:=IMSEC("1+i")

IMSECH

🧪 EngineeringExcel 2013+

Returns the hyperbolic secant of a complex number.

Formula Syntax

=IMSECH(inumber)

Example:=IMSECH("1+i")

IMSIN

🧪 EngineeringAll Versions

Returns the sine of a complex number.

Formula Syntax

=IMSIN(inumber)

Example:=IMSIN("1+i")

IMSINH

🧪 EngineeringExcel 2013+

Returns the hyperbolic sine of a complex number.

Formula Syntax

=IMSINH(inumber)

Example:=IMSINH("1+i")

IMSUB

🧪 EngineeringAll Versions

Returns the difference of two complex numbers.

Formula Syntax

=IMSUB(inumber1, inumber2)

Example:=IMSUB("13+4i", "5+2i")

IMSUM

🧪 EngineeringAll Versions

Returns the sum of complex numbers.

Formula Syntax

=IMSUM(inumber1, [inumber2], ...)

Example:=IMSUM("3+4i", "5-2i")

IMTAN

🧪 EngineeringExcel 2013+

Returns the tangent of a complex number.

Formula Syntax

=IMTAN(inumber)

Example:=IMTAN("4+3i")

BITLSHIFT

🧪 EngineeringExcel 2013+

Returns a number shifted left by specified number of bits.

Formula Syntax

=BITLSHIFT(number, shift_amount)

Example:=BITLSHIFT(4, 2)

BITRSHIFT

🧪 EngineeringExcel 2013+

Returns a number shifted right by specified number of bits.

Formula Syntax

=BITRSHIFT(number, shift_amount)

Example:=BITRSHIFT(16, 2)

BITXOR

🧪 EngineeringExcel 2013+

Returns a bitwise 'XOR' of two numbers.

Formula Syntax

=BITXOR(number1, number2)

Example:=BITXOR(5, 3)

⚖️Logical Advanced Functions

8 functions

ERROR.TYPE

⚖️ LogicalAll Versions

Returns a number corresponding to an error type.

Formula Syntax

=ERROR.TYPE(error_val)

Example:=ERROR.TYPE(A2)

INFO

⚖️ LogicalAll Versions

Returns information about the current operating environment.

Formula Syntax

=INFO(type_text)

Example:=INFO("osversion")

ISREF

⚖️ LogicalAll Versions

Checks whether a value is a reference.

Formula Syntax

=ISREF(value)

Example:=ISREF(A2)

ISFORMULA

★ Popular Advanced

⚖️ LogicalExcel 2013+

Checks whether a reference is to a cell containing a formula.

Formula Syntax

=ISFORMULA(reference)

Example:=ISFORMULA(A2)

ISEMAIL

⚖️ LogicalExcel 365 / Web

Checks whether a value is a valid email address.

Formula Syntax

=ISEMAIL(value)

Example:=ISEMAIL("user@example.com")

N

⚖️ LogicalAll Versions

Returns a value converted to a number.

Formula Syntax

=N(value)

Example:=N(A2)

NA

⚖️ LogicalAll Versions

Returns the error value #N/A.

Formula Syntax

=NA()

Example:=NA()

TYPE

⚖️ LogicalAll Versions

Returns a number indicating the data type of a value.

Formula Syntax

=TYPE(value)

Example:=TYPE(A2)

Need Refreshing On Basics?

Review Essential Basic Excel Functions

Learn SUM, AVERAGE, COUNT, IF, VLOOKUP, CONCATENATE, TODAY, and beginner formulas.

Explore Basic Functions →