Advanced Excel Functions Tutorial by Aha Tutorial

Advanced Excel Functions Tutorial This Microsoft Excel tutorial teaches you how to create and use Excel functions. You can use Excel functions to perform various mathematical, statistical, logical calculations. This tutorial takes you step-by-step through the process. This tutorial is intended for people who use Excel but are intimidated by the concept of formulas and functions. This tutorial assumes your familiarity with basic fomulas for calculations in Excel.

SEE MORE FREE EBOOKS: CLICK HERE

Donate link: https://paypal.me/sutranxt

Thank you!

TABLE OF CONTENTS

1. Advanced Excel - Compatibility Functions 2. Advanced Excel - Cube Functions 3. Advanced Excel - Database Functions 4. Advanced Excel - Date & Time Functions 5. Advanced Excel - Engineering Functions 6. Advanced Excel - Financial Functions 7. Advanced Excel - Information Functions 8. Advanced Excel - Logical Functions 9. Lookup and Reference Functions 10. Math and Trigonometric Functions 11. Advanced Excel - Statistical Functions

Do you want to learn the really advanced content that we couldn’t include in this eBook? 1. Excel-based Production Scheduling System CLICK HERE 2. Dose For Excel Add-in CLICK HERE 3. Aplica Excel Contable (view mobile) CLICK HERE

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 BETADIST

2 3

Returns the cumulative beta probability density function BETAINV Returns the inverse of the cumulative beta probability density function BINOMDIST

Returns the individual term binomial distribution probability CEILING 4

Rounds a number to the nearest integer or to the nearest multiple of significance CHIDIST Returns the one-tailed probability of the chi-squared distribution CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution CHITEST

7 Returns the test for independence CONFIDENCE 8 Returns the confidence interval for a population mean COVAR 9

Returns covariance, the average of the products of paired deviations CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value EXPONDIST

Returns the exponential distribution FDIST 12 Returns the F probability distribution FINV 13 Returns the inverse of the F probability distribution FLOOR 14 Rounds a number down, toward 0 FTEST 15 Returns the result of an F-Test GAMMADIST 16 Returns the gamma distribution GAMMAINV 17

Returns the inverse of the gamma cumulative distribution HYPGEOMDIST

18 Returns the hypergeometric distribution LOGINV 19 Returns the inverse of the lognormal distribution LOGNORMDIST 20 21

Returns the cumulative lognormal distribution MODE

Returns the most common value in a data set NEGBINOMDIST 22 Returns the negative binomial distribution NORMDIST 23 Returns the normal cumulative distribution NORMINV 24

Returns the inverse of the normal cumulative distribution NORMSDIST

25 Returns the standard normal cumulative distribution NORMSINV 26

Returns the inverse of the standard normal cumulative distribution PERCENTILE

27 Returns the kth percentile of values in a range PERCENTRANK 28 Returns the percentage rank of a value in a data set POISSON 29 Returns the Poisson distribution QUARTILE 30 Returns the quartile of a data set

RANK 31 Returns the rank of a number in a list of numbers STDEV 32

Estimates standard deviation based on a sample, ignoring text and logical values STDEVP Calculates standard deviation based on the entire population, ignoring text and logical values TDIST

34 Returns the student’s t-distribution TINV 35 Returns the inverse of the student’s t-distribution TTEST 36

Returns the probability associated with a student’s tTest VAR Estimates variance based on a sample, ignoring logical values and text VARP Calculates variance based on the entire population, ignoring logical values and text WEIBULL

39 Returns the Weibull distribution

ZTEST 40 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 CUBEKPIMEMBER

Returns a key performance indicator name, property, and measure, and displays the name and property in the cell. CUBEMEMBER

2 Returns a member or tuple in a cube hierarchy. CUBEMEMBERPROPERTY 3 Returns the value of a member property in the cube.

CUBERANKEDMEMBER 4 Returns the nth, or ranked, member in a set. CUBESET 5

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

