Advanced Excel Functions - Quick Guide
Advanced Excel - Compatibility Functions
In Excel 2010 or later, the functions listed in this category were replaced with new functions that provide improved accuracy and have names that reflect their usage better. The new functions can be found in Statistical functions and Math and trigonometry functions. If backward compatibility is not required, you should start using the new functions.
You can still use these earlier versions of functions for compatibility with earlier versions of Excel. If you are using Excel 2007, you will find these functions in the Statistical or Math & Trig categories on the Formulas tab.
Compatibility Functions
The following table lists all the Compatibility functions −
S.No. | Function and Description |
---|---|
1 | BETADIST
Returns the cumulative beta probability density function |
2 | BETAINV
Returns the inverse of the cumulative beta probability density function |
3 | BINOMDIST
Returns the individual term binomial distribution probability |
4 | CEILING
Rounds a number to the nearest integer or to the nearest multiple of significance |
5 | CHIDIST
Returns the one-tailed probability of the chi-squared distribution |
6 | CHIINV
Returns the inverse of the one-tailed probability of the chi-squared distribution |
7 | CHITEST
Returns the test for independence |
8 | CONFIDENCE
Returns the confidence interval for a population mean |
9 | COVAR
Returns covariance, the average of the products of paired deviations |
10 | CRITBINOM
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
11 | EXPONDIST
Returns the exponential distribution |
12 | FDIST
Returns the F probability distribution |
13 | FINV
Returns the inverse of the F probability distribution |
14 | FLOOR
Rounds a number down, toward 0 |
15 | FTEST
Returns the result of an F-Test |
16 | GAMMADIST
Returns the gamma distribution |
17 | GAMMAINV
Returns the inverse of the gamma cumulative distribution |
18 | HYPGEOMDIST
Returns the hypergeometric distribution |
19 | LOGINV
Returns the inverse of the lognormal distribution |
20 | LOGNORMDIST
Returns the cumulative lognormal distribution |
21 | MODE
Returns the most common value in a data set |
22 | NEGBINOMDIST
Returns the negative binomial distribution |
23 | NORMDIST
Returns the normal cumulative distribution |
24 | NORMINV
Returns the inverse of the normal cumulative distribution |
25 | NORMSDIST
Returns the standard normal cumulative distribution |
26 | NORMSINV
Returns the inverse of the standard normal cumulative distribution |
27 | PERCENTILE
Returns the kth percentile of values in a range |
28 | PERCENTRANK
Returns the percentage rank of a value in a data set |
29 | POISSON
Returns the Poisson distribution |
30 | QUARTILE
Returns the quartile of a data set |
31 | RANK
Returns the rank of a number in a list of numbers |
32 | STDEV
Estimates standard deviation based on a sample, ignoring text and logical values |
33 | STDEVP
Calculates standard deviation based on the entire population, ignoring text and logical values |
34 | TDIST
Returns the student’s t-distribution |
35 | TINV
Returns the inverse of the student’s t-distribution |
36 | TTEST
Returns the probability associated with a student’s t-Test |
37 | VAR
Estimates variance based on a sample, ignoring logical values and text |
38 | VARP
Calculates variance based on the entire population, ignoring logical values and text |
39 | WEIBULL
Returns the Weibull distribution |
40 | ZTEST
Returns the two-tailed P-value of a z-test |
Advanced Excel - Cube Functions
The Excel Cube functions enable data from OLAP cubes to be brought into Excel to perform calculations. These functions are supported with a connection to Microsoft SQL Server 2005 Analysis Services or later data source.
As PowerPivot creates a data source, which is compatible with OLAP cubes, it can also be used with these functions.
Cube Functions
The following table lists all the Cube functions −
S.No. | Function and Description |
---|---|
1 | CUBEKPIMEMBER
Returns a key performance indicator name, property, and measure, and displays the name and property in the cell. |
2 | CUBEMEMBER
Returns a member or tuple in a cube hierarchy. |
3 | CUBEMEMBERPROPERTY
Returns the value of a member property in the cube. |
4 | CUBERANKEDMEMBER
Returns the nth, or ranked, member in a set. |
5 | CUBESET
Defines a calculated set of members or tuples by sending a set expression to the cube on the server. |
6 | CUBESETCOUNT
Returns the number of items in a set. |
7 | CUBEVALUE
Returns an aggregated value from a cube. |
Advanced Excel - Database Functions
The Excel Database functions work with an Excel Database. This typically takes the form of a large table of Data, where each row in the table stores an individual record. Each column in the Worksheet table stores a different field for each record.
The Database functions perform basic operations, such as Sum, Average, Count, etc., and additionally use criteria arguments, that allow you to perform the calculation only for a specified subset of the records in your Database. Other records in the Database are ignored.
Database Functions
The following table lists all the Database functions −
S.No. | Function and Description |
---|---|
1 | DAVERAGE
Averages the values in a column of a list or database that match conditions you specify. |
2 | DCOUNT
Counts the cells that contain numbers in a column of a list or database that match conditions you specify. |
3 | DCOUNTA
Counts the nonblank cells in a column of a list or database that match conditions you specify. |
4 | DGET
Returns a single value from a column of a list or database that matches conditions you specify. |
5 | DMAX
Returns the largest number in a column of a list or database that matches conditions you specify. |
6 | DMIN
Returns the smallest number in a column of a list or database that matches conditions you specify. |
7 | DPRODUCT
Multiplies the values in a column of a list or database that match conditions you specify. |
8 | DSTDEV
Estimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify. |
9 | DSTDEVP
Calculates the standard deviation of a population based on the entire population, using the numbers in a column of a list or database that match conditions you specify. |
10 | DSUM
Adds the numbers in a column of a list or database that match conditions you specify. |
11 | DVAR
Estimates the variance of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify. |
12 | DVARP
Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match conditions you specify. |
Advanced Excel - Date & Time Functions
Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times.
Some of the Excel Date & Time functions are new to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. You can find this information in the applicability section of the function.
Date and Time Functions
The following table lists all the Date & Time functions −
S.No. | Function and Description |
---|---|
1 | DATE
Returns the serial number of a particular date. |
2 | DATEDIF
Calculates the number of days, months, or years between two dates. |
3 | DATEVALUE
Converts a date in the form of text to a serial number. |
4 | DAY
Converts a serial number to a day of the month. |
5 | DAYS
Returns the number of days between two dates. |
6 | DAYS360
Calculates the number of days between two dates, based on a 360-day year. |
7 | EDATE
Returns the serial number of the date that is the indicated number of months before or after the start date. |
8 | EOMONTH
Returns the serial number of the last day of the month before or after a specified number of months. |
9 | HOUR
Converts a serial number to an hour. |
10 | ISOWEEKNUM
Returns the number of the ISO week number of the year for a given date. |
11 | MINUTE
Converts a serial number to a minute. |
12 | MONTH
Converts a serial number to a month. |
13 | NETWORKDAYS
Returns the number of whole workdays between two dates. |
14 | NETWORKDAYS.INTL
Returns the number of whole workdays between two dates (international version). |
15 | NOW
Returns the serial number of the current date and time. |
16 | SECOND
Converts a serial number to a second. |
17 | TIME
Returns the serial number of a particular time. |
18 | TIMEVALUE
Converts a time in the form of text to a serial number. |
19 | TODAY
Returns the serial number of today’s date. |
20 | WEEKDAY
Converts a serial number to a day of the week. |
21 | WEEKNUM
Returns the week number in the year. |
22 | WORKDAY
Returns the serial number of the date before or after a specified number of workdays. |
23 | WORKDAY.INTL
Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days. |
24 | YEAR
Converts a serial number to a year. |
25 | YEARFRAC
Returns the year fraction representing the number of whole days between start_date and end_date. |
Advanced Excel - Engineering Functions
The Excel Engineering functions perform the most commonly used Engineering calculations, many of which relate to Bessel functions, complex numbers, or converting between different bases.
Engineering Functions
The following table lists all the Engineering functions −
S.No. | Function and Description |
---|---|
1 | BESSELI
Returns the modified Bessel function In(x) |
2 | BESSELJ
Returns the Bessel function Jn(x) |
3 | BESSELK
Returns the modified Bessel function Kn(x) |
4 | BESSELY
Returns the Bessel function Yn(x) |
5 | BIN2DEC
Converts a binary number to decimal |
6 | BIN2HEX
Converts a binary number to hexadecimal |
7 | BIN2OCT
Converts a binary number to octal |
8 | BITAND
Returns a 'Bitwise And' of two numbers |
9 | BITLSHIFT
Returns a value number shifted left by shift_amount bits |
10 | BITOR
Returns a bitwise OR of 2 numbers |
11 | BITRSHIFT
Returns a value number shifted right by shift_amount bits |
12 | BITXOR
Returns a bitwise 'Exclusive Or' of two numbers |
13 | COMPLEX
Converts real and imaginary coefficients into a complex number |
14 | CONVERT
Converts a number from one measurement system to another |
15 | DEC2BIN
Converts a decimal number to binary |
16 | DEC2HEX
Converts a decimal number to hexadecimal |
17 | DEC2OCT
Converts a decimal number to octal |
18 | DELTA
Tests whether two values are equal |
19 | ERF
Returns the error function |
20 | ERF.PRECISE
Returns the error function |
21 | ERFC
Returns the complementary error function |
22 | ERFC.PRECISE
Returns the complementary error function |
23 | GESTEP
Tests whether a number is greater than a threshold value |
24 | HEX2BIN
Converts a hexadecimal number to binary |
25 | HEX2DEC
Converts a hexadecimal number to decimal |
26 | HEX2OCT
Converts a hexadecimal number to octal |
27 | IMABS
Returns the absolute value (modulus) of a complex number |
28 | IMAGINARY
Returns the imaginary coefficient of a complex number |
29 | IMARGUMENT
Returns the argument theta, an angle expressed in radians |
30 | IMCONJUGATE
Returns the complex conjugate of a complex number |
31 | IMCOS
Returns the cosine of a complex number |
32 | IMCOSH
Returns the hyperbolic cosine of a complex number |
33 | IMCOT
Returns the cotangent of a complex number |
34 | IMCSC
Returns the cosecant of a complex number |
35 | IMCSCH
Returns the hyperbolic cosecant of a complex number |
36 | IMDIV
Returns the quotient of two complex numbers |
37 | IMEXP
Returns the exponential of a complex number |
38 | IMLN
Returns the natural logarithm of a complex number |
39 | IMLOG2
Returns the base-2 logarithm of a complex number |
40 | IMLOG10
Returns the base-10 logarithm of a complex number |
41 | IMPOWER
Returns a complex number raised to an integer power |
42 | IMPRODUCT
Returns the product of complex numbers |
43 | IMREAL
Returns the real coefficient of a complex number |
44 | IMSEC
Returns the secant of a complex number |
45 | IMSECH
Returns the hyperbolic secant of a complex number |
46 | IMSIN
Returns the sine of a complex number |
47 | IMSINH
Returns the hyperbolic sine of a complex number |
48 | IMSQRT
Returns the square root of a complex number |
49 | IMSUB
Returns the difference of two complex numbers |
50 | IMSUM
Returns the sum of complex numbers |
51 | IMTAN
Returns the tangent of a complex number |
52 | OCT2BIN
Converts an octal number to binary |
53 | OCT2DEC
Converts an octal number to decimal |
54 | OCT2HEX
Converts an octal number to hexadecimal |
Advanced Excel - Financial Functions
Excel Financial functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and asset depreciation.
S.No. | Function and Description |
---|---|
1 | ACCRINT
Returns the accrued interest for a security that pays periodic interest |
2 | ACCRINTM
Returns the accrued interest for a security that pays interest at maturity |
3 | AMORDEGRC
Returns the depreciation for each accounting period |
4 | AMORLINC
Returns the depreciation for each accounting period (the depreciation coefficient depends on the life of the assets) |
5 | COUPDAYBS
Returns the number of days from the beginning of the coupon period to the settlement date |
6 | COUPDAYS
Returns the number of days in the coupon period that contains the settlement date |
7 | COUPDAYSNC
Returns the number of days from the settlement date to the next coupon date |
8 | COUPNCD
Returns the next coupon date after the settlement date |
9 | COUPNUM
Returns the number of coupons payable between the settlement date and maturity date |
10 | COUPPCD
Returns the previous coupon date before the settlement date |
11 | CUMIPMT
Returns the cumulative interest paid between two periods |
12 | CUMPRINC
Returns the cumulative principal paid on a loan between two periods |
13 | DB
Returns the depreciation of an asset for a specified period, using the fixed-declining-balance method |
14 | DDB
Returns the depreciation of an asset for a specified period, using the double-declining-balance method or some other method that you specify |
15 | DISC
Returns the discount rate for a security |
16 | DOLLARDE
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number |
17 | DOLLARFR
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction |
18 | DURATION
Returns the annual duration of a security with periodic interest payments |
19 | EFFECT
Returns the effective annual interest rate |
20 | FV
Returns the future value of an investment |
21 | FVSCHEDULE
Returns the future value of an initial principal after applying a series of compound interest rates |
22 | INTRATE
Returns the interest rate for a fully invested security |
23 | IPMT
Returns the interest payment for an investment for a given period |
24 | IRR
Returns the internal rate of return for a series of cash flows |
25 | ISPMT
Calculates the interest paid during a specific period of an investment |
26 | MDURATION
Returns the Macauley modified duration for a security with an assumed par value of $100 |
27 | MIRR
Returns the internal rate of return where positive and negative cash flows are financed at different rates |
28 | NOMINAL
Returns the annual nominal interest rate |
29 | NPER
Returns the number of periods for an investment |
30 | NPV
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate |
31 | ODDFPRICE
Returns the price per $100 face value of a security with an odd first period |
32 | ODDFYIELD
Returns the yield of a security with an odd first period |
33 | ODDLPRICE
Returns the price per $100 face value of a security with an odd last period |
34 | ODDLYIELD
Returns the yield of a security with an odd last period |
35 | PDURATION
Returns the number of periods required by an investment to reach a specified value |
36 | PMT
Returns the periodic payment for an annuity |
37 | PPMT
Returns the payment on the principal for an investment for a given period |
38 | PRICE
Returns the price per $100 face value of a security that pays periodic interest |
39 | PRICEDISC
Returns the price per $100 face value of a discounted security |
40 | PRICEMAT
Returns the price per $100 face value of a security that pays interest at maturity |
41 | PV
Returns the present value of an investment |
42 | RATE
Returns the interest rate per period of an annuity |
43 | RECEIVED
Returns the amount received at maturity for a fully invested security |
44 | RRI
Returns an equivalent interest rate for the growth of an investment |
45 | SLN
Returns the straight-line depreciation of an asset for one period |
46 | SYD
Returns the sum-of-years’ digits depreciation of an asset for a specified period |
47 | TBILLEQ
Returns the bond-equivalent yield for a Treasury bill |
48 | TBILLPRICE
Returns the price per $100 face value for a Treasury bill |
49 | TBILLYIELD
Returns the yield for a Treasury bill |
50 | VDB
Returns the depreciation of an asset for a specified or partial period using a declining-balance method |
51 | XIRR
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic |
52 | XNPV
Returns the net present value for a schedule of cash flows that is not necessarily periodic |
53 | YIELD
Returns the yield on a security that pays periodic interest |
54 | YIELDDISC
Returns the annual yield for a discounted security, for example, a Treasury bill |
55 | YIELDMAT
Returns the annual yield of a security that pays interest at maturity |
Advanced Excel - Information Functions
Information functions provide information about the content, formatting and location of cells in an Excel Worksheet.
Information Functions
The following table lists all the Information functions −
S.No. | Function and Description |
---|---|
1 | CELL
Returns information about the formatting, location, or contents of a cell |
2 | ERROR.TYPE
Returns a number corresponding to an error type |
3 | INFO
Returns information about the current operating environment |
4 | ISBLANK
Returns TRUE if the value is blank |
5 | ISERR
Returns TRUE if the value is any error value except #N/A |
6 | ISERROR
Returns TRUE if the value is any error value |
7 | ISEVEN
Returns TRUE if the number is even |
8 | ISFORMULA
Returns TRUE if there is a reference to a cell that contains a formula |
9 | ISLOGICAL
Returns TRUE if the value is a logical value |
10 | ISNA
Returns TRUE if the value is the #N/A error value |
11 | ISNONTEXT
Returns TRUE if the value is not text |
12 | ISNUMBER
Returns TRUE if the value is a number |
13 | ISODD
Returns TRUE if the number is odd |
14 | ISREF
Returns TRUE if the value is a reference |
15 | ISTEXT
Returns TRUE if the value is text |
16 | N
Returns a value converted to a number |
17 | NA
Returns the error value #N/A |
18 | SHEET
Returns the sheet number of the referenced sheet |
19 | SHEETS
Returns the number of sheets in a reference |
20 | TYPE
Returns a number indicating the data type of a value |
Advanced Excel - Logical Functions
Logical functions include the boolean operators and conditional tests, which will be an essential part of many working spreadsheets.
Logical Functions
The following table lists all the Logical functions −
S.No. | Function and Description |
---|---|
1 | AND
Returns TRUE if all its arguments are TRUE. |
2 | FALSE
Returns the logical value FALSE. |
3 | IF
Specifies a logical test to perform. |
4 | IFERROR
Returns a different result if the first argument evaluates to an error. |
5 | IFNA
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. |
6 | IFS
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. |
7 | NOT
Reverses the logic of its argument. |
8 | OR
Returns TRUE if any argument is TRUE. |
9 | SWITCH
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. |
10 | TRUE
Returns the logical value TRUE. |
11 | XOR
Returns a logical exclusive OR of all arguments. |
Lookup and Reference Functions
Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.
Lookup and Reference Functions
The following table lists all the Lookup & Reference functions −
S.No. | Function and Description |
---|---|
1 | ADDRESS
Returns a reference as text to a single cell in a worksheet |
2 | AREAS
Returns the number of areas in a reference |
3 | CHOOSE
Chooses a value from a list of values |
4 | COLUMN
Returns the column number of a reference |
5 | COLUMNS
Returns the number of columns in a reference |
6 | FORMULATEXT
Returns the formula at the given reference as text |
7 | GETPIVOTDATA
Returns data stored in a PivotTable |
8 | HLOOKUP
Searches for a value in the top row of a table and then returns a value in the same column from a row you specify in the table |
9 | HYPERLINK
Creates a shortcut that opens a document on your hard drive, a server, or the Internet |
10 | INDEX
Uses an index to choose a value from a reference or array |
11 | INDIRECT
Returns a reference indicated by a text value |
12 | LOOKUP
Returns a value either from a one-row or one-column range or from an array |
13 | MATCH
Returns the relative position of an item in an array |
14 | OFFSET
Returns a reference offset from a given reference |
15 | ROW
Returns the row number of a reference |
16 | ROWS
Returns the number of rows in a reference |
17 | RTD
Returns real-time data from a program that supports COM automation |
18 | TRANSPOSE
Returns the transpose of an array |
19 | VLOOKUP
Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table |
Math and Trigonometric Functions
The Excel Math & Trig functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios.
Some more math-related functions are also discussed in the Statistical functions and Engineering functions categories.
Math and Trigonometric Functions
The following table lists all the Math & Trigonometric functions −
S.No. | Function and Description |
---|---|
1 | ABS
Returns the absolute value of a number |
2 | ACOS
Returns the arccosine of a number |
3 | ACOSH
Returns the inverse hyperbolic cosine of a number |
4 | ACOT
Returns the arccotangent of a number |
5 | ACOTH
Returns the hyperbolic arccotangent of a number |
6 | AGGREGATE
Returns an aggregate in a list or database |
7 | ARABIC
Converts a Roman number to Arabic, as a number |
8 | ASIN
Returns the arcsine of a number |
9 | ASINH
Returns the inverse hyperbolic sine of a number |
10 | ATAN
Returns the arctangent of a number |
11 | ATAN2
Returns the arctangent from x and y coordinates |
12 | ATANH
Returns the inverse hyperbolic tangent of a number |
13 | BASE
Converts a number into a text representation with the given radix (base) |
14 | CEILING.MATH
Rounds a number up, to the nearest integer or to the nearest multiple of significance |
15 | COMBIN
Returns the number of combinations for a given number of objects |
16 | COMBINA
Returns the number of combinations with repetitions for a given number of items |
17 | COS
Returns the cosine of a number |
18 | COSH
Returns the hyperbolic cosine of a number |
19 | COT
Returns the cotangent of an angle |
20 | COTH
Returns the hyperbolic cotangent of a number |
21 | CSC
Returns the cosecant of an angle |
22 | CSCH
Returns the hyperbolic cosecant of an angle |
23 | DECIMAL
Converts a text representation of a number in a given base into a decimal number |
24 | DEGREES
Converts radians to degrees |
25 | EVEN
Rounds a number up to the nearest even integer |
26 | EXP
Returns e raised to the power of a given number |
27 | FACT
Returns the factorial of a number |
28 | FACTDOUBLE
Returns the double factorial of a number |
29 | FLOOR.MATH
Rounds a number down, to the nearest integer or to the nearest multiple of significance |
30 | GCD
Returns the greatest common divisor |
31 | INT
Rounds a number down to the nearest integer |
32 | LCM
Returns the least common multiple |
33 | LN
Returns the natural logarithm of a number |
34 | LOG
Returns the logarithm of a number to a specified base |
35 | LOG10
Returns the base-10 logarithm of a number |
36 | MDETERM
Returns the matrix determinant of an array |
37 | MINVERSE
Returns the matrix inverse of an array |
38 | MMULT
Returns the matrix product of two arrays |
39 | MOD
Returns the remainder from division |
40 | MROUND
Returns a number rounded to the desired multiple |
41 | MULTINOMIAL
Returns the multinomial of a set of numbers |
42 | MUNIT
Returns the unit matrix or the specified dimension |
43 | ODD
Rounds a number up to the nearest odd integer |
44 | PI
Returns the value of pi |
45 | POWER
Returns the result of a number raised to a power |
46 | PRODUCT
Multiplies its arguments |
47 | QUOTIENT
Returns the integer portion of a division |
48 | RADIANS
Converts degrees to radians |
49 | RAND
Returns a random number between 0 and 1 |
50 | RANDBETWEEN
Returns a random number between the numbers that you specify |
51 | ROMAN
Converts an Arabic numeral to Roman, as text |
52 | ROUND
Rounds a number to a specified number of digits |
53 | ROUNDDOWN
Rounds a number down, toward 0 |
54 | ROUNDUP
Rounds a number up, away from 0 |
55 | SEC
Returns the secant of an angle |
56 | SECH
Returns the hyperbolic secant of an angle |
57 | SERIESSUM
Returns the sum of a power series based on the formula |
58 | SIGN
Returns the sign of a number |
59 | SIN
Returns the sine of the given angle |
60 | SINH
Returns the hyperbolic sine of a number |
61 | SQRT
Returns a positive square root |
62 | SQRTPI
Returns the square root of pi |
63 | SUBTOTAL
Returns a subtotal in a list or database |
64 | SUM
Adds its arguments |
65 | SUMIF
Adds the cells specified by a given criteria |
66 | SUMIFS
Adds the cells specified by a multiple criteria |
67 | SUMPRODUCT
Returns the sum of the products of corresponding array components |
68 | SUMSQ
Returns the sum of the squares of the arguments |
69 | SUMX2MY2
Returns the sum of the difference of squares of corresponding values in two arrays |
70 | SUMX2PY2
Returns the sum of the sum of squares of corresponding values in two arrays |
71 | SUMXMY2
Returns the sum of squares of differences of corresponding values in two arrays |
72 | TAN
Returns the tangent of a number |
73 | TANH
Returns the hyperbolic tangent of a number |
74 | TRUNC
Truncates a number (you specify the precision of the truncation) |
Advanced Excel - Statistical Functions
Statistical functions perform calculations ranging from basic mean, median & mode to the more complex statistical distribution and probability tests.
Statistical Functions
The following table lists all the Statistical functions −
S.No. | Function and Description |
---|---|
1 | AVEDEV
Returns the average of the absolute deviations of data points from their mean |
2 | AVERAGE
Returns the average of its arguments |
3 | AVERAGEA
Returns the average of its arguments and includes evaluation of text and logical values |
4 | AVERAGEIF
Returns the average for the cells specified by a given criterion |
5 | AVERAGEIFS
Returns the average for the cells specified by multiple criteria |
6 | BETA.DIST
Returns the beta cumulative distribution function |
7 | BETA.INV
Returns the inverse of the cumulative distribution function for a specified beta distribution |
8 | BINOM.DIST
Returns the individual term binomial distribution probability |
9 | BINOM.DIST.RANGE
Returns the probability of a trial result using a binomial distribution |
10 | BINOM.INV
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
11 | CHISQ.DIST
Returns the cumulative beta probability density function |
12 | CHISQ.DIST.RT
Returns the one-tailed probability of the chi-squared distribution |
13 | CHISQ.INV
Returns the cumulative beta probability density function |
14 | CHISQ.INV.RT
Returns the inverse of the one-tailed probability of the chi-squared distribution |
15 | CHISQ.TEST
Returns the test for independence |
16 | CONFIDENCE.NORM
Returns the confidence interval for a population mean |
17 | CONFIDENCE.T
Returns the confidence interval for a population mean, using a Student's t distribution |
18 | CORREL
Returns the correlation coefficient between two data sets |
19 | COUNT
Counts how many numbers are in the list of arguments |
20 | COUNTA
Counts how many values are in the list of arguments |
21 | COUNTBLANK
Counts the number of blank cells in the argument range |
22 | COUNTIF
Counts the number of cells that meet the criteria you specify in the argument |
23 | COUNTIFS
Counts the number of cells that meet multiple criteria |
24 | COVARIANCE.P
Returns covariance, the average of the products of paired deviations |
25 | COVARIANCE.S
Returns the sample covariance, the average of the products deviations for each data point pair in two data sets |
26 | DEVSQ
Returns the sum of squares of deviations |
27 | EXPON.DIST
Returns the exponential distribution |
28 | F.DIST
Returns the F probability distribution |
29 | F.DIST.RT
Returns the F probability distribution |
30 | F.INV
Returns the inverse of the F probability distribution |
31 | F.INV.RT
Returns the inverse of the F probability distribution |
32 | F.TEST
Returns the result of an F-test |
33 | FISHER
Returns the Fisher transformation |
34 | FISHERINV
Returns the inverse of the Fisher transformation |
35 | FORECAST
Returns a value along a linear trend |
36 | FORECAST.ETS
Calculates a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm |
37 | FORECAST.ETS.CONFINT
Returns a confidence interval for the forecast value at the specified target date |
38 | FORECAST.ETS.SEASONALITY
Returns the length of the repetitive pattern detected for the specified time series |
39 | FORECAST.ETS.STAT
Returns a statistical value as a result of time series forecasting |
40 | FORECAST.LINEAR
Calculates a future value by using existing values, using linear regression. |
41 | FREQUENCY
Returns a frequency distribution as a vertical array |
42 | GAMMA
Returns the Gamma function value |
43 | GAMMA.DIST
Returns the gamma distribution |
44 | GAMMA.INV
Returns the inverse of the gamma cumulative distribution |
45 | GAMMALN
Returns the natural logarithm of the gamma function, G(x) |
46 | GAMMALN.PRECISE
Returns the natural logarithm of the gamma function, G(x) |
47 | GAUSS
Returns 0.5 less than the standard normal cumulative distribution |
48 | GEOMEAN
Returns the geometric mean |
49 | GROWTH
Returns values along an exponential trend |
50 | HARMEAN
Returns the harmonic mean |
51 | HYPGEOM.DIST
Returns the hypergeometric distribution |
52 | INTERCEPT
Returns the intercept of the linear regression line |
53 | KURT
Returns the kurtosis of a data set |
54 | LARGE
Returns the kth largest value in a data set |
55 | LINEST
Returns the parameters of a linear trend |
56 | LOGEST
Returns the parameters of an exponential trend |
57 | LOGNORM.DIST
Returns the cumulative lognormal distribution |
58 | LOGNORM.INV
Returns the inverse of the lognormal cumulative distribution |
59 | MAX
Returns the maximum value in a list of arguments, ignoring logical values and text |
60 | MAXA
Returns the maximum value in a list of arguments, including logical values and text |
61 | MAXIFS
Returns the maximum value among cells specified by a given set of conditions or criteria. |
62 | MEDIAN
Returns the median of the given numbers |
63 | MIN
Returns the minimum value in a list of arguments, ignoring logical values and text |
64 | MINA
Returns the minimum value in a list of arguments, including logical values and text |
65 | MINIFS
Returns the minimum value among cells specified by a given set of conditions or criteria. |
66 | MODE.MULT
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data |
67 | MODE.SNGL
Returns the most common value in a data set |
68 | NEGBINOM.DIST
Returns the negative binomial distribution |
69 | NORM.DIST
Returns the normal cumulative distribution |
70 | NORM.INV
Returns the inverse of the normal cumulative distribution |
71 | NORM.S.DIST
Returns the standard normal cumulative distribution |
72 | NORM.S.INV
Returns the inverse of the standard normal cumulative distribution |
73 | PEARSON
Returns the Pearson product moment correlation coefficient |
74 | PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive |
75 | PERCENTILE.INC
Returns the k-th percentile of values in a range |
76 | PERCENTRANK.EXC
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |
77 | PERCENTRANK.INC
Returns the percentage rank of a value in a data set |
78 | PERMUT
Returns the number of permutations for a given number of objects |
79 | PERMUTATIONA
Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects |
80 | PHI
Returns the value of the density function for a standard normal distribution |
81 | POISSON.DIST
Returns the Poisson distribution |
82 | PROB
Returns the probability that values in a range are between two limits |
83 | QUARTILE.EXC
Returns the quartile of the data set, based on percentile values from 0..1, exclusive |
84 | QUARTILE.INC
Returns the quartile of a data set |
85 | RANK.AVG
Returns the rank of a number in a list of numbers |
86 | RANK.EQ
Returns the rank of a number in a list of numbers |
87 | RSQ
Returns the square of the Pearson product moment correlation coefficient |
88 | SKEW
Returns the skewness of a distribution |
89 | SKEW.P
Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean |
90 | SLOPE
Returns the slope of the linear regression line |
91 | SMALL
Returns the kth smallest value in a data set |
92 | STANDARDIZE
Returns a normalized value |
93 | STDEV.P
Calculates standard deviation based on the entire population |
94 | STDEV.S
Estimates standard deviation based on a sample |
95 | STDEVA
Estimates standard deviation based on a sample, including text and logical values |
96 | STDEVPA
Calculates standard deviation based on the entire population, including text and logical values |
97 | STEYX
Returns the standard error of the predicted y-value for each x in the regression |
98 | T.DIST
Returns the Percentage Points (probability) for the Student t-distribution |
99 | T.DIST.2T
Returns the Percentage Points (probability) for the Student t-distribution |
100 | T.DIST.RT
Returns the Student's t-distribution |
101 | T.INV
Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom |
102 | T.INV.2T
Returns the inverse of the Student's t-distribution |
103 | T.TEST
Returns the probability associated with a Student's t-test |
104 | TREND
Returns values along a linear trend |
105 | TRIMMEAN
Returns the mean of the interior of a data set |
106 | VAR.P
Calculates variance based on the entire population |
107 | VAR.S
Estimates variance based on a sample |
108 | VARA
Estimates variance based on a sample, including logical values and text |
109 | VARPA
Calculates variance based on the entire population, including logical values and text |
110 | WEIBULL.DIST
Returns the Weibull distribution |
111 | Z.TEST
Returns the one-tailed probability-value of a z-test |
112 | BAHTTEXT
Converts a number to Thai text and adds a suffix of "Baht." |
113 | CHAR
Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. |
114 | CLEAN
Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. |
115 | CODE
Returns a numeric code for the first character in a text string. The returned code corresponds to the ANSI character set for the Windows System. |
116 | CONCAT
Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. |
117 | CONCATENATE
Joins two or more text strings into one string. |
118 | DOLLAR
Converts a number to text format and applies a currency symbol. The name of the Function and the symbol that it applies depend upon your language settings. |
119 | Exact
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. |
120 | FIND
FIND and FINDB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. |
121 | FIXED
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. |
122 | LEFT
Returns the first character or characters in a text string, based on the number of characters you specify. |
123 | LEN
LEN returns the number of characters in a text string. LENB returns the number of bytes used to represent the characters in a text string. |
124 | LOWER
Converts all uppercase letters in a text string to lowercase. |
125 | MID
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
126 | NUMBERVALUE
Converts text to a number, in a locale-independent way. |
127 | PROPER
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
128 | REPLACE
Replaces part of a text string, based on the number of characters you specify, with a different text string. |
129 | REPT
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
130 | RIGHT
Returns the last character or characters in a text string, based on the number of characters you specify. |
131 | SEARCH
The SEARCH and SEARCHB functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. |
132 | SUBSTITUTE
Substitutes new_text for old_text in a text string. |
133 | T
The T function returns the text referred to by value. |
134 | TEXT
Converts a numeric value to text and lets you specify the display formatting by using special format strings. |
135 | TEXTJOIN
Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges. |
136 | TRIM
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. |
137 | UNICHAR
Returns the Unicode character that is referenced by the given numeric value. |
138 | UNICODE
Returns the number (code point) corresponding to the first character of the text. |
139 | UPPER
Converts text to uppercase. |
140 | VALUE
Converts a text string that represents a number to a number. |
141 | ENCODEURL
Returns a URL-encoded string. |
142 | FILTERXML
Returns specific data from the XML content by using the specified XPath. |
143 | WEBSERVICE
Returns data from a web service on the Internet or Intranet. |