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.

Print Page

Advanced Excel Functions – Quick Guide

Advanced Excel – Compatibility Functions

Compatibility Functions

Advanced Excel – Cube Functions

Cube Functions

Advanced Excel – Database Functions

Database Functions

Advanced Excel – Date & Time Functions

Date and Time Functions

Advanced Excel – Engineering Functions

Engineering Functions

Advanced Excel – Financial Functions

Advanced Excel – Information Functions

Information Functions

Advanced Excel – Logical Functions

Logical Functions

Lookup and Reference Functions

Lookup and Reference Functions

Math and Trigonometric Functions

Math and Trigonometric Functions

Advanced Excel – Statistical Functions

Statistical Functions

Leave a Reply Cancel reply