6 Returns the number of items in a set. CUBEVALUE 7 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. 1

7 8

Function and Description DAVERAGE Averages the values in a column of a list or database that match conditions you specify. DCOUNT Counts the cells that contain numbers in a column of a list or database that match conditions you specify. DCOUNTA Counts the nonblank cells in a column of a list or database that match conditions you specify. DGET Returns a single value from a column of a list or database that matches conditions you specify. DMAX Returns the largest number in a column of a list or database that matches conditions you specify. DMIN Returns the smallest number in a column of a list or database that matches conditions you specify. DPRODUCT Multiplies the values in a column of a list or database that match conditions you specify. 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. DSTDEVP 9

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. DSUM Adds the numbers in a column of a list or database that match conditions you specify. 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. 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 DATE

1 Returns the serial number of a particular date. DATEDIF 2

Calculates the number of days, months, or years between two dates. DATEVALUE

3 Converts a date in the form of text to a serial number. DAY 4 Converts a serial number to a day of the month. DAYS 5 Returns the number of days between two dates. DAYS360 6 7

Calculates the number of days between two dates, based on a 360-day year. EDATE

Returns the serial number of the date that is the indicated number of months before or after the start date. EOMONTH 8

Returns the serial number of the last day of the month before or after a specified number of months. HOUR

9 Converts a serial number to an hour. ISOWEEKNUM 10

Returns the number of the ISO week number of the year for a given date. MINUTE

11 Converts a serial number to a minute. MONTH 12 Converts a serial number to a month. NETWORKDAYS 13

Returns the number of whole workdays between two dates. NETWORKDAYS.INTL Returns the number of whole workdays between two dates (international version). NOW

15 Returns the serial number of the current date and time.

SECOND 16 Converts a serial number to a second. TIME 17 Returns the serial number of a particular time. TIMEVALUE 18 Converts a time in the form of text to a serial number. TODAY 19 Returns the serial number of today’s date. WEEKDAY 20 Converts a serial number to a day of the week. WEEKNUM 21 Returns the week number in the year. WORKDAY 22

Returns the serial number of the date before or after a specified number of workdays. 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. YEAR

24 25

Converts a serial number to a year. YEARFRAC

Returns the year fraction representing the number of whole days between start_date and end_date. Do you want to learn the really advanced content that we couldn’t include in this eBook? 1. Excel-based Production Scheduling System CLICK HERE 2. Dose For Excel Add-in CLICK HERE 3. Aplica Excel Contable (view mobile) CLICK HERE

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 BESSELI

1 Returns the modified Bessel function In(x) BESSELJ 2 Returns the Bessel function Jn(x)

BESSELK 3 Returns the modified Bessel function Kn(x) BESSELY 4 Returns the Bessel function Yn(x) BIN2DEC 5 Converts a binary number to decimal BIN2HEX 6 Converts a binary number to hexadecimal BIN2OCT 7 Converts a binary number to octal BITAND 8 Returns a 'Bitwise And' of two numbers BITLSHIFT 9

Returns a value number shifted left by shift_amount bits BITOR

10 Returns a bitwise OR of 2 numbers BITRSHIFT 11

Returns a value number shifted right by shift_amount bits BITXOR

12 Returns a bitwise 'Exclusive Or' of two numbers

COMPLEX 13

Converts real and imaginary coefficients into a complex number CONVERT Converts a number from one measurement system to another DEC2BIN

15 Converts a decimal number to binary DEC2HEX 16 Converts a decimal number to hexadecimal DEC2OCT 17 Converts a decimal number to octal DELTA 18 Tests whether two values are equal ERF 19 Returns the error function ERF.PRECISE 20 Returns the error function ERFC 21 Returns the complementary error function ERFC.PRECISE 22 Returns the complementary error function

GESTEP 23

Tests whether a number is greater than a threshold value HEX2BIN

