Full List of Excel Functions and Formulas helps you to understand the functions with Example Formulas. There are 500+ Excel Functions available in the latest version of Excel and counting. You can go to each formula and find its description and use
List of Excel Formulas and Functions
Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.
Function | Description | Syntax and Formula |
---|---|---|
ABS | The ABS function in Excel returns the absolute value of a number. | Syntax: ABS(number)Formula: ABS(-5)Result: 5 |
ACCRINT | The ACCRINT function in Microsoft Excel calculates the accrued interest for a security that pays periodic interest. | Syntax: ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])Formula: ACCRINT(“1/1/2020″,”2/1/2020″,”3/1/2020”,0.05,1000,2,0,0)This example calculates the accrued interest for a security with an issue date of 1/1/2020, first interest date of 2/1/2020, settlement date of 3/1/2020, interest rate of 5%, par value of 1000, and a frequency of 2 (semi-annually). The basis and calculation method are both set to 0. |
ACCRINTM | The ACCRINTM function returns the amount of interest that has been accrued on a security that pays periodic interest, such as a bond, between a start and end date. | Syntax: ACCRINTM(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])Formula: ACCRINTM(“1/1/2019”, “1/1/2019”, “1/15/2019”, 0.06, 1000, 2, 0, 0)This example returns the amount of interest accrued on a bond with an issue date of 1/1/2019, first interest date of 1/1/2019, settlement date of 1/15/2019, annual interest rate of 6%, par value of $1,000, and semi-annual frequency, using the US (NASD) 30/360 day count basis and the price basis method. |
ACOS | The ACOS function returns the arccosine (in radians) of a number. | Syntax: ACOS(number)Formula: To find the arccosine of 0.5, the formula would be:=ACOS(0.5) The result would be 1.0471975511966 radians. |
ACOSH | The ACOSH function returns the inverse hyperbolic cosine of a number. | Syntax: ACOSH(number)Formula: ACOSH(3)The result of this formula is 1.762747174039086. |
ACOT | The ACOT function returns the inverse cotangent (arccotangent) of a given number. | Syntax: ACOT(number)Formula: ACOT(0.5)This formula returns 1.10714871779409. |
ACOTH | The ACOTH function is an Excel function that returns the inverse hyperbolic cotangent of a given number. | Syntax: ACOTH(number)Formula: ACOTH(2)This formula returns 0.5493061443340548. |
ADDRESS | The ADDRESS function returns a cell address as text, based on a given row and column number. | Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])Formula: ADDRESS(2,3,4,TRUE,”Sheet1″)This example will return the address of cell C4 (row 2, column 3) on Sheet1 as $C$4. |
AGGREGATE | The AGGREGATE function is a built-in function in Excel that performs calculations such as sum, count, average, max, min, product, etc. on a range of data. | Syntax: AGGREGATE(function_num, options, ref1, [ref2], …)Function_num: This is a number that specifies the type of function to be used.Options: This is a number that specifies the options to be used. Ref1, [ref2], …: This is the range of cells that will be used for the calculation. Formula: This formula will calculate the average of the values in the range A1:A10. |
AMORDEGRC | The AMORDEGRC function returns the depreciation of an asset for a specified period using a depreciation coefficient from a table of depreciation coefficients. | Syntax: AMORDEGRC(cost, date_purchased, first_period, salvage, period, [month])Formula: AMORDEGRC(10000, DATE(2015,1,1), DATE(2015,4,1), 1000, 12, 4)This example returns the depreciation of an asset with a cost of 10,000 purchased on January 1, 2015, with a first period ending on April 1, 2015, a salvage value of 1,000, a period of 12, and a depreciation coefficient of 4. |
AMORLINC | The AMORLINC function is used to calculate the depreciation of an asset for a specified period using the linear depreciation method. | Syntax: AMORLINC(cost, date_purchased, first_period, salvage, period, [rate])Formula: To calculate the depreciation of an asset that cost $10,000, was purchased on 1/1/2020, and has a salvage value of $1,000 after 5 years, the following formula can be used:AMORLINC(10000, “1/1/2020”, “1/1/2020”, 1000, 5) |
AND | The AND function is a logical function in Excel that returns TRUE if all of the conditions that are specified are true, and returns FALSE if any of the conditions are false. | Syntax: AND(logical1, [logical2], …)Formula: AND(A1>5,B1<10) |
ARABIC | The ARABIC function is used to convert Roman numerals to Arabic numerals. | Syntax: ARABIC(text)Formula: ARABIC(“XVII”) returns 17 |
AREAS | The AREAS function in Microsoft Excel returns the number of areas in a reference. | Syntax: AREAS(reference)Formula: AREAS(A1:B2)This formula will return the value 2, since the range A1:B2 contains two areas. |
ARRAYTOTEXT | The ARRAYTOTEXT function in Excel converts an array of values into a text string. | Syntax: ARRAYTOTEXT(array, delimiter, [text_qualifier])Formula: ARRAYTOTEXT({1,2,3,4,5},”,”,”‘”)Result: ‘1’,’2′,’3′,’4′,’5′ |
ASC | The ASC function in Microsoft Excel is used to convert a character into its corresponding ASCII code. | Syntax: ASC(text)Formula: ASC(“A”)The result of this formula would be 65, which is the ASCII code for the letter A. |
ASIN | The ASIN function returns the arcsine of a given number. | Syntax: ASIN(number)Formula: ASIN(0.5)This will return the arcsine of 0.5, which is equal to 0.5235987755982988. |
ASINH | The ASINH function returns the inverse hyperbolic sine of a given number. | Syntax: ASINH(number)Formula: ASINH(2)This formula returns 1.4436354751788. |
ATAN | The ATAN function in Excel returns the arctangent of a given number, which is the angle in radians between the x-axis and a line from the origin to the given number. | Syntax: ATAN(number)Formula: ATAN(1) returns 0.785398163397448 |
ATAN2 | The ATAN2 function returns the arctangent of two numbers, which is the angle between the x-axis and a line from the origin to a point in the Cartesian plane. | Syntax: ATAN2(x_num, y_num)Formula: ATAN2(2, 3)This example returns the arctangent of 2 and 3, which is 0.982793723247329 radians or 56.3099324740202 degrees. |
ATANH | The ATANH function is an Excel function that returns the inverse hyperbolic tangent of a number. | Syntax: ATANH(number)Formula: ATANH(0.5)This example would return 0.5493061443340548, which is the inverse hyperbolic tangent of 0.5. |
AVEDEV | AVEDEV: The AVEDEV function in Excel returns the average of the absolute deviations of data points from their mean. | Syntax: AVEDEV(number1, [number2], …)Formula: AVEDEV(2,4,6,8)The result of this function is 2, which is the average of the absolute deviations of 2, 4, 6, and 8 from their mean of 5. |
AVERAGE | The AVERAGE function in Excel calculates the arithmetic mean of a given set of values. | Syntax: AVERAGE(number1, [number2], …)Formula: AVERAGE(10,20,30,40) Result: 25 |
AVERAGEA | The AVERAGEA function is an Excel function that calculates the average of all numbers, including text and logical values. | Syntax: AVERAGEA(value1, [value2], …)Formula: AVERAGEA(1,2,3,4,5)Result: 3 |
AVERAGEIF | The AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria. | Syntax: AVERAGEIF(range, criteria, [average_range])Formula: AVERAGEIF(A2:A9, “>50”, B2:B9)This formula will return the average of all numbers in range B2:B9, where the corresponding cell in range A2:A9 is greater than 50. |
AVERAGEIFS | The AVERAGEIFS function is an Excel function that calculates the average of a range of cells that meet multiple criteria. | Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Formula: To calculate the average of the numbers in the range A1:A10, if the corresponding cells in the range B1:B10 contain the value “apple”, the formula would be:AVERAGEIFS(A1:A10, B1:B10, “apple”) |
BAHTTEXT | BAHTTEXT is an Excel function that converts a number to Thai text. | Syntax: BAHTTEXT(number)Formula: BAHTTEXT(12345)Result: ??????????????????????????????????????? |
BASE | BASE is an Excel function that converts a number from one number base to another. It is used to convert a number from one number system to another, such as from binary to decimal or from hexadecimal to octal. | Syntax: BASE(number, radix, min_length)Formula: BASE(10101, 2, 8)This example would convert the binary number 10101 to its decimal equivalent, 21. The min_length argument is optional and is used to pad the result with zeros to the specified length. |
BESSELI | BESSELI is an Excel function that returns the modified Bessel function In(x). It is used to calculate the modified Bessel function of the first kind. | Syntax: BESSELI(x,n)x – The value for which to calculate the modified Bessel function.n – The order of the modified Bessel function. Formula: This formula will return the modified Bessel function of the first kind for x = 1 and order = 2. The result will be 0.440050585744933. |
BESSELJ | The BESSELJ function is an Excel function that returns the Bessel function of the first kind, Jn, of an order n and argument x. | Syntax: BESSELJ(x,n)Formula: BESSELJ(1,2) returns 0.440050585744933 |
BESSELK | The BESSELK function is an Excel function that returns the modified Bessel function K of the specified order and argument. | Syntax: BESSELK(x,n)Formula: BESSELK(2,3) returns 0.06899 |
BESSELY | Description: The BESSELY function is an Excel function that calculates the Bessel function of the first kind, which is a mathematical function used to solve a variety of problems in physics, engineering, and mathematics. |
Syntax: BESSELY(x,n)Formula: BESSELY(2,3)This formula will return the Bessel function of the first kind for x = 2 and n = 3. |
BETA.DIST | BETA.DIST is an Excel function used to calculate the cumulative beta probability density function (PDF) for a given set of parameters. | Syntax: BETA.DIST(x, alpha, beta, cumulative, [A], [B])Formula: To calculate the cumulative beta probability density function for a given set of parameters x = 0.5, alpha = 2, beta = 3, and cumulative = TRUE, the following formula is used:=BETA.DIST(0.5, 2, 3, TRUE) The result of this formula is 0.7421875. |
BETA.INV | BETA.INV is an Excel function that returns the inverse of the cumulative distribution function for a specified beta distribution. | Syntax: BETA.INV(probability, alpha, beta, [A], [B])Formula: BETA.INV(0.7, 2, 5, 0, 1)This example returns the value 0.837, which is the inverse of the cumulative distribution function for the specified beta distribution with a probability of 0.7, alpha of 2, and beta of 5. |
BETADIST | The BETADIST function in Excel returns the cumulative beta probability density function or the probability that a variable, which follows a beta distribution, is less than or equal to a specified value. | Syntax: BETADIST(x,alpha,beta,A,B)x: A value at which the probability density function is to be evaluated.alpha: The alpha parameter of the beta distribution. beta: The beta parameter of the beta distribution. A: Optional. The lower bound of the beta distribution. B: Optional. The upper bound of the beta distribution. Formula: This example returns the probability that a variable, which follows a beta distribution with alpha = 2 and beta = 3, is less than or equal to 2. The lower bound of the beta distribution is 0 and the upper bound is 4. |
BETAINV | The BETAINV function returns the inverse of the cumulative beta probability density function (BETA.DIST). | Syntax: BETAINV(probability, alpha, beta, [A], [B])Formula: BETAINV(0.5,2,4,0,1)This example returns 0.5, which is the inverse of the cumulative beta probability density function with a probability of 0.5, alpha of 2, beta of 4, and lower bound of 0 and upper bound of 1. |
BIN2DEC | BIN2DEC is an Excel function that converts a binary number to a decimal number. | Syntax: BIN2DEC(number)Formula: BIN2DEC(10101) returns 21 |
BIN2HEX | The BIN2HEX function is used to convert a binary number to a hexadecimal number. | Syntax: BIN2HEX(number, [places])Formula: BIN2HEX(10101010, 8)Result: AA |
BIN2OCT | BIN2OCT is an Excel function that converts a binary number to an octal number. | Syntax: BIN2OCT(number)Formula: BIN2OCT(10101010) returns 252 |
BINOM.DIST | The BINOM.DIST function in Excel returns the individual term binomial distribution probability. It calculates the probability of a certain number of successes in a given number of independent trials, each with the same probability of success. | Syntax: BINOM.DIST(number_s, trials, probability_s, cumulative)Formula: BINOM.DIST(2, 10, 0.5, FALSE)This example returns the probability of 2 successes in 10 independent trials, each with a probability of success of 0.5. |
BINOM.DIST.RANGE | BINOM.DIST.RANGE is an Excel function that calculates the probability of a certain number of successes in a sequence of independent Bernoulli trials, given a probability of success in each trial. | Syntax: BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2)Formula: To calculate the probability of getting between 2 and 4 successes in a sequence of 5 independent Bernoulli trials, with a probability of success of 0.4 in each trial, the formula would be: BINOM.DIST.RANGE(5, 0.4, 2, 4) |
BINOM.INV | The BINOM.INV function is an Excel statistical function that returns the inverse of the cumulative binomial distribution. This function is useful in determining the number of successes given a probability and a number of trials. | Syntax: BINOM.INV(probability, number_trials, alpha)Formula: Suppose you want to find the number of successes given a probability of 0.5 and 10 trials. The formula would be:=BINOM.INV(0.5,10,0.5) The result would be 5, meaning that there is a 50% chance of 5 successes in 10 trials. |
BINOMDIST | The BINOMDIST function in Excel calculates the individual term binomial distribution probability. | Syntax: BINOMDIST(number_s,trials,probability_s,cumulative)Formula: To calculate the probability of getting 3 successes in 5 trials, with a probability of success of 0.4, the formula would be: BINOMDIST(3,5,0.4,FALSE) The result would be 0.3456. |
BITAND | BITAND is a logical function in Excel that performs a bitwise AND operation on two numbers. The syntax for the BITAND function is: BITAND(number1, number2). | Syntax:
Formula: This will return the result of 7 (111 in binary) AND 11 (1011 in binary), which is 3 (11 in binary). |
BITLSHIFT | BITLSHIFT is an Excel function that shifts the bits of a number to the left or right. It takes two arguments: the number to be shifted, and the number of bits to shift it. | Syntax: BITLSHIFT(number, shift_amount)Formula: BITLSHIFT(10, 2)This example shifts the bits of the number 10 to the left by two bits. The result is 40. |
BITOR | BITOR is an Excel logical function that returns a bitwise OR of two numbers. The syntax for the BITOR function is: BITOR(number1, number2). | Syntax:
Formula: This will return 5 as the result. |
BITRSHIFT | BITRSHIFT is an Excel function that shifts the bits of a number to the right. It takes two arguments: the number to be shifted and the number of positions to shift. | Syntax: BITRSHIFT(number, shift_amount)Formula: BITRSHIFT(10,2)This example shifts the bits of the number 10 two positions to the right, resulting in the value 2. |
BITXOR | The BITXOR function returns a bitwise ‘exclusive or’ of two numbers. | Syntax: BITXOR(number1, number2)Formula: BITXOR(5,7)This will return 4, as 5 XOR 7 = 4. |
BYCOL | UMN
BYCOLUMN is a function in Microsoft Excel that allows users to sort a range of data by column. |
Syntax: BYCOLUMN(array, [col], [ascending])Formula: BYCOLUMN(A1:C10, 2, TRUE) This example will sort the range A1:C10 by the second column in ascending order. |
BYROW | BYROW is an Excel function that returns an array of numbers from a given range of cells. It takes two arguments: array and row_num. Array is a range of cells that contains numbers, and row_num is a number that indicates which row should be returned. | Syntax: BYROW(array, row_num)Formula: If the range A1:C3 contains the following numbers:A1: 1 A2: 2 A3: 3 B1: 4 B2: 5 B3: 6 C1: 7 C2: 8 C3: 9 Then the formula BYROW(A1:C3, 2) will return the array {2, 5, 8}. |
CALL | The CALL function is a worksheet function in Microsoft Excel that allows a user to run a program written in an external language, such as Visual Basic for Applications (VBA), from within a worksheet. It is used to run a subroutine (macro) written in an external language. | Syntax: CALL(module_name, procedure, argument1, argument2, …)Formula: CALL(“Module1”, “Sub1”, “Argument1”, 3, “Argument2”)In this example, the CALL function is used to run the Sub1 macro in the Module1 module, passing the arguments “Argument1”, 3, and “Argument2” to the macro. |
CEILING | CEILING: The CEILING function rounds a number up to the nearest multiple of a specified number. | Syntax: CEILING(number, significance)Formula: CEILING(A2,0.1)This formula will round the value in cell A2 up to the nearest multiple of 0.1. |
CEILING.MATH | The CEILING.MATH function rounds a number up to the nearest integer or to the nearest multiple of significance. | Syntax: CEILING.MATH(number, significance, [mode])Formula: CEILING.MATH(4.2, 0.5, 1)This example returns 4.5, as it rounds 4.2 up to the nearest multiple of 0.5, which is 4.5. |
CEILING.PRECISE | The CEILING.PRECISE function rounds a number up to the nearest integer or to the nearest multiple of significance. | Syntax: CEILING.PRECISE(number, significance)Formula: CEILING.PRECISE(14.8, 0.1)Result: 14.9 |
CELL | The CELL function returns information about the formatting, location, or contents of a cell. | Syntax: CELL(info_type, [reference])Formula: CELL(“filename”,A1)This example returns the file name of the workbook containing the cell A1. |
CHAR | The CHAR function returns the character specified by the number code. | Syntax: CHAR(number)Formula: CHAR(65)This will return the character “A”. |
CHIDIST | CHIDIST is an Excel function that returns the one-tailed probability of the chi-squared distribution. The syntax for CHIDIST is:
CHIDIST(x,deg_freedom) Where x is the value at which to evaluate the distribution, and deg_freedom is the number of degrees of freedom. |
Syntax:
Formula: =CHIDIST(5,3) The result would be 0.168. |
CHIINV | The Excel CHIINV function returns the inverse of the left-tailed probability of the chi-squared distribution. | Syntax: CHIINV(probability,deg_freedom)Formula: To calculate the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and degrees of freedom of 5, use the following formula:=CHIINV(0.95,5) The result is 11.070. |
CHISQ.DIST | The CHISQ.DIST function returns the cumulative probability of a chi-squared distribution. | Syntax: CHISQ.DIST(x,deg_freedom,cumulative)Formula: CHISQ.DIST(4,3,TRUE)This example returns the cumulative probability of a chi-squared distribution with 4 degrees of freedom. The result is 0.2266. |
CHISQ.DIST.RT | The CHISQ.DIST.RT function returns the right-tailed probability of the chi-squared distribution. | Syntax: CHISQ.DIST.RT(x,deg_freedom)Formula: CHISQ.DIST.RT(3,4)This example returns the right-tailed probability of the chi-squared distribution with 3 degrees of freedom. The result is 0.0682689492137086. |
CHISQ.INV | The CHISQ.INV function in Excel returns the inverse of the left-tailed probability of the chi-squared distribution. | Syntax: CHISQ.INV(probability,deg_freedom)Formula: CHISQ.INV(0.95,2)This example returns the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and 2 degrees of freedom. The result is 5.9915. |
CHISQ.INV.RT | The CHISQ.INV.RT function returns the inverse of the right-tailed probability of the chi-squared distribution. | Syntax: CHISQ.INV.RT(probability,deg_freedom)Formula: CHISQ.INV.RT(0.95,2)This example returns the inverse of the right-tailed probability of the chi-squared distribution with a probability of 0.95 and a degree of freedom of 2. The result is 5.991464547. |
CHISQ.TEST | CHISQ.TEST is an Excel function used to perform a chi-square test of independence. It is used to determine whether there is a significant association between two categorical variables. | Syntax: CHISQ.TEST(observed_range, expected_range)Formula: CHISQ.TEST(A1:B10, C1:D10) |
CHITEST | The CHITEST function in Microsoft Excel is used to calculate the test for independence of two ranges of categorical data. | Syntax: CHITEST(actual_range, expected_range)Formula: CHITEST(A2:A7, B2:B7) |
CHOOSE | The CHOOSE function in Excel is used to select one of a number of values based on an index number. | Syntax: CHOOSE(index_num, value1, [value2], …)Formula: CHOOSE(2, “red”, “blue”, “green”)This example would return “blue” as the output. |
CHOOSECOLS | The CHOOSECOLS function allows you to select columns from a range of cells. It returns an array of the selected column numbers. | Syntax: CHOOSECOLS(range, [criteria])Formula: CHOOSECOLS(A1:F10, “>50”)This example will return an array of column numbers from A1:F10 that contain values greater than 50. |
CHOOSEROWS | The CHOOSEROWS function returns an array of rows from a list based on a criteria. | Syntax: CHOOSEROWS(list, criteria)Formula: CHOOSEROWS(A1:A10, “>5”)This will return an array of rows from A1:A10 where the value is greater than 5. |
CLEAN | The CLEAN function removes all non-printable characters from a text string. | Syntax: CLEAN(text)Formula: CLEAN(“Hello!@#$%^&*World”)Result: HelloWorld |
CODE | CODE: The CODE function is used to convert a text string into a code with a corresponding numerical value. |
Syntax: CODE(text)Formula: CODE(“A”) returns 65. |
COLUMN | The COLUMN function is an Excel function that returns the column number of a given cell reference. | Syntax: COLUMN(reference)Formula: COLUMN(A1)This will return the value 1, since A1 is the first column in the worksheet. |
COLUMNS | COLUMNS is a function in Microsoft Excel that returns the number of columns in a given array or reference. | Syntax: COLUMNS(array)Formula: COLUMNS(A1:B2)This example would return 2, since the range A1:B2 contains two columns. |
COMBIN | COMBIN is an Excel function that calculates the number of combinations of a given number of objects from a set of objects. | Syntax: COMBIN(number, number_chosen)Formula: If you want to calculate the number of combinations of 5 objects taken 3 at a time, the formula would be COMBIN(5,3). The result of this formula would be 10. |
COMBINA | COMBINA: The COMBINA function returns the number of combinations for a given number of items. | Syntax: COMBINA(number, number_chosen)Formula: COMBINA(6,3)This example returns the number of combinations possible when selecting 3 items from a set of 6 items. The result is 20. |
COMPLEX | The COMPLEX function is used to convert real and imaginary coefficients into a complex number. | Syntax: COMPLEX(real_num, i_num, [suffix])Formula: COMPLEX(5,3,”i”)This will return the complex number 5+3i. |
CONCAT | ENATE
The CONCATENATE function in Excel is used to join two or more text strings together. |
Syntax: CONCATENATE(text1, [text2], [text3], …)Formula: CONCATENATE(“Hello “, “World”)Result: Hello World |
CONCATENATE | The CONCATENATE function is used to join two or more text strings together. | Syntax: CONCATENATE(text1, [text2], [text3], …)Formula: CONCATENATE(“Hello”,” “,”World”)Result: Hello World |
CONFIDENCE | CONFIDENCE is an Excel function that calculates the confidence interval for a population mean, using a normal distribution. It takes three arguments: alpha, standard_dev, and size.
Alpha is the significance level used to compute the confidence level. It is a number between 0 and 1. Standard_dev is the standard deviation for the population. Size is the sample size. |
Syntax: CONFIDENCE(alpha,standard_dev,size)Formula: CONFIDENCE(0.05,2,100)This example would calculate the 95% confidence interval for a population with a standard deviation of 2 and a sample size of 100. |
CONFIDENCE.NORM | The CONFIDENCE.NORM function returns the confidence interval for a population mean, using a normal distribution. This function uses a confidence level to calculate the margin of error that is added and subtracted from the sample mean to create the confidence interval. | Syntax: CONFIDENCE.NORM(alpha,standard_dev,size)alpha: The significance level used to compute the confidence level.standard_dev: The standard deviation for the data range. size: The number of observations in the data range. Formula: =CONFIDENCE.NORM(0.05,10,100) The result would be a margin of error of 2.262. |
CONFIDENCE.T | The CONFIDENCE.T function is used to calculate the confidence interval for a population mean, based on a sample mean and standard deviation. | Syntax: CONFIDENCE.T(alpha, standard_dev, size)Formula: To calculate the 95% confidence interval for a sample mean of 10, with a standard deviation of 4, and a sample size of 25, the formula would be: CONFIDENCE.T(0.05, 4, 25) which would return a result of 8.8 to 11.2. |
CONVERT | The CONVERT function is used to convert a number from one unit of measurement to another. | Syntax: CONVERT(number, from_unit, to_unit)Formula:CONVERT(10, “mi”, “km”) This example would convert 10 miles to kilometers, and the result would be 16.09344 kilometers. |
CORREL | CORREL is an Excel function that returns the correlation coefficient of two sets of values. It is used to measure the linear relationship between two variables. | Syntax: CORREL(array1, array2)Formula: CORREL(A1:A10, B1:B10)This example returns the correlation coefficient of the values in cells A1 through A10 and B1 through B10. |
COS | The COS function returns the cosine of an angle given in radians. | Syntax: COS(number)Formula: COS(PI()) returns -1, which is the cosine of PI radians. |
COSH | The COSH function in Excel returns the hyperbolic cosine of a given number. | Syntax: COSH(number)Formula: COSH(2) returns 3.7621956910836 |
COT | The COT function is used to calculate the cotangent of an angle given in radians. | Syntax: COT(number)Formula: COT(PI()/4)This example would return the cotangent of pi/4, which is equal to 1. |
COTH | The COTH function returns the hyperbolic cotangent of a given number. | Syntax: COTH(number)Formula: COTH(0.5) returns 1.31 |
COUNT | IF
The COUNTIF function is used to count the number of cells within a range that meet a specified criteria. |
Syntax: COUNTIF(range, criteria)Formula: COUNTIF(A1:A10, “>50”) This example would count the number of cells in the range A1 to A10 that have a value greater than 50. |
COUNTA | The COUNTA function in Microsoft Excel counts the number of cells that contain data within a range. | Syntax: COUNTA(value1, [value2], …)Formula: COUNTA(A1:A10)This example will count the number of cells in the range A1:A10 that contain data. |
COUNTBLANK | The COUNTBLANK function counts the number of empty cells in a range of cells. | Syntax: COUNTBLANK(range)Formula: COUNTBLANK(A1:A10) This formula would count the number of blank cells in the range A1 to A10. |
COUNTIF | COUNTIF is an Excel function used to count the number of cells within a range that meet a specified criteria. | Syntax: COUNTIF(range, criteria)Formula: COUNTIF(A1:A10, “>5”) This example will count the number of cells in range A1:A10 that are greater than 5. |
COUNTIFS | The COUNTIFS function is a statistical function used to count the number of cells in a range that meet multiple criteria. | Syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], …)Formula: COUNTIFS (A1:A10, “>20”, B1:B10, “>30”)This example will count the number of cells in the range A1:A10 that are greater than 20, and the number of cells in the range B1:B10 that are greater than 30. |
COUPDAYBS | The COUPDAYBS function returns the number of days from the beginning of the coupon period to the settlement date. | Syntax: COUPDAYBS(settlement, maturity, frequency, [basis])Formula: COUPDAYBS(“1/15/2020”, “7/15/2020”, 2, 0)This example returns the number of days from the beginning of the coupon period to the settlement date of January 15, 2020, with a maturity date of July 15, 2020 and a frequency of 2 (semi-annual). The basis is 0 (US (NASD) 30/360). The result is 91. |
COUPDAYS | The COUPDAYS function returns the number of days in the coupon period that contains the settlement date. | Syntax: COUPDAYS(settlement, maturity, frequency, [basis])Formula: COUPDAYS(“9/15/2020”, “9/15/2021”, 2, 0)This example returns the number of days in the coupon period that contains the settlement date of 9/15/2020, with a maturity date of 9/15/2021, a frequency of 2 (semi-annual payments), and a basis of 0 (US (NASD) 30/360). The result is 180. |
COUPDAYSNC | The COUPDAYSNC function returns the number of days in the coupon period that contains the settlement date for a security with a periodic interest payment. | Syntax: COUPDAYSNC(settlement, maturity, frequency, [basis])Formula: COUPDAYSNC(DATE(2020,8,15),DATE(2022,2,15),2,0)This example returns the number of days in the coupon period that contains the settlement date (15th August 2020) for a security with a periodic interest payment with a maturity date of 15th February 2022 and a frequency of 2 (semi-annual). The basis argument is optional and is set to 0 in this example. |
COUPNCD | The COUPNCD function returns the next coupon date after the settlement date for a security with a specified frequency of coupon payments. | Syntax: COUPNCD(settlement, maturity, frequency, [basis])Formula: COUPNCD(DATE(2020,1,1), DATE(2022,1,1), 2, 0)This example returns the next coupon date after the settlement date of January 1, 2020 for a security with a maturity date of January 1, 2022 and a frequency of 2 (semiannual) payments. The basis is 0 (US (NASD) 30/360). |
COUPNUM | COUPNUM is an Excel function that calculates the number of coupon payments between two dates. It is used to calculate the number of times an investor will receive a coupon payment between two dates. | Syntax: COUPNUM(settlement, maturity, frequency, [basis])Formula: COUPNUM(DATE(2020,1,1), DATE(2025,12,31), 2, 0)This example calculates the number of coupon payments between 1 January 2020 and 31 December 2025, assuming a frequency of 2 payments per year and a basis of 0 (US (NASD) 30/360). The result is 6. |
COUPPCD | The COUPPCD function returns the next coupon payment date after the settlement date. | Syntax: COUPPCD(settlement, maturity, frequency, [basis])Formula: COUPPCD(DATE(2021,1,1), DATE(2021,6,30), 2, 0)This example returns 3/1/2021, which is the next coupon payment date after the settlement date of 1/1/2021, with a maturity date of 6/30/2021, a frequency of 2 (semi-annual), and a basis of 0 (US (NASD) 30/360). |
COVAR | COVAR is an Excel function that calculates the covariance between two sets of data. It is used to measure the linear relationship between two variables. | Syntax: COVAR(array1, array2)Formula: COVAR(A1:A5, B1:B5)This example will calculate the covariance between the data in the range A1:A5 and the data in the range B1:B5. |
COVARIANCE.P | The COVARIANCE.P function calculates the population covariance of two sets of values. | Syntax: COVARIANCE.P(array1, array2)Formula: COVARIANCE.P(A2:A7,B2:B7)This example returns the population covariance of the values in cells A2 through A7 and the values in cells B2 through B7. |
COVARIANCE.S | The COVARIANCE.S function is an Excel function used to calculate the sample covariance of two sets of values. | Syntax: COVARIANCE.S(array1,array2)Formula: COVARIANCE.S(A1:A5,B1:B5)This example will calculate the sample covariance of the two sets of values in the range A1:A5 and B1:B5. |
CRITBINOM | The CRITBINOM function returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. | Syntax: CRITBINOM(trials, probability_s, alpha)Formula: CRITBINOM(10, 0.5, 0.95)This example would return the smallest value for which the cumulative binomial distribution with 10 trials and a probability of success of 0.5 is greater than or equal to 0.95. The result would be 8. |
CSC | CSC (Complementary Sine) is an Excel function that returns the complementary sine of a given number. The syntax for the CSC function is CSC(angle), where angle is the angle in radians for which you want to find the complementary sine. | Syntax:
Formula: This will return the value of 1.4142135623731, which is the complementary sine of PI/4. |
CSCH | The CSCH function returns the hyperbolic cosecant of a given number. | Syntax: CSCH(number)Formula: CSCH(2)Result: 0.275720564771759 |
CUBEKPIMEMBER | CUBEKPIMEMBER is an Excel function that returns a key performance indicator (KPI) value from an OLAP cube. | Syntax: CUBEKPIMEMBER(cube_name, kpi_name)Formula: CUBEKPIMEMBER(“Sales Cube”, “Sales”) returns the sales KPI value from the Sales Cube. |
CUBEMEMBER | The CUBEMEMBER function returns information about a member or tuple within a cube. | Syntax: CUBEMEMBER(cube_name, member_name)Formula: CUBEMEMBER(“Sales Cube”, “Product[Beverages]”) |
CUBEMEMBERPROPERTY | The CUBEMEMBERPROPERTY function returns a property value for a given cube member. | Syntax: CUBEMEMBERPROPERTY(cube_name, member_name, property_name)Formula: CUBEMEMBERPROPERTY(“Sales Cube”, “[Product].[Product].[Bread]”, “UniqueName”)This example returns the unique name of the cube member “[Product].[Product].[Bread]” from the “Sales Cube” cube. |
CUBERANKEDMEMBER | The CUBERANKEDMEMBER function is a Microsoft Excel function that returns the nth item in a set, based on its rank in the set. | Syntax: CUBERANKEDMEMBER(set, rank [, [ascending | descending]])Formula: To return the third highest value in the set {1,2,3,4,5}, the following formula can be used:=CUBERANKEDMEMBER({1,2,3,4,5}, 3, descending) The result of the formula is 3. |
CUBESET | The CUBESET function is used to create a set of members or tuples from a cube. | Syntax: CUBESET(cube_name, set_expression, [name])Formula: CUBESET(“Sales Cube”, “[Measures].[Quantity] > 1000”, “High Quantity Sales”) |
CUBESETCOUNT | The CUBESETCOUNT function in Excel returns the number of sets in a cube. | Syntax: CUBESETCOUNT(cube_name)Formula:=CUBESETCOUNT(“Sales Cube”) This example would return the number of sets in the cube named “Sales Cube”. |
CUBEVALUE | The CUBEVALUE function is a Microsoft Excel function that returns a value from an OLAP cube. | Syntax: CUBEVALUE(connection, cube_ref, field1, field2, …)Formula: CUBEVALUE(“OLAP”, “[Sales].[Product].[Product]”, “Revenue”) |
CUMIPMT | The CUMIPMT function in Excel calculates the cumulative interest paid on a loan between a start period and an end period. | Syntax: CUMIPMT(rate, nper, pv, start_period, end_period, type)Formula: To calculate the cumulative interest paid on a loan of $10,000 at an annual interest rate of 5% over a period of 5 years, starting from the 2nd year, the formula would be:=CUMIPMT(0.05, 5, 10000, 2, 5, 0) The result would be $1,250. |
CUMPRINC | The CUMPRINC function in Excel is used to calculate the cumulative principal paid on a loan between a start period and an end period. | Syntax: CUMPRINC(rate, nper, pv, start_period, end_period, type)Formula: To calculate the cumulative principal paid on a loan with an interest rate of 5%, a total number of periods of 10, a present value of $2000, a start period of 3 and an end period of 6, the formula would be:=CUMPRINC(5%, 10, 2000, 3, 6, 0) The result would be $600, indicating that $600 of the principal was paid between the 3rd and 6th periods. |
DATE | The DATE function is used to create a date value from individual year, month, and day components. | Syntax: DATE(year, month, day)Formula: DATE(2021,5,1) will return the date 5/1/2021. |
DATEDIF | The DATEDIF function is a built-in function in Microsoft Excel that calculates the number of days, months, or years between two dates. | Syntax: DATEDIF(start_date, end_date, unit)Formula: To calculate the number of days between two dates, the syntax would be: DATEDIF(A1,B1,”d”) |
DATEVALUE | The DATEVALUE function in Microsoft Excel converts a date that is stored as text to a serial number that Excel recognizes as a date. | Syntax: DATEVALUE(date_text)Formula: DATEVALUE(“1/1/2020”) This example would return the serial number 43831, which is the number that Excel recognizes as January 1, 2020. |
DAVERAGE | DAVERAGE is an Excel function that calculates the average of selected database entries that meet certain criteria. | Syntax: DAVERAGE(database, field, criteria)Formula: To calculate the average of the prices of all products in the database with the category of “Fruit”, the following formula can be used: DAVERAGE(A2:E7,5,”Fruit”) |
DAY | S
The DAYS function returns the number of days between two dates. |
Syntax: DAYS(end_date, start_date)Formula: DAYS(DATE(2020,10,1),DATE(2020,9,1))This example returns the number of days between October 1st, 2020 and September 1st, 2020, which is 30 days. |
DAYS | The DAYS function returns the number of days between two dates. | Syntax: DAYS(end_date, start_date)Formula: DAYS(A2,A1) where A1 contains the start date and A2 contains the end date. |
DAYS360 | The DAYS360 function is used to calculate the number of days between two dates based on a 360-day year. | Syntax: DAYS360(start_date, end_date, [method])Formula: DAYS360(A2,B2)This formula will calculate the number of days between the dates in cells A2 and B2. |
DB | DB: The DB function is a financial function that returns the depreciation of an asset for a specified period using the fixed-declining balance method. | Syntax: DB(cost, salvage, life, period, [month])Formula: DB(10000,1000,5,2)This example returns the depreciation of an asset with a cost of $10,000, a salvage value of $1,000, a life of 5 years, and a period of 2 years. |
DBCS | The DBCS function is a database function in Microsoft Excel that allows you to extract data from a database. | Syntax: DBCS(database, field, criteria)Formula: DBCS(“customers”,”name”,”state=’CA'”) |
DCOUNT | DCOUNT: The DCOUNT function counts the cells that contain numbers in a database. | Syntax: DCOUNT(database, field, criteria)Database: This is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields.Field: This is the column in the database that you want to count the numbers in. Criteria: This is an optional range of cells that contains criteria. Formula: This example counts the number of cells in the Age column (column 4) of the database range A1:D10 that meet the criteria in the range A1:C10. |
DCOUNTA | DCOUNTA is an Excel function that counts the number of cells in a database that contain numbers and meets a given set of criteria. | Syntax: DCOUNTA(database, field, criteria)Formula: DCOUNTA(A1:C10, 1, “>10”)This example counts the number of cells in the range A1:C10 in the first field (field 1) that contain a number greater than 10. |
DDB | The DDB function is used to calculate the depreciation of an asset for a specified period using the double-declining balance method. | Syntax: DDB(cost, salvage, life, period, [factor])Formula: DDB(10000,1000,5,2,2)This example calculates the depreciation of an asset for the second period, given a cost of $10,000, a salvage value of $1,000, a life of 5 periods, and a factor of 2. |
DEC2BIN | DEC2BIN is an Excel function that converts a decimal (base 10) number to a binary (base 2) number. | Syntax: DEC2BIN(number, [places])Formula: DEC2BIN(10, 8) returns 00001010. |
DEC2HEX | DEC2HEX is an Excel function that converts a decimal number to a hexadecimal number. | Syntax: DEC2HEX(number, [places])Formula: DEC2HEX(255,2)Result: FF |
DEC2OCT | The DEC2OCT function is a mathematical function in Microsoft Excel that converts a decimal number to an octal number. | Syntax: DEC2OCT(number, places)Formula: DEC2OCT(20,2)Result: 24 |
DECIMAL | The DECIMAL function is used to convert a number from any other base to decimal. | Syntax: DECIMAL(number, radix)Formula: DECIMAL(1011,2)This example would return the decimal value of 11. |
DEGREES | The DEGREES function converts an angle in radians to degrees. | Syntax: DEGREES(angle)Formula: DEGREES(3.14159265358979)Result: 180 |
DELTA | The DELTA function in Excel returns 1 if two values are equal and 0 if they are not equal. | Syntax: DELTA(number1, number2)Formula: DELTA(A1, B1) |
DEVSQ | DEVSQ is an Excel function that calculates the sum of squares of deviations of data points from their sample mean. | Syntax: DEVSQ(number1, [number2], …)Formula: DEVSQ(2, 3, 4, 5)This example would return the sum of squares of deviations of the data points 2, 3, 4, and 5 from their sample mean, which is 3.5. The result would be 4.5. |
DGET | The DGET function is used to extract a single value from a specified database record (row) that matches the given criteria. | Syntax: DGET(database, field, criteria)Formula: DGET(A1:D10, “Age”, “Name”=”John”)This example will return the age of the person named John from the database range A1:D10. |
DISC | The DISC function returns the discount rate for a security. | Syntax: DISC(settlement, maturity, pr, redemption, [basis])Formula: DISC(DATE(2020,6,15),DATE(2021,6,15),0.1,100,1)This example returns the discount rate for a security with a settlement date of June 15, 2020, a maturity date of June 15, 2021, a price of 0.1, and a redemption value of 100, using the US (NASD) 30/360 day count basis. |
DMAX | DMAX is an Excel function that returns the maximum value from a specified database column that meets a given criteria. | Syntax: DMAX(database, field, criteria)Formula: DMAX(A1:B10, “Price”, “Product = Shoes”)This example would return the maximum price from the range A1:B10 where the product is equal to “Shoes”. |
DMIN | The DMIN function returns the smallest number in a set of values from a database. | Syntax: DMIN(database, field, criteria)Formula: DMIN(A2:C10,”Price”, “Category=Clothing”) |
DOLLAR | The DOLLAR function converts a number to text, using a currency format. | Syntax: DOLLAR(number, [decimals])Formula: DOLLAR(A2, 2)This example will convert the number in cell A2 to text, using two decimal places. |
DOLLARDE | DOLLARDE is an Excel function that converts a dollar price expressed as a fraction into a dollar price expressed as a decimal. | Syntax: DOLLARDE(fractional_dollar, fraction)Formula: DOLLARDE(3/32, 32)Result: 0.09375 |
DOLLARFR | DOLLARFR is an Excel function that converts a number to text in a currency format. | Syntax: DOLLARFR(number,decimals)Formula: DOLLARFR(123.45,2)Result: 123,45 € |
DPRODUCT | The DPRODUCT function multiplies the corresponding values in two or more arrays and returns the sum of the products. | Syntax: DPRODUCT(array1, array2, [array3], …)Formula: DPRODUCT(A1:A3,B1:B3) This example multiplies the values in cells A1, A2 and A3 with the corresponding values in cells B1, B2 and B3, and returns the sum of the products. |
DROP | DOWN
The DROPDOWN function creates a dropdown list in a cell. |
Syntax: DROPDOWN(list, [selected], [input_title], [input_message])Formula: DROPDOWN(A1:A5, 2, “Select an Option”, “Choose an Option”) |
DSTDEV | DSTDEV is an Excel statistical function that calculates the standard deviation of a population based on a sample of numbers. | Syntax: DSTDEV(number1, [number2], …)Formula: DSTDEV(2, 4, 6, 8, 10)Result: 2.82842712474619 |
DSTDEVP | DSTDEVP is a statistical function in Microsoft Excel that returns the standard deviation of a population based on a sample of data. | Syntax: DSTDEVP(database, field, criteria)Formula: To find the standard deviation of the population of people living in a certain city, the following formula could be used: =DSTDEVP(A1:A100, “population”, “city”) |
DSUM | DSUM is a function in Microsoft Excel that allows users to sum up values in a list that meet certain criteria. | Syntax: DSUM(database, field, criteria)Formula: DSUM(A1:C10, “Price”, “Category=Clothing”)This example will sum up all of the values in the “Price” column of the range A1:C10 that have the category “Clothing”. |
DURATION | The DURATION function in Excel calculates the number of days between two given dates. | Syntax: DURATION(settlement, maturity, frequency, [basis])Formula: DURATION(DATE(2020,1,1),DATE(2021,1,1),2,0)This formula will return the number of days between January 1st, 2020 and January 1st, 2021, assuming a semi-annual frequency and a US (NASD) 30/360 day count basis. The result will be 365. |
DVAR | The DVAR function in Excel calculates the variance of a sample set of data. | Syntax: DVAR(database, field, criteria)Formula: To calculate the variance of the amount column in the table below, where the date is greater than or equal to 1/1/2020:DVAR(A2:C10, “Amount”, “Date>=1/1/2020”) |
DVARP | The DVARP function in Excel is used to calculate the variance of a population based on a sample of data. | Syntax: DVARP(number1,[number2],…)Formula: DVARP(2,4,6,8,10)This example would return 4. |
EDATE | EDATE: The EDATE function returns a date that is a specified number of months before or after a given date. | Syntax: EDATE(start_date, months)Formula: EDATE(TODAY(), 3)This formula will return a date that is 3 months after the current date. |
EFFECT | The EFFECT function is an Excel function that calculates the effective annual interest rate. It takes two arguments, the nominal interest rate and the number of compounding periods per year. | Syntax: EFFECT(nominal_rate, npery)Formula: EFFECT(0.05, 12)This example would return 0.0514, which is the effective annual interest rate for a nominal interest rate of 5% and 12 compounding periods per year. |
ENCODEURL | ENCODEURL is an Excel function that encodes a string of text into a URL-safe format. This is useful when creating a hyperlink in a cell. | Syntax: ENCODEURL(text)Formula: ENCODEURL(“www.example.com/search?q=hello world”)Result: www.example.com/search%3Fq%3Dhello%20world |
EOMONTH | The EOMONTH function returns the last day of the month that is a specified number of months before or after a given date. | Syntax: EOMONTH(start_date, months)Formula: EOMONTH(A2, 3) where A2 contains a date value. This will return the last day of the month 3 months after the date in A2. |
ERF | The ERF function returns the error function integrated between two supplied limits. The error function is a special function used in probability and statistics to measure the area under a normal distribution curve. | Syntax: ERF(lower_limit, upper_limit)Formula: ERF(-1,2)This example returns the value of 0.954499736103642, which is the area under a normal distribution curve between -1 and 2. |
ERF.PRECISE | The ERF.PRECISE function is an Excel function that calculates the error function of a given value. The error function is a mathematical function used to calculate the probability that a normally distributed random variable will be within a certain range. | Syntax: ERF.PRECISE(x)Formula: ERF.PRECISE(0.5)This formula will return the error function of 0.5, which is 0.5204999. |
ERFC | ERFC is an Excel function that returns the complementary error function of a given number. | Syntax: ERFC(number)Formula: ERFC(0.5)This example returns the value 0.479500122. |
ERFC.PRECISE | The ERFC.PRECISE function is an Excel statistical function that returns the complementary error function integrated between lower_limit and upper_limit. The complementary error function is a special function used in statistics and probability theory. | Syntax: ERFC.PRECISE(lower_limit, upper_limit)Formula: To calculate the complementary error function integrated between 0 and 1, the formula would be: =ERFC.PRECISE(0,1) which returns 0.842700792949715. |
ERROR.TYPE | The ERROR.TYPE function returns a number that corresponds to a specific error value in Microsoft Excel. | Syntax: ERROR.TYPE(error_val)Formula: ERROR.TYPE(#VALUE!) This will return the number 2, which corresponds to the #VALUE! error. |
EUROCONVERT | The EUROCONVERT function converts a given amount from one currency to another, using the European Central Bank’s exchange rate. | Syntax: EUROCONVERT(amount, from_currency, to_currency, [exchange_date], [triangulation_precision])Formula: To convert 10 euros to US dollars on December 31, 2020, the EUROCONVERT function would be written as follows:=EUROCONVERT(10,”EUR”,”USD”,”31/12/2020″) |
EVEN | The EVEN function rounds a number up to the nearest even integer. | Syntax: EVEN(number)Formula: EVEN(3.2)Result: 4 |
EXACT | The EXACT function compares two text strings and returns TRUE if the two strings are exactly the same, including case and any spaces. | Syntax: EXACT(text1, text2)Formula: EXACT(“Apple”,”apple”)Result: FALSE |
EXP | The EXP function in Excel returns the result of the mathematical constant e raised to the power of a given number. | Syntax: EXP(number)Formula: EXP(2)Result: 7.38905609893065 |
EXPAND | The EXPAND function is used to expand a range of cells from a single cell. | Syntax: EXPAND(reference, row_num, column_num)Formula: EXPAND(A1,2,2) This would expand the range of cells from A1 to C2. |
EXPON.DIST | The EXPON.DIST function returns the exponential distribution. It returns the probability that a random variable, which follows an exponential distribution, is less than or equal to a given value. | Syntax: EXPON.DIST(x,lambda,cumulative)x: The value at which you want to evaluate the distribution.lambda: The rate parameter of the distribution. cumulative: A logical value that determines the form of the function. Formula: This formula will return the probability that a random variable, which follows an exponential distribution with rate parameter 1, is less than or equal to 2. |
EXPONDIST | The EXPONDIST function returns the exponential distribution for a specified value, lambda, and cumulative. | Syntax: EXPONDIST(x,lambda,cumulative)Formula: EXPONDIST(2,3,TRUE)This formula returns the cumulative exponential distribution for x=2 and lambda=3. |
F.DIST | F.DIST is an Excel function that returns the F probability distribution. It calculates the probability of a value occurring given a certain number of degrees of freedom. | Syntax: F.DIST(x,deg_freedom1,deg_freedom2)Formula: F.DIST(2,2,4)This example returns the probability of a value of 2 occurring given 2 and 4 degrees of freedom. |
F.DIST.RT | The F.DIST.RT function returns the right-tailed F probability distribution. This function is used to calculate the probability that the observed variance in a sample is greater than the variance in the entire population. | Syntax: F.DIST.RT(x,deg_freedom1,deg_freedom2)Formula: F.DIST.RT(4,5,6)This example will return the probability that the observed variance in a sample is greater than 4, when the degrees of freedom for the sample and the population are 5 and 6, respectively. |
F.INV | F.INV is an Excel function used to calculate the inverse of the F-distribution. It returns the probability associated with a given x-value and degrees of freedom. | Syntax: F.INV(probability, degrees_freedom1, degrees_freedom2)Formula: F.INV(0.05, 5, 10)This example returns the x-value associated with a probability of 0.05, with 5 degrees of freedom in the numerator and 10 degrees of freedom in the denominator. |
F.INV.RT | F.INV.RT is an Excel function that returns the inverse of the F probability distribution. It returns the value of the inverse cumulative distribution function (inverse of the cumulative probability) for a given probability. | Syntax: F.INV.RT(probability,deg_freedom1,deg_freedom2)Formula: F.INV.RT(0.95,2,3)This example returns the inverse cumulative distribution for a probability of 0.95 with two degrees of freedom in the numerator and three degrees of freedom in the denominator. The result is 6.867. |
F.TEST | F.TEST is an Excel function used to calculate the probability of two samples having the same variance. It takes two sets of data as its arguments and returns a probability value. | Syntax: F.TEST(array1, array2)Formula: F.TEST(A1:A10, B1:B10) This function returns the probability that the two sets of data in cells A1:A10 and B1:B10 have the same variance. |
FACT | FACT: The FACT function returns the factorial of a number. | Syntax: FACT(number)Formula: FACT(5)This formula will return 120, which is the factorial of 5 (5x4x3x2x1). |
FACTDOUBLE | The FACTDOUBLE function returns the double factorial of a given number. The double factorial is the product of all the integers from 1 up to the given number, but skipping every other number. | Syntax: FACTDOUBLE(number)Formula: To calculate the double factorial of 8, the formula would be =FACTDOUBLE(8). The result would be 8 x 6 x 4 x 2 = 384. |
False | The FALSE function in Excel is a logical function that returns the value FALSE. | Syntax: FALSE()Formula: FALSE()This formula will return the value FALSE. |
FDIST | The FDIST function in Excel is a statistical function that returns the F probability distribution. It returns the probability that the random variable has a value between two specified values. | Syntax: FDIST(x,deg_freedom1,deg_freedom2)Formula: FDIST(2,3,4)This example returns the probability that the random variable has a value between 2 and 3 with degrees of freedom 3 and 4. |
FILTER | The FILTER function is used to filter a range of data based on supplied criteria. It returns a subset of data that meets the criteria. | Syntax: FILTER(array, include, [if_empty])Formula: FILTER(A2:C10, B2:B10=”Yes”, “No matches”)This example will return the values in column A that correspond to the rows in column B that contain the value “Yes”. If no matches are found, it will return “No matches”. |
FILTERXML | The FILTERXML function in Excel is used to extract data from an XML string, based on an XPath expression. | Syntax: FILTERXML(xml, xpath)Formula: FILTERXML(“Dr. Seuss”,”/book/title”) The result of this formula would be “The Cat in the Hat”. |
FIND, FINDBs | The FIND and FINDB functions are used to locate a character or text string within a larger text string. | Syntax: FIND(find_text, within_text, [start_num]) FINDB(find_text, within_text, [start_num])Formula:=FIND(“Hello”, “Hello World”, 1) =FINDB(“e”, “Hello World”, 1) The FIND function returns the character position of the first occurrence of “Hello” in the text string “Hello World”, which is 1. The FINDB function returns the character position of the first occurrence of “e” in the text string “Hello World”, which is 2. |
FINV | FINV is an Excel function used to calculate the inverse of the F-Distribution. | Syntax: FINV(probability, degrees_freedom1, degrees_freedom2)Formula: FINV(0.95, 10, 10)This example would return the inverse of the F-Distribution with a probability of 0.95, 10 degrees of freedom for the numerator, and 10 degrees of freedom for the denominator. |
FISHER | FISHER is an Excel function that returns the Fisher transformation at x-value. The Fisher transformation is a way to normalize data that may not be normally distributed. | Syntax: FISHER(x)Formula: FISHER(0.5) returns 0.279415498198926 |
FISHERINV | The FISHERINV function returns the inverse of the Fisher transformation at a specified value. | Syntax: FISHERINV(x)Formula: FISHERINV(0.9) returns 0.47140452079103 |
FIXED | The FIXED function in Excel is used to round a number to a specified number of decimal places and display it as a text string with a fixed number of decimal places. | Syntax: FIXED(number, [decimals], [no_commas])Formula: FIXED(123.4567,2,TRUE)Result: 123.46 |
FLOOR | The FLOOR function in Excel rounds a number down to the nearest multiple of a specified value. | Syntax: FLOOR(number, significance)Formula: FLOOR(4.7, 0.5)The result of this formula is 4.5. |
FLOOR.MATH | The FLOOR.MATH function rounds a number down to the nearest multiple of a specified significance. | Syntax: FLOOR.MATH(number, significance, [mode])Formula: FLOOR.MATH(12.5, 0.5)Result: 12 |
FLOOR.PRECISE | The FLOOR.PRECISE function rounds a number down to the nearest multiple of significance. | Syntax: FLOOR.PRECISE(number, significance)Formula: FLOOR.PRECISE(3.14159, 0.01)The result of this example is 3.14. |
FORECAST | The FORECAST function is used to calculate a future value based on existing values provided. It uses linear regression to calculate the value. | Syntax: FORECAST(x, known_y’s, known_x’s)Formula: FORECAST(4,A1:A4,B1:B4)This example uses the FORECAST function to calculate the future value of 4 based on the existing values in cells A1-A4 and B1-B4. |
FORECAST.ETS | The FORECAST.ETS function is an Excel function that predicts future values based on existing values. It uses the Exponential Triple Smoothing (ETS) algorithm to predict future values. | Syntax: FORECAST.ETS(known_y’s, [known_x’s], new_x’s, [seasonality], [trend], [confidence])Formula: FORECAST.ETS(A1:A10, B1:B10, 11, 0, 1, 95)This example uses the values in cells A1 to A10 and B1 to B10 to predict the value in cell A11 using the Exponential Triple Smoothing (ETS) algorithm with a trend of 1 and a confidence of 95%. |
FORECAST.ETS.CONFINT | The FORECAST.ETS.CONFINT function is an Excel function that returns a confidence interval for a forecast generated by the FORECAST.ETS function. | Syntax: FORECAST.ETS.CONFINT(known_y’s, [known_x’s], [confidence_level], [forecast_type], [seasonality], [data_completion], [aggregation])Formula: FORECAST.ETS.CONFINT(B2:B13,C2:C13,90%)This example will return a 90% confidence interval for the forecast generated by the FORECAST.ETS function using the known y values in the range B2:B13 and the known x values in the range C2:C13. |
FORECAST.ETS.SEASONALITY | The FORECAST.ETS.SEASONALITY function in Microsoft Excel is used to predict future values based on existing values that have a seasonal pattern. | Syntax: FORECAST.ETS.SEASONALITY(x, known_y’s, [seasonality], [data_completion], [aggregation])Formula: FORECAST.ETS.SEASONALITY(B2, B3:B12, 12, 1, 0)This example uses the FORECAST.ETS.SEASONALITY function to predict the value in cell B2 based on the values in cells B3:B12. The seasonality is set to 12, data completion is set to 1, and aggregation is set to 0. |
FORECAST.ETS.STAT | The FORECAST.ETS.STAT function in Microsoft Excel is used to calculate a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm. | Syntax: FORECAST.ETS.STAT(known_y’s, [known_x’s], [new_x’s], [stat_type], [seasonality], [aggregation])Formula: FORECAST.ETS.STAT(A1:A10, B1:B10, 11, “stat”, 12, “average”) |
FORECAST.LINEAR | The FORECAST.LINEAR function is an Excel function that predicts a value based on existing values. It uses a linear regression algorithm to calculate the best fit line for the existing values and predict a value for the specified x value. | Syntax: FORECAST.LINEAR(x, known_y’s, known_x’s)Formula: To predict the sales for the month of April based on the existing sales data for the months of January, February and March, the following formula can be used:FORECAST.LINEAR(4, B2:B4, A2:A4) Where A2:A4 contains the months of January, February and March, and B2:B4 contains the sales figures for those months. |
FORMULATEXT | The FORMULATEXT function is used to return the formula in a cell as text. | Syntax: FORMULATEXT(reference)Formula: FORMULATEXT(A1) This example would return the formula in cell A1 as text. |
FREQUENCY | The FREQUENCY function in Excel returns a frequency distribution, which is a summary table that shows the frequency of values within a range. | Syntax: FREQUENCY(data_array, bins_array)Formula: FREQUENCY(A2:A8,B2:B4)This example will return a frequency distribution of the values in range A2:A8, using the range B2:B4 as the bins. |
FTEST | The FTEST function returns the result of an F-test. An F-test is a type of statistical test that is used to compare the variances of two data sets. | Syntax: FTEST(array1, array2)Formula: FTEST(A1:A10, B1:B10) |
FV | FV: The FV function in Excel is used to calculate the future value of an investment. It takes three arguments: rate, nper, and pmt. | Syntax: FV(rate, nper, pmt)Formula: FV(0.06, 10, -100)This example returns the future value of an investment of $100 for 10 periods at an interest rate of 6%. |
FVSCHEDULE | FVSCHEDULE is an Excel function that returns the future value of an investment based on a series of periodic payments and a constant interest rate. | Syntax: FVSCHEDULE(principal, schedule)Formula: FVSCHEDULE(1000, {0.1, 0.1, 0.1, 0.1})This example returns the future value of an investment of $1000 with four periodic payments of 10% each. The result is $1,464. |
GAMMA | The GAMMA function is used to calculate the gamma value of a given number. Gamma is a mathematical function that is used to calculate the probability of a random variable taking on a value less than or equal to a certain number. | Syntax: GAMMA(number)Formula: GAMMA(5)This formula will return the gamma value of 5. |
GAMMA.DIST | The GAMMA.DIST function in Excel returns the gamma distribution, which is a type of probability distribution. It is used to calculate the probability that a random variable is less than or equal to a given value. | Syntax: GAMMA.DIST(x,alpha,beta,cumulative)Formula: GAMMA.DIST(2,2,2,TRUE)This example returns the cumulative gamma distribution for x=2, alpha=2, and beta=2. The result is 0.632120558828558. |
GAMMA.INV | The GAMMA.INV function returns the inverse of the gamma cumulative distribution. | Syntax: GAMMA.INV(probability,alpha,beta,cumulative)Formula: To calculate the inverse of the gamma cumulative distribution with a probability of 0.5, an alpha of 2, and a beta of 3, the following formula would be used:=GAMMA.INV(0.5,2,3,TRUE) The result of this formula would be 3. |
GAMMADIST | GAMMADIST is an Excel function that calculates the Gamma probability density function or the cumulative Gamma distribution. | Syntax: GAMMADIST(x,alpha,beta,cumulative)Formula: GAMMADIST(2,3,4,TRUE)This example will calculate the cumulative Gamma distribution for x = 2, alpha = 3, and beta = 4. |
GAMMAINV | The GAMMAINV function returns the inverse of the gamma cumulative distribution. | Syntax: GAMMAINV(probability,alpha,beta)Formula: GAMMAINV(0.95,2,2)This example returns the inverse of the gamma cumulative distribution for a probability of 0.95, an alpha of 2, and a beta of 2. |
GAMMALN | GAMMALN: The GAMMALN function returns the natural logarithm of the gamma function, G(x). | Syntax: GAMMALN(x)Formula: GAMMALN(7)The result of this example would be 1.94591. |
GAMMALN.PRECISE | The GAMMALN.PRECISE function is an Excel function that returns the natural logarithm of the gamma function, G(x), to a high degree of precision. | Syntax: GAMMALN.PRECISE(x)Formula: GAMMALN.PRECISE(5)The result of this function would be the natural logarithm of the gamma function, G(5), to a high degree of precision. |
GAUSS | The GAUSS function returns the cumulative distribution function (CDF) of a standard normal distribution. | Syntax: GAUSS(x)Formula: GAUSS(1.2)This function returns the value of 0.8849. |
GCD | GCD stands for Greatest Common Divisor. It is used to calculate the greatest common divisor of two or more integers. | Syntax: GCD(number1, [number2], …)Formula: GCD(12, 18)The result of this formula is 6, as 6 is the greatest common divisor of 12 and 18. |
GEOMEAN | The GEOMEAN function calculates the geometric mean of a set of numbers. The geometric mean is the average of a set of numbers multiplied together and then taking the nth root, where n is the number of numbers in the set. | Syntax: GEOMEAN(number1, [number2], …)Formula: GEOMEAN(2,3,4,5)This example would return 3.5, which is the geometric mean of the set of numbers (2, 3, 4, and 5). |
GESTEP | The GESTEP function returns a logical value of TRUE or FALSE depending on whether a number is greater than or equal to a given step value. | Syntax: GESTEP(number, step)Formula: GESTEP(7, 3)This will return TRUE as 7 is greater than or equal to 3. |
GETPIVOTDATA | The GETPIVOTDATA function in Excel is used to retrieve data stored in a PivotTable. It is used to reference data stored in a PivotTable by using a standard cell reference. | Syntax: GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)Formula: GETPIVOTDATA(“Sales”,A2,”Region”,”West”) |
GROWTH | The GROWTH function returns an array of y-values for a series of new x-values that are linearly interpolated from a given set of x-values and y-values. | Syntax: GROWTH(known_y’s, [known_x’s], [new_x’s], [const])Formula: GROWTH(B2:B6,A2:A6,A7:A10,TRUE) |
HARMEAN | The HARMEAN function is used to calculate the harmonic mean of a set of numbers. The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of the given numbers. | Syntax: HARMEAN(number1, [number2], …)Formula: HARMEAN(2,4,6)Result: 3.6 |
HEX2BIN | The HEX2BIN function in Excel is used to convert a hexadecimal number to a binary number. | Syntax: HEX2BIN(number, [places])Formula: HEX2BIN(A1, 8)In this example, the HEX2BIN function will convert the hexadecimal number in cell A1 to a binary number with 8 places. |
HEX2DEC | HEX2DEC is an Excel function that converts a hexadecimal number to its decimal equivalent. | Syntax: HEX2DEC(number)Formula: HEX2DEC(“FF”) returns 255 |
HEX2OCT | The HEX2OCT function in Excel is used to convert a hexadecimal number to an octal number. | Syntax: HEX2OCT(number)Formula: HEX2OCT(A1) where A1 contains the hexadecimal number “F1” will return the octal number “361”. |
HLOOKUP | HLOOKUP is an Excel function used to look up and retrieve data from a row in a table. It searches for a value in the top row of the table and returns the corresponding value in the same column from another row. | Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Formula: HLOOKUP(B2,A2:D7,3,FALSE)In this example, the HLOOKUP function searches for the value in cell B2 in the first row of the table (A2:D7). It then returns the corresponding value in the third row of the same column. The range_lookup argument is set to FALSE to ensure an exact match is found. |
HOUR | The HOUR function returns the hour part of a given time, as a number between 0 and 23. | Syntax: HOUR(serial_number)Formula: HOUR(A2) returns the hour part of the time in cell A2. |
HSTACK | HSTACK is an Excel function that allows users to combine two or more arrays into one single array. | Syntax: HSTACK(array1, array2, array3, …)Formula: HSTACK({1,2,3}, {4,5,6}, {7,8,9}) returns {1,2,3,4,5,6,7,8,9} |
HYPERLINK | The HYPERLINK function creates a shortcut that jumps to a specific location in the current workbook, opens a document stored on a network server, or opens a web page on the Internet. | Syntax: HYPERLINK(link_location, [friendly_name])Formula: HYPERLINK(“www.google.com”, “Google”)This will create a link to Google’s website with the friendly name “Google”. |
HYPGEOM.DIST | HYPGEOM.DIST is an Excel function that calculates the probability of a given number of successes in a sample, without replacement, from a population of a given size. | Syntax: HYPGEOM.DIST(sample_s, number_sample, population_s, successes, cumulative)Formula: To calculate the probability of getting 3 successes in a sample of 10 from a population of 20, the formula would be: HYPGEOM.DIST(10, 20, 3, FALSE). |
HYPGEOMDIST | The HYPGEOMDIST function returns the hypergeometric distribution. It is used to calculate the probability of a given number of successes in a sample of a given size, taken from a population of a given size containing a certain number of successes. | Syntax: HYPGEOMDIST(sample_s, number_sample, population_s, number_successes)Formula: HYPGEOMDIST(10, 5, 20, 3)This example returns the probability of 3 successes in a sample of 5 taken from a population of 20 containing 3 successes. |
IF | The IF function in Excel is a logical function that allows you to make a logical comparison between two values and returns a value based on the result of that comparison. | Syntax: IF(logical_test, value_if_true, value_if_false)Formula: IF(A2>B2, “A is greater than B”, “B is greater than A”) |
IFERROR | The IFERROR function is used to catch and handle errors in a formula. It tests a value for an error and returns a different value if an error is found. | Syntax: IFERROR(value, value_if_error)Formula: IFERROR(A1/B1, 0) |
IFNA | IFNA: This function returns the value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. | Syntax: IFNA(value_if_error, value_if_no_error)Formula: IFNA(0,A1/A2) |
IFS | IFS is an Excel function that allows you to test multiple conditions at the same time and returns a value that corresponds to the first TRUE result. | Syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)Formula: IFS(A1=1, “Yes”, A1=2, “No”, A1=3, “Maybe”)This formula will check the value in cell A1 and return “Yes” if it is equal to 1, “No” if it is equal to 2, and “Maybe” if it is equal to 3. |
IMABS | The IMABS function is an Excel function that returns the absolute value of a complex number. | Syntax: IMABS(inumber)Formula: IMABS(5+3i)Result: 5 |
IMAGINARY | The IMAGINARY function returns the imaginary coefficient of a complex number in Excel. | Syntax: IMAGINARY(inumber)Formula: IMAGINARY(2+3i)This formula will return 3, which is the imaginary coefficient of the complex number 2+3i. |
IMARGUMENT | The IMARGUMENT function returns the argument theta, which is the angle associated with the complex number in x + iy (x and y are real numbers). | Syntax: IMARGUMENT(inumber)Formula: IMARGUMENT(2+3i) returns 0.982793723247329 |
IMCONJUGATE | The IMCONJUGATE function returns the complex conjugate of a complex number. | Syntax: IMCONJUGATE(inumber)Formula: IMCONJUGATE(2+4i)Result: 2-4i |
IMCOS | The IMCOS function returns the cosine of an angle given in radians. | Syntax: IMCOS(number)Formula: To calculate the cosine of 2 radians, use the following formula:=IMCOS(2) The result would be -0.41614683654714. |
IMCOSH | The IMCOSH function returns the hyperbolic cosine of a complex number in x + yi or x + yj text format. | Syntax: IMCOSH(inumber)Formula: IMCOSH(1+2i)Result: 3.76219 + 0.96623i |
IMCOT | IMCOT is an Excel function that returns the imaginary part of a complex number. | Syntax: IMCOT(inumber)Formula: IMCOT(2+3i)Result: -3 |
IMCSC | The IMCSC function is used to calculate the complex conjugate of a complex number in Microsoft Excel. | Syntax: IMCSC(inumber)Formula: IMCSC(2+3i)Result: 2-3i |
IMCSCH | EDULE
The IMCSCHEDULE function in Microsoft Excel is used to calculate the number of payments for a loan or an annuity. |
Syntax: IMCSCHEDULE(principal, interest, payments, start_period, end_period, type)Formula: To calculate the number of payments for a loan of $1000 with an interest rate of 5% and a payment period of 12 months, the following formula can be used: IMCSCHEDULE(1000, 0.05, 12, 1, 12, 0) The result of this formula will be 12, meaning that the loan will have 12 payments. |
IMDIV | The IMDIV function returns the result of a complex number division in the imaginary number format. | Syntax: IMDIV(inumber1, inumber2)Formula: To divide the imaginary numbers 5i and 2i, the formula would be:=IMDIV(5i,2i) The result would be 2.5i. |
IMEXP | The IMEXP function is an Excel function that imports data from a text file into a worksheet. It is a useful tool for importing data from other sources into Excel. | Syntax: IMEXP(filename, [worksheet], [start_row], [start_column], [import_type], [import_options])Formula: IMEXP(“C:\\mydata.txt”, “Sheet1”, 1, 1, “Delimited”, “FieldsPerRecord=4”) |
IMLN | IMLN: The IMLN function returns the inverse matrix logarithm of a matrix. | Syntax: IMLN(matrix)Formula: IMLN(A1:B2)Where A1:B2 is a 2×2 matrix containing the values 1, 2, 3 and 4. The result of this formula would be the inverse matrix logarithm of the matrix. |
IMLOG10 | The IMLOG10 function returns the logarithm of a number to the base 10. | Syntax: IMLOG10(number)Formula:=IMLOG10(100) The result of this formula is 2, as the logarithm of 100 to the base 10 is 2. |
IMLOG2 | IMLOG2 is an Excel function used to calculate the binary logarithm of a number. The function takes a single argument, which is the number for which the binary logarithm is to be calculated. | Syntax: IMLOG2(number)Formula: IMLOG2(8)This formula will return 3, as the binary logarithm of 8 is 3. |
IMPOWER | The IMPOWER function is an Excel function that returns the given number raised to the power of another number. | Syntax: IMPOWER(number, power)Formula: IMPOWER(2,3)This will return 8, as 2 to the power of 3 is 8. |
IMPRODUCT | The IMPRODUCT function returns the product of two or more complex numbers. | Syntax: IMPRODUCT(inumber1,inumber2,…)Formula: IMPRODUCT(2+3i,4+5i)This would return 22+22i. |
IMREAL | IMREAL is an Excel function that returns TRUE if a number is a real number and FALSE if it is not. | Syntax: IMREAL(number)Formula: IMREAL(3.14)This example would return TRUE as 3.14 is a real number. |
IMSEC | The IMSEC function returns the number of seconds in a given time. | Syntax: IMSEC(time)Formula: IMSEC(“12:30:15”) returns 45015. |
IMSECH | The IMSECH function returns the inverse hyperbolic secant of a given number. | Syntax: IMSECH(number)Formula: IMSECH(0.5)The result of this function is 1.3169578969248. |
IMSIN | V
The IMSINV function returns the inverse of the matrix which is provided as an argument. |
Syntax: IMSINV(array)Formula: IMSINV({1,2,3;4,5,6;7,8,9}) returns the inverse of the matrix {1,2,3;4,5,6;7,8,9} as {-0.222, 0.111, 0.333; 0.167, -0.056, -0.139; 0.056, 0.167, -0.056}. |
IMSINH | The IMSINH function returns the hyperbolic sine of a given number. | Syntax: IMSINH(number)Formula: IMSINH(2) returns 1.919154444 |
IMSQRT | IMSQRT is an Excel function that returns the imaginary component of the square root of a complex number. | Syntax: IMSQRT(inumber)inumber – A complex number for which you want to find the imaginary component of the square root.Formula: IMSQRT(2+3i) This will return 1.5i, which is the imaginary component of the square root of 2+3i. |
IMSUB | The IMSUB function returns the difference between two arrays of any size. | Syntax: IMSUB(array1, array2)Formula:=IMSUB(A1:A5, B1:B5) This formula will return the difference between the two arrays A1:A5 and B1:B5. |
IMSUM | The IMSUM function calculates the sum of an array of numbers based on a given set of conditions. | Syntax: IMSUM(array, criteria)Formula: IMSUM(A1:A10, “>50”)This formula will sum all the numbers in the range A1:A10 that are greater than 50. |
IMTAN | The IMTAN function returns the modified Bessel function of the first kind, which is a mathematical function used to solve differential equations. | Syntax: IMTAN(x)Formula: IMTAN(2)This example returns the value 0.2078795763507619. |
INDEX | The INDEX function returns a value or reference of the cell at the intersection of a particular row and column within a range. | Syntax: INDEX(array, row_num, [column_num])Formula: INDEX(A1:C3, 2, 3)This formula will return the value at the intersection of the second row and third column of the range A1:C3. |
INDIRECT | The INDIRECT function returns a reference to a range. It is useful when you want to convert a text string into a valid reference. | Syntax: INDIRECT(ref_text, [a1])Formula: INDIRECT(“A1”) returns the value of cell A1. |
INFO | Description: The INFO function is an information function in Excel that returns information about the operating environment. |
Syntax: INFO(type)Formula: INFO(“numfile”)This example returns the number of currently open files. |
INT | The INT function in Excel returns the integer part of a number by rounding down to the nearest integer. | Syntax: INT(number)Formula: INT(3.14)Result: 3 |
INTERCEPT | The INTERCEPT function in Excel returns the y-intercept of a given linear regression line. It is used to calculate the point at which a line crosses the y-axis. | Syntax: INTERCEPT(known_y’s, known_x’s)Formula:=INTERCEPT(B2:B7,A2:A7) This formula returns the y-intercept of the linear regression line based on the data in cells A2:A7 and B2:B7. |
INTRATE | INTRATE calculates the interest rate for a fully invested security. | Syntax: INTRATE(settlement, maturity, investment, redemption, [basis])Formula: INTRATE(DATE(2020, 1, 1), DATE(2021, 1, 1), 1000, 1100, 0)This example calculates the interest rate for a security that is invested on January 1, 2020, matures on January 1, 2021, and has an investment of $1000 and a redemption of $1100. The basis is set to 0. |
IPMT | IPMT: The IPMT function in Excel is used to calculate the interest payment for a given period of an investment or loan. | Syntax: IPMT(rate, period, number_of_periods, present_value, [future_value], [type])Formula: To calculate the interest payment for the 3rd period of a loan with a rate of 5%, a present value of $1000 and a total of 10 periods, the formula would be:=IPMT(5%,3,10,1000) This would return a result of -$20.83, indicating that the interest payment for the 3rd period is $20.83. |
IRR | The IRR function in Excel is a financial function that returns the internal rate of return for a series of cash flows. The internal rate of return is the discount rate that sets the net present value of the cash flows to zero. | Syntax: IRR(values, [guess])Formula: IRR(A2:A7,0.1)This example uses the IRR function to calculate the internal rate of return for a series of cash flows in cells A2 to A7, with an initial guess of 0.1. |
ISBLANK | The ISBLANK function in Excel checks if a cell is empty or not. | Syntax: ISBLANK(value)Formula: ISBLANK(A1)This formula will return TRUE if cell A1 is empty, and FALSE if it is not. |
ISERR | The ISERR function in Microsoft Excel is a logical function that returns TRUE when the value provided is an error value other than #N/A, and FALSE when the value is not an error value. | Syntax: ISERR(value)Formula: ISERR(A1)In this example, the ISERR function will return TRUE if the value in cell A1 is an error value, and FALSE if it is not. |
ISERROR | The ISERROR function is used to check if a value is an error and returns either TRUE or FALSE. | Syntax: ISERROR(value)Formula: ISERROR(A1) |
ISEVEN | The ISEVEN function is used to determine whether a number is even or odd. | Syntax: ISEVEN(number)Formula: ISEVEN(2) will return TRUE because 2 is an even number. |
ISFORMULA | The ISFORMULA function in Excel is used to check if a cell contains a formula. It returns TRUE if the cell contains a formula and FALSE if it does not. | Syntax: ISFORMULA(cell)Formula: To check if cell A1 contains a formula, the following formula can be used:=ISFORMULA(A1) |
ISLOGICAL | The ISLOGICAL function is used to check if a value is a logical value (TRUE or FALSE). | Syntax: ISLOGICAL(value)Formula: ISLOGICAL(TRUE) returns TRUE |
ISNA | The ISNA function in Excel is used to check whether a given value is the #N/A error value. | Syntax: ISNA(value)Formula: ISNA(A1)If A1 contains the #N/A error value, then the ISNA function will return TRUE. Otherwise, it will return FALSE. |
ISNONTEXT | The ISNONTEXT function is used to check if a value is not text. It returns TRUE if the value is not text and FALSE if it is text. | Syntax: ISNONTEXT(value)Formula:=ISNONTEXT(“Hello”) This will return FALSE as “Hello” is text. |
ISNUMBER | The ISNUMBER function is used to determine if a value is a number or not. | Syntax: ISNUMBER(value)Formula: ISNUMBER(A1) will return TRUE if A1 contains a number, and FALSE if A1 does not contain a number. |
ISO.CEILING | ISO.CEILING is an Excel function that rounds a number to the nearest integer or multiple of significance. | Syntax: ISO.CEILING(number,significance)Formula: ISO.CEILING(3.14,0.1)Result: 3.2 |
ISODD | The ISODD function is used to check if a number is odd. It returns TRUE if the number is odd and FALSE if the number is even. | Syntax: ISODD(number)Formula: ISODD(3)This will return TRUE because 3 is an odd number. |
ISOMITTED | The ISOMITTED function in Excel checks whether a value is omitted from a list of values. | Syntax: ISOMITTED(value)Formula: ISOMITTED(A1) |
ISOWEEKNUM | The ISOWEEKNUM function returns the ISO week number of a given date. | Syntax: ISOWEEKNUM(date)Formula: ISOWEEKNUM(A2) where A2 contains a date value. |
ISPMT | The ISPMT function calculates the interest paid during a specific period of an investment. | Syntax: ISPMT(rate, period, number_of_periods, present_value)Formula: ISPMT(0.08, 3, 12, 10000)This example returns a result of -66.67, which is the interest paid during the 3rd period of an investment with a rate of 8%, 12 periods and a present value of 10,000. |
ISREF | The ISREF function checks if a cell reference is valid. | Syntax: ISREF(value)Formula: ISREF(A1) returns TRUE if A1 is a valid cell reference, FALSE if not. |
ISTEXT | The ISTEXT function is a built-in function in Excel that checks to see if a cell contains text. It returns TRUE if the value is text and FALSE if not. | Syntax: ISTEXT(value)Formula: ISTEXT(A1) This formula will return TRUE if the value in cell A1 is text and FALSE if not. |
JIS | The JIS function is a statistical function in Microsoft Excel that returns the Johnson System S_B probability distribution. The Johnson System S_B probability distribution is used to model the probability of a continuous variable that is bounded between two values. | Syntax: JIS(x, gamma, delta, lambda, xi)x: The value for which you want the probabilitygamma: The location parameter delta: The scale parameter lambda: The shape parameter xi: The shape parameter Formula: This example returns the probability of a continuous variable that is bounded between two values when x = 1.5, gamma = 0.5, delta = 1, lambda = 1, and xi = 0.5. |
KURT | KURT: The KURT function in Excel returns the kurtosis of a data set. Kurtosis is a measure of the “peakedness” of a distribution. | Syntax: KURT(number1, [number2],…)Formula: KURT(2,4,6,8,10)This formula would return -1.2, which is the kurtosis of the given data set. |
LAMBDA | The LAMBDA function in Excel is used to create custom functions. It allows users to create their own functions that can be used in formulas. It is a powerful tool that can be used to create complex formulas. | Syntax: LAMBDA(argument1, argument2, …, argument_n, expression)Arguments:argument1, argument2, …, argument_n: These are the arguments that will be used in the expression. expression: This is the expression that will be evaluated. Formula: This function adds two arguments (x and y) and returns the sum of the two. |
LARGE | The LARGE function returns the nth largest value from a range of values. | Syntax: LARGE(array, nth)Formula: LARGE(A1:A10, 3)This example would return the third largest value from the range of values in cells A1 to A10. |
LCM | LCM stands for Least Common Multiple. It is a function that is used to determine the smallest number that two or more numbers have in common. | Syntax: LCM(number1, [number2], …)Formula: LCM(2,3,4)This example would return 12, as 12 is the smallest number that 2, 3, and 4 all have in common. |
LEFT, LEFTBs | LEFT: The LEFT function in Excel returns the leftmost characters from a text string. |
Syntax: LEFT(text, [num_chars])text: The text string from which you want to extract the characters.num_chars: [optional] The number of characters you want to extract from the left of the text string. Formula: Result: Ex LEFTB: Syntax: LEFTB(text, [num_bytes]) text: The text string from which you want to extract the characters. num_bytes: [optional] The number of bytes you want to extract from the left of the text string. Example: Result: Exc |
LEN, LENBs | LEN:
The LEN function is used to count the number of characters in a text string. |
Syntax: LEN(text)Formula: LEN(“Hello World”)Result: 11 LENBs: Description: The LENBs function is used to count the number of bytes in a text string. Syntax: LENBs(text) LENBs(“Hello World”) Result: 11 |
LET | The LET function is a new function in Excel that allows you to assign a name to a value or expression. It is useful for making formulas easier to read and understand. | Syntax: LET(name, expression)Formula: Let(A, 10+5)This assigns the value 15 to the name A. |
LINEST | The LINEST function is a statistical function in Excel that returns the parameters of a linear trend. It can be used to calculate the slope and y-intercept of a line, as well as the correlation coefficient, standard error of the estimate, and other statistics. | Syntax: LINEST(known_y’s, [known_x’s], [const], [stats])Formula: LINEST(B2:B11,A2:A11,TRUE,TRUE) |
LN | The LN function returns the natural logarithm of a number. The natural logarithm is the logarithm to the base e. | Syntax: LN(number)Formula: To calculate the natural logarithm of 10, the formula would be:=LN(10) The result would be 2.302585092994046. |
LOG | The LOG function in Microsoft Excel returns the logarithm of a number to a specified base. | Syntax: LOG(number, [base])Formula: LOG(8,2)This example returns the logarithm of 8 to the base of 2, which is 3. |
LOG10 | The LOG10 function returns the logarithm of a number to the base 10. | Syntax: LOG10(number)Formula: LOG10(100)The result of this formula is 2, as 100 is equal to 10^2. |
LOGEST | The LOGEST function is used to calculate an exponential curve that best fits a set of data points. It returns an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient. | Syntax: LOGEST(known_y’s, [known_x’s], [const], [stats])Formula: LOGEST(A2:A10, B2:B10, TRUE, TRUE)This example will calculate an exponential curve that best fits the data points in cells A2:A10 and B2:B10, and return an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient. |
LOGINV | LOGINV is an Excel function that returns the inverse of the lognormal cumulative distribution. | Syntax: LOGINV(probability, mean, standard_dev)Formula: LOGINV(0.5, 0.5, 0.5) returns 1.29 |
LOGNORM.DIST | The LOGNORM.DIST function returns the cumulative lognormal distribution of x, given parameters for the distribution’s mean and standard deviation. | Syntax: LOGNORM.DIST(x, mean, standard_dev, cumulative)Formula: LOGNORM.DIST(2, 3, 0.5, TRUE)This example returns the cumulative lognormal distribution of 2, given a mean of 3 and a standard deviation of 0.5. |
LOGNORM.INV | The LOGNORM.INV function returns the inverse of the lognormal cumulative distribution for a specified value, mean and standard deviation. | Syntax: LOGNORM.INV(probability, mean, standard_dev)Formula: LOGNORM.INV(0.5, 2, 0.5)This example returns the inverse of the lognormal cumulative distribution for a probability of 0.5, a mean of 2 and a standard deviation of 0.5. The result is 1.897. |
LOGNORMDIST | The LOGNORMDIST function in Microsoft Excel returns the cumulative log-normal distribution for a given set of parameters. | Syntax: LOGNORMDIST(x, mean, standard_dev)Formula: To find the cumulative log-normal distribution for a value of x = 0.5, with a mean of 0.3 and a standard deviation of 0.2, the formula would be: LOGNORMDIST(0.5, 0.3, 0.2). The result of this formula is 0.788. |
LOOKUP | The LOOKUP function is used to look up a value in a row or column of data. It can be used to look up a value either vertically (in a column) or horizontally (in a row). | Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])Formula: LOOKUP(A2,B2:B6,C2:C6) |
LOWER | The LOWER function in Excel converts all letters in a text string to lowercase. | Syntax: LOWER(text)Formula: LOWER(“EXCEL FUNCTION”)Result: excel function |
MAKEARRAY | The MAKEARRAY function creates a single-column array from multiple values. | Syntax: MAKEARRAY(value1, [value2], [value3], …)Formula: MAKEARRAY(1,2,3,4)This will return an array of {1;2;3;4}. |
MAP | MAP is an Excel function that allows you to map one set of values to another set of values. It takes three arguments: lookup_value, from_array, and to_array. | Syntax: MAP(lookup_value, from_array, to_array)Formula: MAP(2, {1,2,3}, {10,20,30})This example would return 20 as the result. |
MATCH | MATCH is a function in Excel that looks for a specified item in a range of cells and returns the relative position of that item in the range. | Syntax: MATCH(lookup_value, lookup_array, [match_type])lookup_value: The value to be found in the lookup_array.lookup_array: The range of cells being searched. match_type: [optional] The number -1, 0, or 1. The match_type specifies how Excel matches lookup_value with values in lookup_array. Formula: This example looks for the value in cell A1 in the range of cells B1:B10. The match_type is set to 0, which means that Excel will find an exact match for the value in A1. The function will return the relative position of the value in A1 in the range B1:B10. |
MAX | The MAX function in Excel returns the largest value from a range of values. | Syntax: MAX(number1, [number2], …)Formula: MAX(A1:A10) This example returns the largest value from the range A1:A10. |
MAXA | The MAXA function is an Excel function that returns the maximum value in a range of values, including logical values and text. | Syntax: MAXA(value1, [value2], …)Formula: MAXA(A1:A10)This example returns the maximum value in the range of cells A1 to A10. |
MAXIFS | The MAXIFS function returns the maximum value in a range, based on multiple criteria. | Syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Formula: MAXIFS(A1:A10, B1:B10, “>=10”, C1:C10, “<=20”)This formula returns the maximum value in the range A1:A10, where the values in the range B1:B10 are greater than or equal to 10 and the values in the range C1:C10 are less than or equal to 20. |
MDETERM | MDETERM is an Excel function used to calculate the determinant of a given matrix. | Syntax: MDETERM(array)Formula: MDETERM({1,2;3,4})The result of this formula is -2, which is the determinant of the given matrix. |
MDURATION | The MDURATION function returns the Macauley duration of a security with an assumed par value of $100. | Syntax: MDURATION(settlement, maturity, coupon, yld, frequency, [basis])Formula: MDURATION(DATE(2018,1,1),DATE(2022,1,1),0.06,0.05,2,0)This example returns the Macauley duration of a security with a settlement date of January 1, 2018, a maturity date of January 1, 2022, a coupon rate of 6%, an annual yield of 5%, and a frequency of semi-annual payments, using a US (NASD) 30/360 day count basis. |
MEDIAN | The MEDIAN function returns the median of the given numbers. The median is the middle number in a set of numbers; it is calculated by arranging all the numbers from lowest to highest value and picking the middle number. | Syntax: MEDIAN(number1, [number2], …)Formula: MEDIAN(2, 4, 5, 7, 9)Result: 5 |
MID, MIDBs | MID: The MID function in Excel returns a specific number of characters from a text string, starting from the position you specify. | Syntax: MID(text, start_num, num_chars)Formula: MID(“Hello World”, 6, 5) This formula will return the text “World”. |
MIN | The MIN function returns the smallest value from a given set of numbers. | Syntax: MIN(number1, [number2], …)Formula: MIN(2,4,6,8)Result: 2 |
MINA | The MINA function is used to find the minimum value in a given array of values, including text and logical values. | Syntax: MINA(value1, [value2], …)Formula: MINA(2,5,8,1,6)This will return 1, which is the minimum value in the array. |
MINIFS | The MINIFS function is used to find the minimum value from a range of cells that meet multiple criteria. | Syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Formula: MINIFS(C2:C10,A2:A10,”>10″,B2:B10,”<20″)This formula will find the minimum value in the range C2:C10 where the values in A2:A10 are greater than 10 and the values in B2:B10 are less than 20. |
MINUTE | The MINUTE function in Excel returns the minute component of a given time. | Syntax: MINUTE(serial_number)Formula: MINUTE(A2) where A2 contains the time 10:15 AM. The result would be 15. |
MINVERSE | The MINVERSE function returns the inverse matrix for a given matrix. | Syntax: MINVERSE(array)Formula: MINVERSE({1,2;3,4})Result: {-2,1;1.5,-0.5} |
MIRR | MIRR: The MIRR function is an Excel financial function that returns the modified internal rate of return for a series of periodic cash flows. | Syntax: MIRR(values, finance_rate, reinvest_rate)Formula: MIRR(B2:B6, 10%, 20%)In this example, the MIRR function returns the modified internal rate of return for the cash flows in the range B2:B6, with a 10% finance rate and a 20% reinvestment rate. |
MMULT | The MMULT function returns the matrix product of two arrays. | Syntax: MMULT(array1, array2)Formula: MMULT(A1:B2,C1:D2) |
MOD | The MOD function returns the remainder after a number is divided by a divisor. | Syntax: MOD(number, divisor)Formula: MOD(15,4)This will return a result of 3, as 15 divided by 4 is 3 with a remainder of 3. |
MODE | MODE: The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data. | Syntax: MODE(number1, [number2], …)Formula: MODE(1,2,3,3,3,4,5)Result: 3 |
MODE.MULT | MODE.MULT is an Excel function that returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data. | Syntax: MODE.MULT(array)Formula: MODE.MULT(A2:A10)This formula returns the most frequently occurring value in the range A2:A10. |
MODE.SNGL | MODE.SNGL is an Excel function that returns the most frequently occurring value in a set of numbers. | Syntax: MODE.SNGL(number1,[number2],…)Formula: MODE.SNGL(1,2,2,3,3,3,4,4,4,4)The result of this example would be 4, since 4 is the most frequently occurring value in the set. |
MONTH | The MONTH function in Microsoft Excel is used to return the month number from a given date. | Syntax: MONTH(serial_number)Formula: MONTH(A2)In this example, the function will return the month number of the date in cell A2. |
MROUND | The MROUND function rounds a number to the nearest multiple of another number. | Syntax: MROUND(number, multiple)Formula: MROUND(7,2)The result of this formula is 8. |
MULTINOMIAL | The MULTINOMIAL function returns the multinomial coefficient of a set of numbers. | Syntax: MULTINOMIAL(number1, number2, …)Formula: MULTINOMIAL(2,3,4)This example returns the multinomial coefficient of 2, 3 and 4, which is 120. |
MUNIT | MUNIT is an Excel function that returns the unit of measure associated with a given number. | Syntax: MUNIT(number)Formula: MUNIT(10)This will return the unit of measure associated with 10, which is “none”. |
N | PER
The NPER function in Excel is used to calculate the number of periods for an investment based on a constant payment and a constant interest rate. |
Syntax: NPER(rate, pmt, pv, [fv], [type])Formula: To calculate the number of periods for an investment of $1000 with a payment of $50 and an interest rate of 5%:NPER(5%, -50, 1000) Result: 20 |
NA | The NA function returns the #N/A error value, which indicates that a value is not available. | Syntax: NA()Formula: NA() |
NEGBINOM.DIST | NEGBINOM.DIST is an Excel function that calculates the negative binomial distribution. The negative binomial distribution is a probability distribution that is used to represent the number of successes in a sequence of independent Bernoulli trials before a specified number of failures occur. | Syntax: NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)Where:number_f = the number of failures number_s = the number of successes probability_s = the probability of success cumulative = a logical value that determines the form of the function (TRUE for cumulative distribution function and FALSE for probability mass function) Formula: This formula returns the probability of having 2 successes in 4 independent Bernoulli trials with a probability of success of 0.5. |
NEGBINOMDIST | The NEGBINOMDIST function returns the probability of a given number of failures occurring before a specified number of successes in a negative binomial experiment. | Syntax: NEGBINOMDIST(number_f, number_s, probability_s)Formula: To calculate the probability of 3 failures occurring before 5 successes in a negative binomial experiment with a success probability of 0.4, the formula would be:=NEGBINOMDIST(3, 5, 0.4) The result would be 0.09216. |
NETWORKDAYS | NETWORKDAYS Function:
The NETWORKDAYS function calculates the number of whole working days between two dates. It excludes weekends and holidays from the calculation. |
Syntax: NETWORKDAYS(start_date, end_date, [holidays])Formula:=NETWORKDAYS(A2,A3) This example would calculate the number of working days between the dates in cells A2 and A3. |
NETWORKDAYS.INTL | NETWORKDAYS.INTL is an Excel function that calculates the number of workdays between two dates, excluding weekends and holidays. | Syntax: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])Formula: NETWORKDAYS.INTL(A1,B1,11,C1:C5)This example calculates the number of workdays between the dates in A1 and B1, with weekends set to Saturday and Sunday (11), and holidays in the range C1:C5. |
NOMINAL | NOMINAL: This function is used to calculate the nominal interest rate given the effective rate and the number of compounding periods per year. | Syntax: NOMINAL(effect_rate, npery)Formula: NOMINAL(0.1, 4)Result: 0.0943 |
NORM.DIST | NORM.DIST is an Excel function that returns the cumulative normal distribution for a given mean and standard deviation. It is used to calculate the probability that a random variable is less than or equal to a certain value. | Syntax: NORM.DIST(x, mean, standard_dev, cumulative)Where:x = The value for which you want to calculate the distribution. mean = The mean of the distribution. standard_dev = The standard deviation of the distribution. cumulative = A logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function. Formula: This formula returns the cumulative normal distribution for a mean of 1 and a standard deviation of 1, with a value of 2. The result is 0.97725. |
NORM.INV | NORM.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. The syntax is NORM.INV(probability, mean, standard_dev). | Syntax:
Formula: The above formula will return a value of 0, which is the inverse of the standard normal cumulative distribution at the probability of 0.5. |
NORM.S.DIST | NORM.S.DIST is an Excel statistical function that returns the standard normal cumulative distribution function. It gives the probability that a random variable with a normal distribution will be less than or equal to a specified value. | Syntax: NORM.S.DIST(x, cumulative)x: The value for which you want the distribution.Cumulative: A logical value that determines the form of the function. If cumulative is TRUE, NORM.S.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function. Formula: |
NORM.S.INV | NORM.S.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. This function is used to calculate the probability of a value being greater than or less than a certain number. | Syntax: NORM.S.INV(probability)Formula: To calculate the probability of a value being greater than 0.5, the formula would be NORM.S.INV(0.5). This would return 0.67448975019608. |
NORMDIST | NORMDIST is a statistical function in Microsoft Excel which returns the normal cumulative distribution for a specified mean and standard deviation. | Syntax: NORMDIST(x, mean, standard_dev, cumulative)Formula: To find the normal cumulative distribution for a mean of 10 and a standard deviation of 2, the formula would be =NORMDIST(10, 10, 2, TRUE). |
NORMINV | The NORMINV function returns the inverse of the normal cumulative distribution for a given mean and standard deviation. | Syntax: NORMINV(probability, mean, standard_dev)Formula: To find the inverse of the normal cumulative distribution for a probability of 0.5, a mean of 10 and a standard deviation of 2, the formula would be:=NORMINV(0.5, 10, 2) The result would be 10. |
NORMSDIST | The NORMSDIST function returns the cumulative normal distribution for a given value. It is used to calculate the probability that a random variable will take a value less than or equal to a given value. | Syntax: NORMSDIST(x)Formula: To calculate the probability that a random variable will take a value less than or equal to 0.5, the formula would be: =NORMSDIST(0.5) |
NORMSINV | NORMSINV is an Excel function that calculates the inverse of the standard normal cumulative distribution. It returns the value of the inverse standard normal distribution for a given probability. | Syntax: NORMSINV(probability)Formula: To calculate the inverse of the standard normal cumulative distribution for a probability of 0.5, the formula would be:=NORMSINV(0.5) This would return a value of 0, as the inverse of the standard normal cumulative distribution for a probability of 0.5 is 0. |
NOT | The NOT function is a logical function in Excel that returns the opposite of a given logical value. It takes a single argument that is either TRUE or FALSE and returns the opposite. | Syntax: NOT(logical)Formula: NOT(TRUE) returns FALSE. |
NOW | NOW: This function returns the current system date and time. | Syntax: NOW()Formula: NOW()This formula will return the current date and time, for 2/26/2020 4:54:00 PM |
NPER | The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. | Syntax: NPER(rate, pmt, pv, [fv], [type])Formula: To calculate the number of periods for an investment of $10,000 with a payment of $500 and an interest rate of 5%, the formula would be:=NPER(0.05, -500, 10000) The result is 18. |
NPV | NPV is a financial function in Excel used to calculate the net present value of an investment. It takes into account the initial investment, the rate of return, and the number of periods in the investment. | Syntax: NPV(rate, value1, value2, …)Formula: NPV(0.1, -1000, 500, 500, 500)This example would calculate the net present value of an investment with an initial investment of -1000, and three subsequent payments of 500 each at a rate of 0.1. |
NUMBERVALUE | The NUMBERVALUE function converts a text string that represents a number to a number. | Syntax: NUMBERVALUE(text, [decimal_separator], [group_separator])Formula: NUMBERVALUE(“$1,234.56”)This would return 1234.56. |
OCT2BIN | OCT2BIN is an Excel function that converts an octal number to a binary number. | Syntax: OCT2BIN(number, [places])number: The octal number that you want to convert to a binary number.places: (optional) The number of characters to use. Formula: Result: 1011 |
OCT2DEC | The OCT2DEC function converts an octal number to a decimal number in Microsoft Excel. | Syntax: OCT2DEC(number)Formula: OCT2DEC(77)This example would return the decimal number 63. |
OCT2HEX | The OCT2HEX function converts an octal number to a hexadecimal number. | Syntax: OCT2HEX(number, [places])Formula: OCT2HEX(11, 4)Result: 13 |
ODD | The ODD function is used to round a number up to the nearest odd integer. | Syntax: ODD(number)Formula: ODD(8.2)Result: 9 |
ODDFPRICE | ODDFPRICE is an Excel function that calculates the price per $100 face value of a security with an odd first period. | Syntax: ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])Formula: ODDFPRICE(DATE(2021,1,1),DATE(2021,7,1),DATE(2020,7,1),0.06,0.06,0.06,100,2,0)This example calculates the price per $100 face value of a security with a settlement date of January 1, 2021, a maturity date of July 1, 2021, an issue date of July 1, 2020, a first coupon of 0.06, a rate of 0.06, a yield of 0.06, a redemption value of 100, and a frequency of 2 (semi-annual). The basis is set to 0. |
ODDFYIELD | The ODDFYIELD function returns the odd yield of a security with an odd first period. | Syntax: ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])Formula: ODDFYIELD(DATE(2020,1,1),DATE(2023,1,1),DATE(2020,1,1),0.06,0.05,100,100,2,1)This example returns the odd yield of a security with a settlement date of January 1, 2020, a maturity date of January 1, 2023, an issue date of January 1, 2020, a first coupon of 0.06, a rate of 0.05, a price of 100, a redemption value of 100, and a frequency of 2 (semi-annually). The optional basis parameter is set to 1 (US (NASD) 30/360). |
ODDLPRICE | ODDLPRICE is an Excel function that returns the price per unit of an odd lot, which is an order for a quantity of stock that is less than the standard trading unit. | Syntax: ODDLPRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])Formula: ODDLPRICE(“1/1/2020″,”1/1/2021”,0.05,0.06,100,2,1)This example returns the price per unit of an odd lot with a settlement date of 1/1/2020, a maturity date of 1/1/2021, a rate of 0.05, a yield of 0.06, a redemption value of 100, and a frequency of 2 (semi-annual), using the US (NASD) 30/360 day count basis. |
ODDLYIELD | The ODDLYIELD function is used to calculate the yield of a security that pays odd interest payments. | Syntax: ODDLYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])Formula: ODDLYIELD(DATE(2021,1,1), DATE(2024,1,1), DATE(2020,7,1), DATE(2021,7,1), 0.05, 100, 100, 2, 0)This example calculates the yield of a security with a settlement date of 1/1/2021, maturity date of 1/1/2024, issue date of 7/1/2020, first coupon date of 7/1/2021, rate of 0.05, price of 100, redemption of 100, frequency of 2, and basis of 0. |
OFFSET | The OFFSET function returns a reference to a range that is a specified number of rows and columns from a given reference cell. | Syntax: OFFSET(reference, rows, cols, [height], [width])Formula: OFFSET(B2, 2, 3, 2, 1) This will return the range C4:C5, which is two rows and three columns away from cell B2. |
OR | The OR function is a logical function in Excel that returns either TRUE or FALSE based on one or more conditions. | Syntax: OR(logical1, [logical2], …)Formula: OR(A1=”Yes”, B1=”Yes”) |
PDURATION | PDURATION is a financial function in Excel that calculates the number of periods required to pay off a loan or make an investment given the present value, periodic payment, and future value. | Syntax: PDURATION(present_value, payment, future_value)Formula: Suppose you have a loan of $10,000 with a periodic payment of $200 and a future value of $0. The number of periods required to pay off the loan is calculated by using the PDURATION function as follows:=PDURATION(10000, 200, 0) The result is 50, meaning it will take 50 periods (months, years, etc.) to pay off the loan. |
PEARSON | The PEARSON function returns the Pearson product moment correlation coefficient, which is a measure of the linear correlation between two variables. | Syntax: PEARSON(array1, array2)Formula:=PEARSON(A1:A10,B1:B10) |
PERCENTILE | The PERCENTILE function returns the k-th percentile of values in a range. | Syntax: PERCENTILE(array,k)Formula: PERCENTILE(A1:A10,0.5)This example returns the 50th percentile of the values in cells A1 through A10. |
PERCENTILE.EXC | The PERCENTILE.EXC function is an Excel function that returns the k-th percentile of values in a range. | Syntax: PERCENTILE.EXC(array, k)Formula: PERCENTILE.EXC(A1:A10, 0.5)This example will return the 50th percentile of the values in the range A1:A10. |
PERCENTILE.INC | The PERCENTILE.INC function in Excel returns the k-th percentile of values in a range. | Syntax: PERCENTILE.INC(array, k)Formula: PERCENTILE.INC(B2:B10, 0.5) This example would return the 50th percentile of the values in the range B2:B10. |
PERCENTRANK | The PERCENTRANK function in Excel returns the rank of a value in a data set as a percentage of the data set. | Syntax: PERCENTRANK(array, x, [significance])Formula: PERCENTRANK(A2:A10, A2)This formula will return the rank of the value in cell A2 as a percentage of the values in the range A2:A10. |
PERCENTRANK.EXC | The PERCENTRANK.EXC function returns the rank of a value in a data set as a percentage of the data set. | Syntax: PERCENTRANK.EXC(array, x, [significance])Formula: PERCENTRANK.EXC(A2:A10, A7, 4)In this example, the PERCENTRANK.EXC function returns the rank of the value in cell A7 (which is 5) as a percentage of the values in the range A2:A10. The third argument, 4, specifies that the result should be calculated to four decimal places. |
PERCENTRANK.INC | The PERCENTRANK.INC function returns the rank of a value in a data set as a percentage of the data set. | Syntax: PERCENTRANK.INC(array, x, [significance])Formula: PERCENTRANK.INC(A2:A8,A4,4)In this example, the PERCENTRANK.INC function would return the rank of the value in cell A4 as a percentage of the data set in cells A2 to A8, with 4 decimal places of significance. |
PERMUT | The PERMUT function is used to calculate the number of permutations for a given number of objects. A permutation is the arrangement of a set of objects in a specific order. | Syntax: PERMUT(number, number_chosen)Formula: To calculate the number of permutations of 8 objects taken 3 at a time, the formula would be: =PERMUT(8,3)The result would be 336. |
PERMUTATIONA | The PERMUTATIONA function returns the number of permutations for a given number of objects that can be selected from number objects. | Syntax: PERMUTATIONA(number, number_chosen)Formula: PERMUTATIONA(5,2)This example returns the number of permutations for 5 objects that can be selected from 2 objects, which is 10. |
PHI | PHI is a statistical function in Excel that calculates the value of the Phi coefficient, which is a measure of association between two binary variables. It is often used to measure the strength of a linear relationship between two variables. | Syntax: PHI(x, y)Formula: PHI(A1:A10, B1:B10) |
PHONETIC | The PHONETIC function is used to convert text into phonetic pronunciation. This function is useful for creating a phonetic representation of names or words, which can help differentiate between similar sounding words. | Syntax: PHONETIC(text)Formula: PHONETIC(“Excel”)Result: “ECKS-ELL” |
PI | The PI function in Excel returns the value of pi (p), which is the ratio of the circumference of a circle to its diameter. | Syntax: PI()Formula: PI()Result: 3.14159265358979 |
PMT | PMT is an Excel function used to calculate the periodic payment for a loan. The syntax is PMT(rate, nper, pv, [fv], [type]).
Rate is the interest rate per period. |
Syntax:
Formula: |
POISSON | The POISSON function in Excel calculates the probability of a given number of events occurring in a fixed period of time, given the average rate of occurrence. | Syntax: POISSON(x, mean, cumulative)x = The number of events mean = The expected mean number of events cumulative = A logical value that determines the form of the function (TRUE for cumulative, FALSE for probability)Formula: POISSON(4,6,FALSE) This example would calculate the probability of 4 events occurring in a fixed period of time, given the average rate of occurrence is 6. The result would be 0.1839. |
POISSON.DIST | The POISSON.DIST function in Excel returns the probability of a given number of events occurring in a fixed period of time when the rate of events is known. | Syntax: POISSON.DIST(x,mean,cumulative)Formula: POISSON.DIST(5,3,FALSE)This formula returns the probability of 5 events occurring in a given period of time when the rate of events is 3. |
POWER | Description: The POWER function is a mathematical function that returns the result of a number raised to a specified power. |
Syntax: POWER(number, power)Formula: POWER(2,3)This will return 8, as 2 to the power of 3 is 8. |
PPMT | The PPMT function in Excel calculates the principal payment for a given period for an investment based on a constant periodic payment and a constant interest rate. | Syntax: PPMT(rate, period, number_of_periods, present_value, future_value, type)Formula: To calculate the principal payment for the 3rd period of a loan with a 6% annual interest rate, a present value of $10,000, and a total of 5 periods, the formula would be:=PPMT(6%,3,5,10000,0,0) The result would be -$2,037.50, which is the principal payment for the 3rd period. |
PRICE | The PRICE function is used to calculate the price per $100 face value of a security that pays periodic interest. | Syntax: PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])Formula: PRICE(DATE(2020,1,1), DATE(2022,1,1), 0.05, 0.06, 100, 2, 0)This example returns the price per $100 face value of a security that pays semiannual interest at a rate of 5%, with a yield of 6%, and a redemption value of $100. The settlement date is January 1, 2020, and the maturity date is January 1, 2022. |
PRICEDISC | PRICEDISC is an Excel function that calculates the price of a discounted security. It takes three arguments: the settlement date, the maturity date, and the discount rate. | Syntax: PRICEDISC(settlement, maturity, discount)Formula: PRICEDISC(“2/2/2020”, “2/2/2021”, 0.05)This example would calculate the price of a security with a settlement date of 2/2/2020, a maturity date of 2/2/2021, and a discount rate of 0.05. |
PRICEMAT | PRICEMAT is an Excel function that calculates the price of a security based on its yield. It takes three arguments: settlement, maturity, and rate. | Syntax: PRICEMAT(settlement, maturity, rate)Formula: PRICEMAT(A1, A2, A3) where A1 = settlement date, A2 = maturity date, A3 = rate |
PROB | The PROB function in Microsoft Excel is used to calculate the probability of a given event. It returns the probability that values in a range are between two limits. | Syntax: PROB(x_range,prob_range,lower_limit,upper_limit)Formula: PROB(A1:A5,B1:B5,20,30) |
PRODUCT | The PRODUCT function multiplies all the numbers given as arguments and returns the product. | Syntax: PRODUCT(number1, [number2], …)Formula: PRODUCT(2,3,4,5)Result: 120 |
PROPER | The PROPER function is used to convert a text string to proper case, which means that the first letter of each word is capitalized and all other letters are lowercase. | Syntax: PROPER(text)Formula: PROPER(“this is a test”)Result: This Is A Test |
PV | The PV function calculates the present value of an investment, which is the total amount that a series of future payments is worth now. | Syntax: PV(rate, nper, pmt, [fv], [type])Formula: To calculate the present value of an investment that will pay $1000 at the end of each year for 10 years, with an interest rate of 5%, the formula would be:=PV(5%, 10, 1000) The result would be: -$7,637.45 |
QUARTILE | The QUARTILE function returns the quartile of a given set of data. Quartiles divide a set of data into four equal parts. | Syntax: QUARTILE(array, quart)Formula: QUARTILE(A2:A10,2)This example returns the second quartile of the data set in cells A2 to A10. |
QUARTILE.EXC | The QUARTILE.EXC function returns the quartile of a dataset, which is a type of statistical measure. | Syntax: QUARTILE.EXC(array, quart)Formula: QUARTILE.EXC(A1:A10,3)This example returns the third quartile of the data in cells A1 through A10. |
QUARTILE.INC | The QUARTILE.INC function returns the quartile of a given data set, where quartiles divide a ranked set of data into four equal parts. | Syntax: QUARTILE.INC(array, quart)Array: The array or range of cells containing numerical data for which you want the quartile value.Quart: A number specifying which quartile value to return. Formula: |
QUOTIENT | The QUOTIENT function returns the integer portion of a division. | Syntax: QUOTIENT(numerator, denominator)Formula: QUOTIENT(10,3) returns 3 |
RADIANS | The RADIANS function is used to convert angles from degrees to radians. | Syntax: RADIANS(angle)Formula: RADIANS(45)This function would return the result 0.78539816339745. |
RAND | The RAND function in Excel is used to generate a random number between 0 and 1. | Syntax: RAND()Formula: RAND()Result: 0.838592582 |
RANDARRAY | The RANDARRAY function is an Excel function that generates an array of random numbers between two specified numbers. | Syntax: RANDARRAY(rows, columns, min, max)Formula: RANDARRAY(2,3,1,10) This will generate a 2×3 array of random numbers between 1 and 10. |
RANDBETWEEN | The RANDBETWEEN function is used to generate a random number between two specified numbers. | Syntax: RANDBETWEEN(bottom, top)Formula: RANDBETWEEN(1,10) This will generate a random number between 1 and 10. |
RANK | The RANK function in Excel is used to return the rank of a number within a set of numbers. | Syntax: RANK(number,ref,[order])Formula: RANK(A2,A2:A7,1)In this example, the RANK function will return the rank of the number in cell A2 within the range of cells A2 to A7, in ascending order. |
RANK.AVG | The RANK.AVG function returns the rank of a number in a list of numbers. It assigns the same rank to numbers with the same value. | Syntax: RANK.AVG(number,ref,[order])Formula: RANK.AVG(B2,B2:B7,1)This example returns the rank of the number in cell B2 in the range B2:B7, in ascending order. |
RANK.EQ | The RANK.EQ function returns the rank of a number in a list of numbers. | Syntax: RANK.EQ (number, reference, [order])Formula: RANK.EQ (5, {1,2,3,5,6,7}, 0)The example returns 4, as 5 is the 4th number in the list. |
RATE | The RATE function is used to calculate the interest rate per period of an annuity. | Syntax: RATE(nper, pmt, pv, [fv], [type], [guess])Formula: To calculate the interest rate per period for a loan of $2,000 to be paid back in 5 years with a payment of $400 per period, the formula would be:=RATE(5, -400, 2000) The result would be 0.05, indicating a 5% interest rate per period. |
RECEIVED | The RECEIVED function is a financial function in Microsoft Excel that returns the amount received at maturity for a fully invested security. | Syntax: RECEIVED(settlement, maturity, investment, discount, [basis])Formula: RECEIVED(A2,A3,A4,A5,A6)In this example, A2 is the settlement date, A3 is the maturity date, A4 is the investment amount, A5 is the discount rate, and A6 is the basis (optional). |
REDUCE | The REDUCE function is an Excel function that reduces a range of values by applying a given function. It is a dynamic array function that can take multiple values and return a single result. | Syntax: REDUCE(function, range[, initial])Formula: REDUCE(SUM, A1:A10, 0)This example will return the sum of the values in the range A1:A10, starting with an initial value of 0. |
REGISTER.ID | The REGISTER.ID function is a Microsoft Excel function that returns the registration identifier of the current workbook. | Syntax: REGISTER.ID()Formula: REGISTER.ID() |
REPLACE, REPLACEBs | REPLACE: Replaces characters within text. | Syntax: REPLACE(old_text, start_num, num_chars, new_text)Formula: REPLACE(“Hello World”, 6, 5, “Universe”)Result: Hello Universe REPLACEB: Replaces characters within text based on the number of bytes. Syntax: REPLACEB(old_text, start_num, num_bytes, new_text) REPLACEB(“Hello World”, 6, 5, “Universe”) Result: Hello Universe |
REPT | The REPT function in Excel is used to repeat a text string a specified number of times. | Syntax: REPT(text, number_times)Formula: REPT(“Hello”, 3)Result: HelloHelloHello |
RIGHT, RIGHTBs | The RIGHT function in Excel returns the specified number of characters from the right of a supplied text string. | Syntax: RIGHT(text, [num_chars])Formula: RIGHT(“Hello World”, 5)Result: “World” |
ROMAN | ROMAN is an Excel function that converts a number to a Roman numeral. | Syntax: ROMAN(number, [form])number: The number to be converted to a Roman numeral.form: [optional] An integer that specifies the type of Roman numeral you want. Formula: Result: X |
ROUND | The ROUND function rounds a number to a specified number of digits. | Syntax: ROUND(number, num_digits)Formula: ROUND(3.14159, 2)This example would return 3.14. |
ROUNDDOWN | The ROUNDDOWN function rounds a number down to the specified number of decimal places. | Syntax: ROUNDDOWN(number, num_digits)Formula: ROUNDDOWN(2.567,2)Result: 2.56 |
ROUNDUP | The ROUNDUP function rounds a number up to a specified number of decimal places. | Syntax: ROUNDUP(number, num_digits)Formula: ROUNDUP(3.14159, 2) returns 3.15 |
ROW | The ROW function returns the row number of a cell reference. | Syntax: ROW([reference])Formula: ROW(A1) returns 1. |
ROWS | The ROWS function returns the number of rows in a given array or range. | Syntax: ROWS(array)Formula: ROWS(A1:B10) This will return the number 10, as there are 10 rows in the range A1:B10. |
RRI | RRI: The RRI function is used to calculate the rate of return on an investment over a period of time. | Syntax: RRI(nper, pv, fv)nper: The total number of periods over which the investment is made.pv: The present value of the investment. fv: The future value of the investment. Formula: This example calculates the rate of return on an investment of $1000 that will be worth $2000 after 5 periods. The result is 0.1487, or 14.87%. |
RSQ | RSQ is a statistical function in Microsoft Excel that returns the square of the Pearson product-moment correlation coefficient (R-squared) of two supplied sets of values. It is used to measure how closely two sets of data points fit a given regression line. | Syntax: RSQ(known_y’s, known_x’s)Formula: RSQ(A1:A6,B1:B6) |
RTD | The RTD function in Excel is used to retrieve real-time data from a program that supports COM automation. | Syntax: RTD(progID, server, topic1, [topic2], …)Formula: RTD(“MSFTQuote.RTD”,,”MSFT”) |
SCAN | The SCAN function searches for specific characters within a text string and returns the text string from the start position to the character found. | Syntax: SCAN(text, character_set, [start_num], [num_chars])Formula: SCAN(“Hello World”, ” “, 1, 4)The example above will return “Hello” as the text string from the start position of 1 to the character found, which is a space. |
SEARCH, SEARCHBs | SEARCH: The SEARCH function returns the location of a string of text within another string of text. | Syntax: SEARCH(find_text, within_text, [start_num])Formula: SEARCH(“x”, “Excel”)Result: 4 SEARCHB: The SEARCHB function is similar to the SEARCH function, but is case-sensitive. Syntax: SEARCHB(find_text, within_text, [start_num]) SEARCHB(“X”, “Excel”) Result: #VALUE! |
SEC | The SEC function returns the secant of an angle, which is the reciprocal of the cosine of that angle. | Syntax: SEC(angle)Formula: SEC(45) This will return the secant of 45 degrees, which is 1.4142135623731. |
SECH | The SECH function returns the hyperbolic secant of a given number. | Syntax: SECH(number)Formula: SECH(1)The result of this formula is 0.85091812823932. |
SECOND | The SECOND function returns the second part of a time value. | Syntax: SECOND(time_value)Formula: SECOND(A1) where A1 contains the time value 09:15:30, the result would be 30. |
SEQUENCE | The SEQUENCE function generates a list of sequential numbers in an array, based on user-defined parameters. | Syntax: SEQUENCE(rows, columns, [start], [step])Formula: SEQUENCE(3,2,1,2)This would generate the following array: {1,3; 5,7; 9,11} |
SERIESSUM | The SERIESSUM function is an Excel function that calculates the sum of a series of terms in a power series. | Syntax: SERIESSUM(x, n, m, coefficients)x: The input value for the power series.n: The power of the first term in the series. m: The power of the last term in the series. coefficients: The coefficients of the terms in the series. Formula: =SERIESSUM(5, 0, 5, 1,2,3,4,5,6) The result of this formula is 441. |
SHEET | SHEET is an Excel function that returns the sheet number of a reference. | Syntax: SHEET(reference)Formula: SHEET(A1) This would return the sheet number of the cell A1. |
SHEETS | The SHEETS function returns the number of sheets in a reference. | Syntax: SHEETS(reference)Formula: SHEETS(“Sheet1:Sheet3”) This would return the number 3, since the reference includes three sheets. |
SIGN | The SIGN function returns the sign of a number. It returns 1 if the number is positive, 0 if the number is 0, and -1 if the number is negative. | Syntax: SIGN(number)Formula: SIGN(5)This formula will return 1, since 5 is a positive number. |
SIN | The SIN function returns the sine of a given angle. | Syntax: SIN(angle)Formula: SIN(45)Result: 0.70710678118 |
SINH | The SINH function returns the hyperbolic sine of a given number. | Syntax: SINH(number)Formula: SINH(2) returns 3.626860407847019 |
SKEW | SKEW: The SKEW function returns the skewness of a distribution based on a supplied set of values. Skewness is a measure of asymmetry in a probability distribution. | Syntax: SKEW(number1,[number2],…)Formula: SKEW(A2:A10)This function will return the skewness of the distribution of the values in cells A2 through A10. |
SKEW.P | SKEW.P is a statistical function in Microsoft Excel that calculates the skewness of a distribution based on a population of numerical data. | Syntax: SKEW.P(number1,[number2],…)Formula: SKEW.P(2,4,6,8,10)The result of this formula is 0, indicating that the data set is perfectly symmetrical. |
SLN | SLN: The SLN function is used to calculate the straight-line depreciation of an asset for a single period. | Syntax: SLN(cost, salvage, life)Formula: SLN(1000,200,5)This example would calculate the straight-line depreciation of an asset with a cost of 1000, a salvage value of 200, and a life of 5 years. The result would be 160. |
SLOPE | The SLOPE function calculates the slope of a regression line based on the given data points. It is used to measure the rate of change in a data set. | Syntax: SLOPE (known_y’s, known_x’s)Formula: SLOPE (A1:A10, B1:B10) |
SMALL | The SMALL function returns the nth smallest value from a given set of values. | Syntax: SMALL(array, nth_smallest)Formula: SMALL(A1:A10, 3)This example returns the 3rd smallest value from the range A1:A10. |
SORT | The SORT function in Excel allows users to sort a range of data by one or more columns or rows in either ascending or descending order. | Syntax: SORT(array, [sort_index], [sort_order], [by_col])Formula: SORT(A2:D6, 2, 1, TRUE)This example will sort the range of data in A2:D6 by the second column in ascending order. |
SORTBY | The SORTBY function sorts a range of data based on one or more columns. | Syntax: SORTBY(array, sort_column1, [sort_order1], [sort_column2], [sort_order2], …)Formula: SORTBY(A2:F6, 2, 1, 5, -1)This example will sort the range A2:F6 based on the values in column 2 in ascending order and the values in column 5 in descending order. |
SQRT | The SQRT function in Excel returns the square root of a given number. | Syntax: SQRT(number)Formula: SQRT(9)Result: 3 |
SQRTPI | The SQRTPI function returns the square root of a number multiplied by pi. | Syntax: SQRTPI(number)Formula: SQRTPI(2) The result of this formula is 2.506628274631. |
STANDARDIZE | The STANDARDIZE function returns a normalized value from a distribution characterized by a mean and standard deviation. | Syntax: STANDARDIZE(x, mean, standard_dev)Formula: STANDARDIZE(3, 5, 2)This example returns -1, which is the normalized value from a distribution with a mean of 5 and a standard deviation of 2. |
STDEV | STDEV is an Excel function that calculates the standard deviation of a set of numbers. It is used to measure the amount of variation or dispersion from the average of a set of values. | Syntax: STDEV(number1, [number2], …)Formula: STDEV(2,4,6,8,10)The result of this example would be 2.87. |
STDEV.P | STDEV.P is an Excel function that calculates the standard deviation of a population based on a sample of numeric values. | Syntax: STDEV.P(number1, [number2], …)Formula: STDEV.P(1,2,3,4,5)This example would return 1.58113883008419. |
STDEV.S | STDEV.S is an Excel function that calculates the standard deviation of a sample based on a supplied set of values. | Syntax: STDEV.S(number1, [number2], …)Formula: STDEV.S(2,4,6,8)This will return 2, the standard deviation of the sample set. |
STDEVA | STDEVA is an Excel function that returns the standard deviation of a population based on a sample of numeric values. | Syntax: STDEVA(number1, [number2], …)Formula: STDEVA(2,4,6,8,10)This example returns 2.58198889747161, which is the standard deviation of the given sample. |
STDEVP | STDEVP is a statistical function in Microsoft Excel that calculates the standard deviation of a population based on a sample of numbers. | Syntax: STDEVP(number1, [number2], …)Formula: STDEVP(2, 4, 6, 8)This would return 2, which is the standard deviation of the sample set (2, 4, 6, 8). |
STDEVPA | STDEVPA is an Excel function that calculates the standard deviation of a population based on a sample of numbers. It takes the following syntax:
STDEVPA(number1, [number2], …) Where number1, number2, etc. are the numeric values for which you want to calculate the standard deviation. |
Syntax:
Formula: This will return 2.87228132326901. |
STEYX | STEYX is a statistical function in Microsoft Excel that calculates the standard error of the predicted y-value for each x in the regression. | Syntax: STEYX(known_y’s, known_x’s)Formula: STEYX(A2:A6, B2:B6) |
STOCKHISTORY | STOCKHISTORY is an Excel function that retrieves historical stock prices and other related information from Yahoo Finance. | Syntax: STOCKHISTORY(ticker, [start_date], [end_date], [interval], [data_items])Formula: STOCKHISTORY(“AAPL”, “1/1/2020”, “1/31/2020”, “daily”, “close”) |
SUBSTITUTE | The SUBSTITUTE function in Excel replaces existing text with new text in a provided text string. | Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])Formula: SUBSTITUTE(A2,”red”,”blue”) This example would replace all instances of “red” with “blue” in the text string in cell A2. |
SUBTOTAL | The SUBTOTAL function is used to calculate a subtotal in a list or database. It can perform calculations such as sum, average, count, etc. | Syntax: SUBTOTAL(function_num, range1, range2,…)Function_num: This is a number that specifies the type of calculation to perform.Range1, Range2,…: This is a range of cells or array of values to perform the calculation on. Formula: This example would calculate the sum of the range A2:A10. |
SUM | The SUM function is an Excel function used to add up a range of values. | Syntax: SUM(number1, [number2], …)Formula: SUM(A1:A5) |
SUMIF | The SUMIF function in Excel is used to sum values that meet a certain criteria. | Syntax: SUMIF(range, criteria, [sum_range])Formula: SUMIF(A2:A10,”>20″,B2:B10) This formula will sum all values in B2:B10 that are greater than 20, based on the corresponding values in A2:A10. |
SUMIFS | The SUMIFS function is an Excel function used to sum values in a range that meet multiple criteria. | Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Formula: To sum the values in cell range A1:A10, if the corresponding values in cell range B1:B10 are greater than 5, the formula would be:=SUMIFS(A1:A10,B1:B10,”>5″) |
SUMPRODUCT | The SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products. | Syntax: SUMPRODUCT(array1, [array2], [array3], …)Formula: SUMPRODUCT(A1:A5, B1:B5) |
SUMSQ | The SUMSQ function is an Excel function that returns the sum of the squares of a set of numbers. | Syntax: SUMSQ(number1, [number2], …)Formula: SUMSQ(2,3,4)This example will return the sum of the squares of 2, 3, and 4, which is 29. |
SUMX2MY2 | Description: The SUMX2MY2 function in Microsoft Excel calculates the sum of the squares of the differences between two arrays of numbers. |
Syntax: SUMX2MY2(array_x, array_y)Formula: SUMX2MY2({1,2,3},{4,5,6})This formula will calculate the sum of the squares of the differences between the two arrays, which is equal to 54. |
SUMX2PY2 | The SUMX2PY2 function adds the sum of the squares of two arrays and then returns the square root of the result. | Syntax: SUMX2PY2(array1, array2)Formula: SUMX2PY2(A1:A5,B1:B5)This will return the square root of the sum of the squares of the values in A1:A5 and B1:B5. |
SUMXMY2 | The SUMXMY2 function returns the sum of the difference of two arrays, or ranges, of numbers. | Syntax: SUMXMY2(array1, array2)Formula: SUMXMY2(A1:A5,B1:B5) |
SWITCH | The SWITCH function is a logical function in Microsoft Excel that evaluates an expression against multiple conditions and returns a result corresponding to the first matching condition. | Syntax: SWITCH (expression, value_1, result_1, [value_2, result_2], … [default])Formula: SWITCH(A1,”A”,1,”B”,2,”C”,3,”D”,4,0)In this example, the SWITCH function will evaluate the value in cell A1 and return a result based on the following conditions: If A1 = “A”, the result is 1 |
SYD | The SYD function returns the sum-of-years’ digits depreciation of an asset for a specified period. | Syntax: SYD(cost, salvage, life, period)Formula: SYD(10000, 1000, 5, 3)This example returns the sum-of-years’ digits depreciation of an asset with a cost of $10,000, a salvage value of $1,000, and a useful life of 5 years for the 3rd period. The result is $2,400. |
T | .TEST
T.TEST is a statistical function in Excel that calculates the probability associated with a Student’s t-test. It is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. |
Syntax: T.TEST(array1, array2, tails, type)Array1: The first array or range of dataArray2: The second array or range of data Tails: The number of tails in the test, either 1 or 2 Type: The type of t-test to be used, either “1” for a paired two-sample t-test or “2” for a two-sample t-test with unequal variances Formula: This example would calculate the probability associated with a two-sample t-test with unequal variances using the data in range A1:A10 and B1:B10. |
T.DIST | T.DIST is an Excel function that returns the probability associated with a Student’s t-distribution. | Syntax: T.DIST(x,deg_freedom,cumulative)Formula: T.DIST(1.5,5,TRUE)This example returns the cumulative probability associated with a t-distribution with a value of 1.5 and 5 degrees of freedom. |
T.DIST.2T | The T.DIST.2T function returns the probability associated with a Student’s t-distribution. It can be used to determine the probability of a given range of values. | Syntax: T.DIST.2T(x,deg_freedom,cumulative)Formula: To calculate the probability of obtaining a value of 2 or less in a t-distribution with 20 degrees of freedom, the formula would be =T.DIST.2T(2,20,TRUE). This will return the probability of 0.97725. |
T.DIST.RT | The T.DIST.RT function returns the right-tailed Student’s t-distribution. The function is used to calculate probabilities for a given value of the t-distribution. | Syntax: T.DIST.RT(x,deg_freedom,cumulative)Formula: T.DIST.RT(2,5,TRUE)This example returns the cumulative probability of 2 for a t-distribution with 5 degrees of freedom. |
T.INV | T.INV is an Excel function that returns the t-value of the Student’s t-distribution, given the probability and the degrees of freedom. | Syntax: T.INV(probability, degrees_of_freedom)Formula: T.INV(0.95, 10)This example returns the t-value of 0.816496580927726, which is the t-value for a 95% confidence interval with 10 degrees of freedom. |
T.INV.2T | The T.INV.2T function returns the two-tailed probability of the Student’s t-distribution. | Syntax: T.INV.2T(probability,deg_freedom)Formula: T.INV.2T(0.05,10)This example returns the two-tailed probability of the Student’s t-distribution with a probability of 0.05 and 10 degrees of freedom. The result is 2.228. |
T.TEST | T.TEST is an Excel statistical function used to calculate the probability associated with the Student’s t-test. The syntax for the T.TEST function is:
T.TEST(array1, array2, tails, type) Where: • array1 – The first array or range of data. |
Syntax:
Formula: This example would calculate the two-tailed probability associated with the paired t-test of the data in the range A1:A10 and B1:B10. |
TAKE | TAKE is an Excel function that returns a specified number of characters from the start of a text string. | Syntax: TAKE(text,num_chars)Formula: TAKE(“Excel”,3)Result: Exc |
TAN | The TAN function returns the tangent of a given angle. | Syntax: TAN(angle)Formula: TAN(45) returns the value 1. |
TANH | The TANH function returns the hyperbolic tangent of a given number. | Syntax: TANH(number)Formula: TANH(0.5)The result of this formula is 0.4621171572600098 |
TBILLEQ | The TBILLEQ function is used to calculate the bond-equivalent yield of a treasury bill. | Syntax: TBILLEQ(settlement, maturity, discount)Formula: TBILLEQ(DATE(2020,1,1), DATE(2020,2,1), 0.05)This example would calculate the bond-equivalent yield of a treasury bill with a settlement date of January 1, 2020, a maturity date of February 1, 2020, and a discount rate of 0.05. |
TBILLPRICE | TBILLPRICE is an Excel function that calculates the price of a Treasury bill based on the rate, issue date, and maturity date. | Syntax: TBILLPRICE(settlement, maturity, discount)Formula: TBILLPRICE(3/1/2020, 6/1/2020, 0.05)The result of this example would be 99.876. |
TBILLYIELD | The TBILLYIELD function returns the yield for a Treasury bill, given the discount rate, settlement date, and maturity date. | Syntax: TBILLYIELD(discount, settlement, maturity)Formula: TBILLYIELD(0.05, “1/1/2020”, “4/1/2020”) returns 0.0541 |
TDIST | The TDIST function returns the probability associated with a Student’s t-distribution. | Syntax: TDIST(x,deg_freedom,tails)Formula: TDIST(2,5,1)This example returns the probability associated with a Student’s t-distribution for x=2, degrees of freedom=5, and 1 tail. |
TEXT | The TEXT function is used to convert a value to text in a specific number format. | Syntax: TEXT(value, format_text)Formula: TEXT(A1,”$#,##0.00″) |
TEXTAFTER | The TEXTAFTER function returns the characters after a given character or characters in a text string. | Syntax: TEXTAFTER(text, character)Formula: TEXTAFTER(“Hello World”,”o”)Result: ” World” |
TEXTBEFORE | The TEXTBEFORE function is a text function in Microsoft Excel that returns the text before a specific character in a string. | Syntax: TEXTBEFORE(text, delimiter)Formula: TEXTBEFORE(“www.example.com”,”.”)Result: www |
TEXTJOIN | The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that is combined. | Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)Formula: TEXTJOIN(“, “, TRUE, B2:B4)This example combines the text from cells B2, B3, and B4, and includes a comma and space between each value. The “TRUE” argument ignores any empty cells in the range. |
TEXTSPLIT | The TEXTSPLIT function splits a text string into multiple parts based on a specified delimiter. | Syntax: TEXTSPLIT(text, delimiter)Formula: TEXTSPLIT(“Apple,Banana,Orange”,”,”)This formula will return the result “Apple, Banana, Orange”. |
TIME | The TIME function is used to construct a time value from hour, minute, and second values. | Syntax: TIME(hour, minute, second)Formula: TIME(14,30,45) returns the value 14:30:45. |
TIMEVALUE | The TIMEVALUE function is used to convert a time expressed as text into a serial number that Excel recognizes as time. | Syntax: TIMEVALUE(time_text)Formula: TIMEVALUE(“10:30 AM”) will return 0.4375. |
TINV | TINV is an Excel function used to calculate the inverse of the t-distribution. It is used to calculate the critical value of the t-distribution, given a certain significance level. | Syntax: TINV(probability, degrees_of_freedom)Formula: TINV(0.05, 10)This example returns the critical value of the t-distribution with 10 degrees of freedom and a significance level of 0.05. The result is 1.81246. |
TOCOL | The TOCOL function returns the column number of a given cell reference. | Syntax: TOCOL(cell_reference)Formula: TOCOL(“A1”) returns 1 |
TODAY | The TODAY function returns the current date. | Syntax: TODAY()Formula: TODAY() This will return the current date in the cell. |
TOROW | The TOROW function returns the row number of a reference. | Syntax: TOROW(reference)Formula: TOROW(A1)This example returns the row number of cell A1, which is 1. |
TRANSPOSE | The TRANSPOSE function in Excel is used to transpose a range of cells from vertical to horizontal or vice versa. | Syntax: TRANSPOSE(array)Formula: TRANSPOSE(A1:C3)This example would take the range of cells from A1 to C3 and transpose them so that the rows become columns and the columns become rows. |
TREND | The TREND function is used to calculate linear trend line to a given set of data points and extend it to new data points. | Syntax: TREND(known_y’s, [known_x’s], [new_x’s], [const])Formula: TREND(A2:A5, B2:B5, B6, FALSE)This example would calculate the linear trend line for the data points in cells A2 to A5, using the corresponding x values in cells B2 to B5. It would then extend the trend line to the x value in cell B6. The FALSE argument specifies that the trend line should not include a constant. |
TRIM | The TRIM function removes all spaces from a text string except for single spaces between words. | Syntax: TRIM(text)Formula: TRIM(” This is a test “)Result: “This is a test” |
TRIMMEAN | The TRIMMEAN function is an Excel function used to calculate the mean of a dataset after a certain percentage of the data points have been excluded from the calculation. | Syntax: TRIMMEAN(array, percent)Formula: TRIMMEAN(A2:A8, 0.2)This example would calculate the mean of the dataset in the range A2:A8, excluding the top and bottom 20% of the data points. |
True | The TRUE function returns the logical value TRUE. | Syntax: TRUE()Formula: TRUE()This formula will return the logical value TRUE. |
TRUNC | The TRUNC function truncates a number to an integer by removing the decimal values. | Syntax: TRUNC(number, [num_digits])Formula:=TRUNC(3.14159,2) Result: 3.14 |
TTEST | TTEST Function:
The TTEST function calculates the probability associated with the Student’s t-test. The Student’s t-test is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. |
Syntax: TTEST(array1, array2, tails, type)Array1: The first array of data.Array2: The second array of data. Tails: The number of tails in the t-test. The default value is 2. Type: The type of t-test. The default value is 1. Formula: This example will calculate the probability associated with the Student’s t-test for the two arrays of data in A2:A7 and B2:B7, using two tails and type 1. |
TYPE | TYPE: The TYPE function is an Excel Information function that returns a number indicating the type of value in a cell. | Syntax: TYPE(value)Formula: TYPE(A1)If cell A1 contains the value “Hello”, the result of the function would be 1, indicating that the value is a text string. |
UNICHAR | UNICHAR is an Excel function that returns the Unicode character that corresponds to the given numeric code. | Syntax: UNICHAR(number)Formula:=UNICHAR(65) This will return the character “A” in the cell. |
UNICODE | The UNICODE function is a built-in function in Microsoft Excel that returns the numerical value of a character or the first character in a string. | Syntax: UNICODE(text)Formula: UNICODE(“A”)Result: 65 |
UNIQUE | The UNIQUE function in Excel is used to return a list of unique values from a list or range. | Syntax: UNIQUE(array, [by_col], [exactly_once])Array: The array or range of cells from which to extract unique values.By_col: (optional) A logical value that specifies whether to return unique rows or columns. If set to TRUE, the function will return unique rows; if set to FALSE, the function will return unique columns. The default value is FALSE. Exactly_once: (optional) A logical value that specifies whether to return only values that appear exactly once in the list or range. The default value is FALSE. Formula: |
UPPER | The UPPER function in Excel converts all text in a cell to uppercase. | Syntax: UPPER(text)Formula: UPPER(“Hello World”)Result: HELLO WORLD |
VALUE | The VALUE function converts a text string that represents a number to a number. | Syntax: VALUE(text)Formula: VALUE(“2”) returns 2 |
VALUETOTEXT | VALUETOTEXT is an Excel function that converts a numerical value into a text string. | Syntax: VALUETOTEXT(value, [format_text])Formula: VALUETOTEXT(A1,”$#,##0.00″) This example will convert the numerical value in cell A1 into a text string in the format “$#,##0.00”. |
VAR | The VAR function in Excel is used to calculate the variance of a set of values. | Syntax: VAR(number1, [number2], …)Formula: VAR(2,4,6,8)This will return 6, which is the variance of the set of values. |
VAR.P | VAR.P is a statistical function in Excel that calculates the population variance of a given set of values. | Syntax: VAR.P(value1, [value2], …)Formula: VAR.P(1,2,3,4,5)This example would return 2.5, which is the population variance of the set of values (1,2,3,4,5). |
VAR.S | VAR.S is an Excel function that calculates the sample variance of a population based on a sample of numbers. | Syntax: VAR.S(number1, [number2], …)Formula: VAR.S(2, 4, 6, 8, 10)This example would calculate the sample variance of the population of numbers (2, 4, 6, 8, 10) and return 8. |
VARA | The VARA function in Excel returns the variance of an array or range of numbers. | Syntax: VARA(number1, [number2], …)Formula: VARA(5, 10, 15, 20)This example will return the variance of the numbers 5, 10, 15, and 20, which is 25. |
VARP | VARP is an Excel function that calculates the variance of a population based on a set of values. | Syntax: VARP(value1, [value2], …)Formula: VARP(1,2,3,4,5)Result: 2 |
VARPA | VARPA is an Excel function that calculates the variance of an entire population based on a set of values. | Syntax: VARPA(value1, [value2], …)Formula: VARPA(2,4,6,8,10)Result: 8 |
VDB | VDB stands for Variable Declining Balance. It is a financial function that is used to calculate the depreciation of an asset over a period of time. | Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])Formula: VDB(10000, 1000, 5, 1, 3, 2, FALSE)This example will calculate the depreciation of an asset with a cost of 10,000, a salvage value of 1,000 and a life of 5 years for the first 3 periods, using a double declining balance factor and no switch. |
VLOOKUP | VLOOKUP is an Excel function used to look up and retrieve data from a specific column in a table. It searches for a value in the leftmost column of a table and returns a value in the same row from another column. | Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Formula: VLOOKUP(A2, C2:E5, 3, FALSE)This example looks up the value in cell A2 in the leftmost column of the table in cells C2 to E5 and returns the value in the same row from the third column of the table. |
VSTACK | The VSTACK function in Excel is used to stack two or more vertical ranges of data on top of each other. | Syntax: VSTACK(array1, array2, [array3], …)Formula: VSTACK(A1:A5, B1:B5) |
WEBSERVICE | The WEBSERVICE function is used to retrieve data from a web service in an Excel worksheet. | Syntax: WEBSERVICE(url)Formula: WEBSERVICE(“https://www.example.com/data.xml”) |
WEEKDAY | The WEEKDAY function in Excel returns a number from 1-7 corresponding to the day of the week for a given date. | Syntax: WEEKDAY(serial_number, [return_type])Formula: WEEKDAY(A2, 1) Where A2 is a cell containing a date, the WEEKDAY function will return a number from 1-7 corresponding to the day of the week. For example, if A2 contains the date “2/14/2021”, the WEEKDAY function will return 4, corresponding to Wednesday. |
WEEKNUM | The WEEKNUM function returns the week number of a given date in the year. | Syntax: WEEKNUM(serial_number, [return_type])Formula: WEEKNUM(A1,1)This example will return the week number of the date in cell A1 using the return type of 1 (week starts on Sunday). |
WEIBULL | The WEIBULL function in Excel calculates the Weibull probability density function or the Weibull cumulative distribution function for a supplied set of parameters. | Syntax: WEIBULL(x,alpha,beta,cumulative)Formula: WEIBULL(2,3,4,TRUE)This example will calculate the Weibull cumulative distribution function with x = 2, alpha = 3, and beta = 4. |
WEIBULL.DIST | WEIBULL.DIST is an Excel function used to calculate the probability that a value is less than or equal to a given value in a Weibull distribution. | Syntax: WEIBULL.DIST(x,alpha,beta,cumulative)x: The value for which you want to calculate the distribution.alpha: The shape parameter of the distribution. beta: The scale parameter of the distribution. cumulative: A logical value that determines the form of the function. If cumulative is TRUE, WEIBULL.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function. Formula: This formula will return the cumulative probability that a value is less than or equal to 3 in a Weibull distribution with a shape parameter of 2 and a scale parameter of 4. |
WORKDAY | The Excel WORKDAY function returns a date that is a given number of workdays away from a start date. | Syntax: WORKDAY(start_date, days, [holidays])Formula: WORKDAY(DATE(2020,1,1), 10)This example returns the date 10 workdays after January 1, 2020. |
WORKDAY.INTL | The WORKDAY.INTL function calculates the date after a specified number of workdays, taking into account weekends and holidays. | Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays])Formula: WORKDAY.INTL(DATE(2020, 1, 1), 10, 11, A2:A10)This example returns the date 10 workdays after January 1, 2020, taking into account weekends 11 (Saturday and Sunday) and holidays specified in cells A2:A10. |
WRAPCOLS | The WRAPCOLS function in Microsoft Excel is used to wrap the contents of a cell across multiple columns. | Syntax: WRAPCOLS(cell_reference, number_of_columns)Formula: WRAPCOLS(A1, 3) This will wrap the contents of cell A1 across 3 columns. |
WRAPROWS | WRAPROWS is a Microsoft Excel function that allows you to wrap text in a cell to multiple lines. This is useful when you have a long text string that needs to be displayed in a cell. | Syntax: WRAPROWS(cell_reference)Formula: WRAPROWS(A1) |
XIRR | XIRR is an Excel function that calculates the internal rate of return for a series of cash flows that occur at irregular intervals. It is used to calculate the rate of return for investments that don’t have a fixed schedule of payments, such as investments in stocks or mutual funds. | Syntax: XIRR (values, dates, [guess])Formula: XIRR (A1:A4, B1:B4, 0.1)A1:A4 = -1000, 500, 500, 500 B1:B4 = 01/01/2020, 02/01/2020, 03/01/2020, 04/01/2020 In this example, XIRR will return the internal rate of return for the series of cash flows in A1:A4 that occur at the dates in B1:B4, with an initial guess of 0.1. |
XLOOKUP | XLOOKUP is an Excel function that looks for a value in the first column of a table array and returns the corresponding value from another column in the same row. | Syntax: XLOOKUP(lookup_value, lookup_array, return_column_number, [not_found], [match_mode], [search_mode])Formula: XLOOKUP(A2, B2:E5, 3, “Not Found”, 0, 1)In this example, the lookup_value is A2, the lookup_array is B2:E5, the return_column_number is 3, the not_found value is “Not Found”, the match_mode is 0 and the search_mode is 1. |
XMATCH | XMATCH: This function returns the position of a value in a given array that matches a specified value in a specified order. | Syntax: XMATCH(lookup_value, lookup_array, [match_type])Formula: XMATCH(“Apple”, {“Orange”, “Banana”, “Apple”, “Grape”}, 0)This example will return 3, since “Apple” is the third item in the array. |
XNPV | The XNPV function calculates the net present value of an investment based on a discount rate and a series of cash flows. | Syntax: XNPV(rate, values, dates)Formula: XNPV(0.1, {-1000, 400, 400, 400}, {DATE(2020,1,1), DATE(2020,2,1), DATE(2020,3,1), DATE(2020,4,1)})This example would return a net present value of $912.11. |
XOR | The XOR function in Excel returns a logical exclusive OR of all arguments. It returns TRUE if an odd number of arguments are TRUE, and FALSE if an even number of arguments are TRUE. | Syntax: XOR(logical1, [logical2], …)Formula: XOR(TRUE, FALSE, TRUE)This example would return TRUE. |
YEAR | The YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value. | Syntax: YEAR(serial_number)Formula: YEAR(A1) where A1 is a cell containing a date value. The result would be the four-digit year of the date in A1. |
YEARFRAC | The YEARFRAC function is used to calculate the fraction of the year represented by the number of days between two dates. | Syntax: YEARFRAC(start_date, end_date, [basis])Formula: YEARFRAC(B2,C2,1)This example will calculate the fraction of the year between the dates in cells B2 and C2, using the US (NASD) 30/360 day count basis. |
YIELD | YIELD: The YIELD function returns the yield on a security that pays periodic interest. | Syntax: YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])Formula: YIELD(DATE(2020,5,15),DATE(2021,5,15),5%,100,100,2,1)This example would return the yield of a security that has a settlement date of May 15th, 2020, a maturity date of May 15th, 2021, a rate of 5%, a price of 100, a redemption value of 100, a frequency of 2 (semi-annual), and a basis of 1 (US (NASD) 30/360). |
YIELDDISC | YIELDDISC is an Excel function used to calculate the yield on a discounted security. It takes the following arguments:
• Settlement: The security’s settlement date. |
Syntax: YIELDDISC(settlement, maturity, price, redemption, basis)Formula: YIELDDISC(“1/1/2020”, “1/1/2021”, 98, 100, 0)This example would calculate the yield on a security with a settlement date of 1/1/2020, a maturity date of 1/1/2021, a price of 98 per $100 face value, a redemption value of 100 per $100 face value, and a day count basis of 0. |
YIELDMAT | YIELDMAT is an Excel function used to calculate the yield of a security that pays periodic interest. It is used to calculate the yield of a bond or other security on a given date. | Syntax: YIELDMAT(settlement, maturity, issue, rate, pr, redemption, frequency, [basis])Formula: YIELDMAT(A2, B2, C2, D2, E2, F2, G2, H2)In this example, A2 is the settlement date, B2 is the maturity date, C2 is the issue date, D2 is the rate, E2 is the price, F2 is the redemption value, G2 is the frequency, and H2 is the basis. |
Z.TEST | The Z.TEST function in Excel is used to calculate the one-tailed or two-tailed probability-value of a z-test. | Syntax: Z.TEST(array, x, sigma)Formula: Z.TEST(A2:A10, 7, 2)This example would calculate the one-tailed or two-tailed probability-value of a z-test using the data in cells A2 through A10, with a mean of 7 and a standard deviation of 2. |
ZTEST | The ZTEST function in Excel is used to calculate the one-tailed or two-tailed probability-value of a z-test. A z-test is used to determine whether two population means are different when the variances are known and the sample size is large. | Syntax: ZTEST(array, x, sigma)Array: The array of data to test.X: The hypothesized population mean. Sigma: The population standard deviation. Formula: This example would test the data in the range A2:A10 against a hypothesized population mean of 5 and a population standard deviation of 2. |