Advanced Excel Functions - Quick Guide


Advertisements


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.



Advertisements
E-Books Store