24 Converts a hexadecimal number to binary HEX2DEC 25 Converts a hexadecimal number to decimal HEX2OCT 26 Converts a hexadecimal number to octal IMABS 27

Returns the absolute value (modulus) of a complex number IMAGINARY Returns the imaginary coefficient of a complex number IMARGUMENT Returns the argument theta, an angle expressed in radians IMCONJUGATE

30 Returns the complex conjugate of a complex number IMCOS 31 32

Returns the cosine of a complex number IMCOSH

Returns the hyperbolic cosine of a complex number IMCOT 33 Returns the cotangent of a complex number IMCSC 34 Returns the cosecant of a complex number IMCSCH 35 Returns the hyperbolic cosecant of a complex number IMDIV 36 Returns the quotient of two complex numbers IMEXP 37 Returns the exponential of a complex number IMLN 38 Returns the natural logarithm of a complex number IMLOG2 39 Returns the base-2 logarithm of a complex number IMLOG10 40 Returns the base-10 logarithm of a complex number IMPOWER 41 42

Returns a complex number raised to an integer power IMPRODUCT

Returns the product of complex numbers IMREAL 43 Returns the real coefficient of a complex number IMSEC 44 Returns the secant of a complex number IMSECH 45 Returns the hyperbolic secant of a complex number IMSIN 46 Returns the sine of a complex number IMSINH 47 Returns the hyperbolic sine of a complex number IMSQRT 48 Returns the square root of a complex number IMSUB 49 Returns the difference of two complex numbers IMSUM 50 Returns the sum of complex numbers IMTAN 51 52

Returns the tangent of a complex number OCT2BIN

Converts an octal number to binary OCT2DEC 53 Converts an octal number to decimal OCT2HEX 54 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 ACCRINT

Returns the accrued interest for a security that pays periodic interest ACCRINTM Returns the accrued interest for a security that pays interest at maturity AMORDEGRC

3 Returns the depreciation for each accounting period AMORLINC 4 Returns the depreciation for each accounting period

(the depreciation coefficient depends on the life of the assets) COUPDAYBS 5

Returns the number of days from the beginning of the coupon period to the settlement date COUPDAYS Returns the number of days in the coupon period that contains the settlement date COUPDAYSNC Returns the number of days from the settlement date to the next coupon date COUPNCD

8 Returns the next coupon date after the settlement date COUPNUM 9

Returns the number of coupons payable between the settlement date and maturity date COUPPCD Returns the previous coupon date before the settlement date CUMIPMT Returns the cumulative interest paid between two periods CUMPRINC

12 Returns the cumulative principal paid on a loan

between two periods DB 13

Returns the depreciation of an asset for a specified period, using the fixed-declining-balance method DDB Returns the depreciation of an asset for a specified period, using the double-declining-balance method or some other method that you specify DISC

15 Returns the discount rate for a security DOLLARDE 16

Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction DURATION Returns the annual duration of a security with periodic interest payments EFFECT

19 Returns the effective annual interest rate FV 20 21

Returns the future value of an investment FVSCHEDULE

Returns the future value of an initial principal after applying a series of compound interest rates INTRATE 22 Returns the interest rate for a fully invested security IPMT 23

Returns the interest payment for an investment for a given period IRR Returns the internal rate of return for a series of cash flows ISPMT Calculates the interest paid during a specific period of an investment MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100 MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates NOMINAL

28 Returns the annual nominal interest rate NPER 29 Returns the number of periods for an investment

NPV 30

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate ODDFPRICE Returns the price per $100 face value of a security with an odd first period ODDFYIELD

32 Returns the yield of a security with an odd first period ODDLPRICE 33

Returns the price per $100 face value of a security with an odd last period ODDLYIELD

34 Returns the yield of a security with an odd last period PDURATION 35

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

36 Returns the periodic payment for an annuity PPMT 37

Returns the payment on the principal for an investment for a given period PRICE

38 Returns the price per $100 face value of a security

that pays periodic interest PRICEDISC 39

Returns the price per $100 face value of a discounted security PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity PV

41 Returns the present value of an investment RATE 42 Returns the interest rate per period of an annuity RECEIVED 43

46 47

Returns the amount received at maturity for a fully invested security RRI Returns an equivalent interest rate for the growth of an investment SLN Returns the straight-line depreciation of an asset for one period SYD Returns the sum-of-years’ digits depreciation of an asset for a specified period TBILLEQ

Returns the bond-equivalent yield for a Treasury bill TBILLPRICE 48

Returns the price per $100 face value for a Treasury bill TBILLYIELD

49 Returns the yield for a Treasury bill VDB 50

Returns the depreciation of an asset for a specified or partial period using a declining-balance method XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic YIELD Returns the yield on a security that pays periodic interest YIELDDISC Returns the annual yield for a discounted security, for example, a Treasury bill YIELDMAT

55 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 CELL

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

2 Returns a number corresponding to an error type INFO 3

Returns information about the current operating environment ISBLANK

4 Returns TRUE if the value is blank ISERR 5 Returns TRUE if the value is any error value except

#N/A ISERROR 6 Returns TRUE if the value is any error value ISEVEN 7 Returns TRUE if the number is even ISFORMULA 8

Returns TRUE if there is a reference to a cell that contains a formula ISLOGICAL

9 Returns TRUE if the value is a logical value ISNA 10 Returns TRUE if the value is the #N/A error value ISNONTEXT 11 Returns TRUE if the value is not text ISNUMBER 12 Returns TRUE if the value is a number ISODD 13 Returns TRUE if the number is odd ISREF 14 15

Returns TRUE if the value is a reference ISTEXT

Returns TRUE if the value is text N 16 Returns a value converted to a number NA 17 Returns the error value #N/A SHEET 18 Returns the sheet number of the referenced sheet SHEETS 19 Returns the number of sheets in a reference TYPE 20 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

AND 1 Returns TRUE if all its arguments are TRUE. FALSE 2 Returns the logical value FALSE. IF 3 Specifies a logical test to perform. IFERROR 4

Returns a different result if the first argument evaluates to an error. IFNA

Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression. IFS

Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. NOT

7 Reverses the logic of its argument. OR 8 Returns TRUE if any argument is TRUE. SWITCH 9 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. TRUE 10 Returns the logical value TRUE. XOR 11 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 ADDRESS

1 2

Returns a reference as text to a single cell in a worksheet AREAS

Returns the number of areas in a reference CHOOSE 3 Chooses a value from a list of values COLUMN 4 Returns the column number of a reference COLUMNS 5 Returns the number of columns in a reference FORMULATEXT 6 Returns the formula at the given reference as text GETPIVOTDATA 7 Returns data stored in a PivotTable HLOOKUP 8

10 11

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 HYPERLINK Creates a shortcut that opens a document on your hard drive, a server, or the Internet INDEX Uses an index to choose a value from a reference or array INDIRECT

Returns a reference indicated by a text value LOOKUP 12

Returns a value either from a one-row or one-column range or from an array MATCH

13 Returns the relative position of an item in an array OFFSET 14 Returns a reference offset from a given reference ROW 15 Returns the row number of a reference ROWS 16 Returns the number of rows in a reference RTD 17

Returns real-time data from a program that supports COM automation TRANSPOSE

18 Returns the transpose of an array VLOOKUP 19

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 ABS

1 Returns the absolute value of a number ACOS 2 Returns the arccosine of a number ACOSH 3 Returns the inverse hyperbolic cosine of a number ACOT 4 5

Returns the arccotangent of a number ACOTH

Returns the hyperbolic arccotangent of a number AGGREGATE 6 Returns an aggregate in a list or database ARABIC 7 Converts a Roman number to Arabic, as a number ASIN 8 Returns the arcsine of a number ASINH 9 Returns the inverse hyperbolic sine of a number ATAN 10 Returns the arctangent of a number ATAN2 11 Returns the arctangent from x and y coordinates ATANH 12 Returns the inverse hyperbolic tangent of a number BASE 13

Converts a number into a text representation with the given radix (base) CEILING.MATH Rounds a number up, to the nearest integer or to the nearest multiple of significance

COMBIN 15

Returns the number of combinations for a given number of objects COMBINA Returns the number of combinations with repetitions for a given number of items COS

17 Returns the cosine of a number COSH 18 Returns the hyperbolic cosine of a number COT 19 Returns the cotangent of an angle COTH 20 Returns the hyperbolic cotangent of a number CSC 21 Returns the cosecant of an angle CSCH 22 Returns the hyperbolic cosecant of an angle DECIMAL 23 24

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

Converts radians to degrees EVEN 25 Rounds a number up to the nearest even integer EXP 26 Returns e raised to the power of a given number FACT 27 Returns the factorial of a number FACTDOUBLE 28 Returns the double factorial of a number FLOOR.MATH 29

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

30 Returns the greatest common divisor INT 31 Rounds a number down to the nearest integer LCM 32 Returns the least common multiple LN 33 34

Returns the natural logarithm of a number LOG

Returns the logarithm of a number to a specified base LOG10 35 Returns the base-10 logarithm of a number MDETERM 36 Returns the matrix determinant of an array MINVERSE 37 Returns the matrix inverse of an array MMULT 38 Returns the matrix product of two arrays MOD 39 Returns the remainder from division MROUND 40 Returns a number rounded to the desired multiple MULTINOMIAL 41 Returns the multinomial of a set of numbers MUNIT 42 Returns the unit matrix or the specified dimension ODD 43 44

Rounds a number up to the nearest odd integer PI

Returns the value of pi POWER 45 Returns the result of a number raised to a power PRODUCT 46 Multiplies its arguments QUOTIENT 47 Returns the integer portion of a division RADIANS 48 Converts degrees to radians RAND 49 Returns a random number between 0 and 1 RANDBETWEEN 50

Returns a random number between the numbers that you specify ROMAN

51 Converts an Arabic numeral to Roman, as text ROUND 52 Rounds a number to a specified number of digits ROUNDDOWN 53 54

Rounds a number down, toward 0 ROUNDUP

Rounds a number up, away from 0 SEC 55 Returns the secant of an angle SECH 56 Returns the hyperbolic secant of an angle SERIESSUM 57

Returns the sum of a power series based on the formula SIGN

58 Returns the sign of a number SIN 59 Returns the sine of the given angle SINH 60 Returns the hyperbolic sine of a number SQRT 61 Returns a positive square root SQRTPI 62 Returns the square root of pi SUBTOTAL 63 64

Returns a subtotal in a list or database SUM

Adds its arguments SUMIF 65 Adds the cells specified by a given criteria SUMIFS 66 Adds the cells specified by a multiple criteria SUMPRODUCT 67

Returns the sum of the products of corresponding array components SUMSQ

68 Returns the sum of the squares of the arguments SUMX2MY2 69

Returns the sum of the difference of squares of corresponding values in two arrays SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two arrays SUMXMY2 Returns the sum of squares of differences of corresponding values in two arrays TAN

72 73

Returns the tangent of a number TANH

Returns the hyperbolic tangent of a number TRUNC 74

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. 1 AVEDEV

Function and Description

Returns the average of the absolute deviations of data points from their mean AVERAGE 2 Returns the average of its arguments AVERAGEA 3

Returns the average of its arguments and includes evaluation of text and logical values AVERAGEIF Returns the average for the cells specified by a given criterion AVERAGEIFS Returns the average for the cells specified by multiple criteria BETA.DIST

6 Returns the beta cumulative distribution function BETA.INV 7

Returns the inverse of the cumulative distribution function for a specified beta distribution BINOM.DIST Returns the individual term binomial distribution probability BINOM.DIST.RANGE

9 Returns the probability of a trial result using a

binomial distribution BINOM.INV 10

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value CHISQ.DIST Returns the cumulative beta probability density function CHISQ.DIST.RT Returns the one-tailed probability of the chi-squared distribution CHISQ.INV Returns the cumulative beta probability density function CHISQ.INV.RT Returns the inverse of the one-tailed probability of the chi-squared distribution CHISQ.TEST

15 Returns the test for independence CONFIDENCE.NORM 16 Returns the confidence interval for a population mean CONFIDENCE.T 17

Returns the confidence interval for a population mean, using a Student's t distribution

CORREL 18

Returns the correlation coefficient between two data sets COUNT Counts how many numbers are in the list of arguments COUNTA

20 Counts how many values are in the list of arguments COUNTBLANK 21

Counts the number of blank cells in the argument range COUNTIF Counts the number of cells that meet the criteria you specify in the argument COUNTIFS

23 Counts the number of cells that meet multiple criteria COVARIANCE.P 24

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

Returns the sum of squares of deviations EXPON.DIST 27 Returns the exponential distribution F.DIST 28 Returns the F probability distribution F.DIST.RT 29 Returns the F probability distribution F.INV 30 Returns the inverse of the F probability distribution F.INV.RT 31 Returns the inverse of the F probability distribution F.TEST 32 Returns the result of an F-test FISHER 33 Returns the Fisher transformation FISHERINV 34 Returns the inverse of the Fisher transformation FORECAST 35 36

Returns a value along a linear trend FORECAST.ETS

Calculates a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm FORECAST.ETS.CONFINT 37

Returns a confidence interval for the forecast value at the specified target date FORECAST.ETS.SEASONALITY Returns the length of the repetitive pattern detected for the specified time series FORECAST.ETS.STAT Returns a statistical value as a result of time series forecasting FORECAST.LINEAR Calculates a future value by using existing values, using linear regression. FREQUENCY

41 Returns a frequency distribution as a vertical array GAMMA 42 Returns the Gamma function value GAMMA.DIST 43 Returns the gamma distribution GAMMA.INV 44 Returns the inverse of the gamma cumulative

distribution GAMMALN 45

Returns the natural logarithm of the gamma function, G(x) GAMMALN.PRECISE Returns the natural logarithm of the gamma function, G(x) GAUSS Returns 0.5 less than the standard normal cumulative distribution GEOMEAN

48 Returns the geometric mean GROWTH 49 Returns values along an exponential trend HARMEAN 50 Returns the harmonic mean HYPGEOM.DIST 51 Returns the hypergeometric distribution INTERCEPT 52 Returns the intercept of the linear regression line KURT 53 54

Returns the kurtosis of a data set LARGE

Returns the kth largest value in a data set LINEST 55 Returns the parameters of a linear trend LOGEST 56 Returns the parameters of an exponential trend LOGNORM.DIST 57 Returns the cumulative lognormal distribution LOGNORM.INV 58

Returns the inverse of the lognormal cumulative distribution MAX Returns the maximum value in a list of arguments, ignoring logical values and text MAXA Returns the maximum value in a list of arguments, including logical values and text MAXIFS Returns the maximum value among cells specified by a given set of conditions or criteria. MEDIAN

62 63

Returns the median of the given numbers MIN

Returns the minimum value in a list of arguments, ignoring logical values and text MINA 64

Returns the minimum value in a list of arguments, including logical values and text MINIFS Returns the minimum value among cells specified by a given set of conditions or criteria. MODE.MULT Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data MODE.SNGL

67 Returns the most common value in a data set NEGBINOM.DIST 68 Returns the negative binomial distribution NORM.DIST 69 Returns the normal cumulative distribution NORM.INV 70

Returns the inverse of the normal cumulative distribution NORM.S.DIST

71 Returns the standard normal cumulative distribution

NORM.S.INV 72

Returns the inverse of the standard normal cumulative distribution PEARSON Returns the Pearson product moment correlation coefficient PERCENTILE.EXC Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive PERCENTILE.INC

75 Returns the k-th percentile of values in a range PERCENTRANK.EXC 76

Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set PERCENTRANK.INC

77 Returns the percentage rank of a value in a data set PERMUT 78

Returns the number of permutations for a given number of objects PERMUTATIONA Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects PHI

Returns the value of the density function for a standard normal distribution POISSON.DIST 81 Returns the Poisson distribution PROB 82

Returns the probability that values in a range are between two limits QUARTILE.EXC Returns the quartile of the data set, based on percentile values from 0..1, exclusive QUARTILE.INC

84 Returns the quartile of a data set RANK.AVG 85 Returns the rank of a number in a list of numbers RANK.EQ 86 Returns the rank of a number in a list of numbers RSQ 87

Returns the square of the Pearson product moment correlation coefficient SKEW

88 89

Returns the skewness of a distribution 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 SLOPE 90 Returns the slope of the linear regression line SMALL 91 Returns the kth smallest value in a data set STANDARDIZE 92 Returns a normalized value STDEV.P 93

Calculates standard deviation based on the entire population STDEV.S

94 Estimates standard deviation based on a sample STDEVA 95

Estimates standard deviation based on a sample, including text and logical values STDEVPA Calculates standard deviation based on the entire population, including text and logical values STEYX Returns the standard error of the predicted y-value for each x in the regression

T.DIST 98

Returns the Percentage Points (probability) for the Student t-distribution T.DIST.2T Returns the Percentage Points (probability) for the Student t-distribution T.DIST.RT

100 Returns the Student's t-distribution T.INV 101

Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom T.INV.2T

102 Returns the inverse of the Student's t-distribution T.TEST 103

Returns the probability associated with a Student's ttest TREND

104 Returns values along a linear trend TRIMMEAN 105 Returns the mean of the interior of a data set VAR.P 106 107

Calculates variance based on the entire population VAR.S

Estimates variance based on a sample VARA 108

109

Estimates variance based on a sample, including logical values and text VARPA Calculates variance based on the entire population, including logical values and text WEIBULL.DIST

110 Returns the Weibull distribution Z.TEST 111 Returns the one-tailed probability-value of a z-test BAHTTEXT 112

113

114

115

Converts a number to Thai text and adds a suffix of "Baht." 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. 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. 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. CONCAT 116

Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. CONCATENATE

117 Joins two or more text strings into one string. DOLLAR 118

119

120

121

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. Exact Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. 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. 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. LEFT 122

123

124

125

126

127

128

Returns the first character or characters in a text string, based on the number of characters you specify. 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. LOWER Converts all uppercase letters in a text string to lowercase. 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. NUMBERVALUE Converts text to a number, in a locale-independent way. 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. REPLACE Replaces part of a text string, based on the number of characters you specify, with a different text string.

REPT 129

130

131

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. RIGHT Returns the last character or characters in a text string, based on the number of characters you specify. 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. SUBSTITUTE

132 Substitutes new_text for old_text in a text string. T 133 The T function returns the text referred to by value. TEXT 134

135

136

Converts a numeric value to text and lets you specify the display formatting by using special format strings. 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. 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. UNICHAR 137

138

Returns the Unicode character that is referenced by the given numeric value. UNICODE Returns the number (code point) corresponding to the first character of the text. UPPER

139 Converts text to uppercase. VALUE 140

Converts a text string that represents a number to a number. ENCODEURL

141 Returns a URL-encoded string. FILTERXML 142

143

Returns specific data from the XML content by using the specified XPath. WEBSERVICE Returns data from a web service on the Internet or Intranet.

Donate link: https://paypal.me/sutranxt

Thank you!