A bibliography of financial, statistical, and engineering methods

The following books provide detailed information on financial, statistical, and engineering methods.

Abramowitz, Milton, and Irene A. Stegun. Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables. 10th ed. New York, NY: Dover Publications, 1974.

Antkies, Candace Mary. Standard Securities Calculation Methods: Fixed Income Securities Formulas for Price, Yield and Accrued Interest: Volume 1. New York, NY: Securities Industry Association.

Antkies, Candace Mary. Standard Securities Calculation Methods, Fixed Income Securities Formulas for Analytic Measures: Volume 2. New York, NY: Securities Industry Association.

Devore, Jay L. Probability and Statistics for Engineering and the Sciences. 5th ed. Pacific Grove, CA: Duxbury Press, 1998.

Fabozzi, Frank J. The Handbook of Fixed-Income Securities. 6th ed. New York, NY: Mcgraw-Hill Trade, 2000.

Hewlett-Packard, HP-12C Solutions Handbook. Palo Alto, CA: Hewlett-Packard, 2001.

McCall, Robert B. Fundamental Statistics for the Behavioral Sciences. 7th ed. Pacific Grove, CA: Brooks Cole, 1998.

Monks, Joseph G. Operations Management: Theory and Problems. 3rd ed. New York, NY: Macmillan/McGraw-Hill, 1988.

Press, W. H., B. P. Flannery, S. A. Teukolsky, and W. T. Vetterling. Numerical Recipes in C: The Art of Scientific Computing. 2nd ed. New York, NY: Cambridge University Press, 1993.

Sokal, Robert R., and F. James Rohlf. Biometry: The Principles and Practice of Statistics in Biological Research. 3rd ed. New York, NY: W. H. Freeman, 1994.

Stigum, Marcia, and John Mann. Money Market Calculations: Yields, Break-Evens, & Arbitrage. 2nd ed. Homewood, IL: Irwin Professional, 1991.

See Also

• List of worksheet functions (by category)

 

 

Excel > Function reference > Statistical

Statistical functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

 

 

Excel > Function reference > Statistical

AVEDEV function

Excel 2007

Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.

Syntax

AVEDEV(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want the average of the absolute deviations. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• AVEDEV is influenced by the unit of measurement in the input data.
• Arguments must either be numbers or be names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• The equation for average deviation is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• AVERAGEA function
• DEVSQ function
• Statistical functions
• STDEV function
• STDEVP function
• VAR function
• VARP function

 

 

Excel > Function reference > Statistical

AVERAGE function

Excel 2007

This article describes the formula syntax and usage of the AVERAGE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Syntax

AVERAGE(number1, [number2],...)

The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• number1  Required. The first number, cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), or range for which you want the average.
• number2, ...  Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

Remarks

• Arguments can either be numbers or names, ranges, or cell references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
• If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.

 Note    The AVERAGE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average, which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median, which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode, which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Tip  When you average cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Show a zero in cells that have a zero value check box in the Excel Options dialog box. When this option is selected, empty cells are not counted, but zero values are.

To locate the Show a zero in cells that have a zero value check box:

• Click the Microsoft Office Button , click Excel Options, and then, in the Advanced category, look under Display options for this worksheet.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• The sum and average function view all numbers as zero??
• Weighted Average for Different Aggragates of Duplicate Entries?
• Weighted avg of numbers not in contiguous row-ie array w lookup?
• Can you AVERAGE IF and not null?
• XL how to change a cell reference in a formula to variable value?
• Is there an AVERAGE IF function in Excel?
• Where is the "average" button on Mictosoft Excel?

See Also

• AVEDEV function
• AVERAGEA function
• AVERAGEIF function
• AVERAGEIFS function
• Calculate the average of a group of numbers
• GEOMEAN function
• HARMEAN function
• MEDIAN function
• MODE function
• TRIMMEAN function

 

 

Excel > Function reference > Statistical

AVERAGEA function

Excel 2007

Calculates the average (arithmetic mean) of the values in the list of arguments.

Syntax

AVERAGEA(value1,value2,...)

Value1, value2, ...   are 1 to 255 cells, ranges of cells, or values for which you want the average.

Remarks

• Arguments can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• Arguments that contain TRUE evaluate as 1; arguments that contain FALSE evaluate as 0 (zero).
• Array or reference arguments that contain text evaluate as 0 (zero). Empty text ("") evaluates as 0 (zero).
• If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGE function.

 Note    The AVERAGEA function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average   which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median   which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode   which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Tip  When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Show a zero in cells that have a zero value check box in the Working with Office Applications section of the Advanced category on the Excel Options dialog box under the File menu. Empty cells are not counted but zero values are.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVEDEV function
• AVERAGE function
• Statistical functions

 

 

Excel > Function reference > Statistical

AVERAGEIF function

Excel 2007

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

Syntax

AVERAGEIF(range,criteria,average_range)

Range  is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria  is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range  is the actual set of cells to average. If omitted, range is used.

Remarks

• Cells in range that contain TRUE or FALSE are ignored.
• If a cell in average_range is an empty cell, AVERAGEIF ignores it.
• If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
• If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
• If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:

 Note    The AVERAGEIF function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average   which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median   which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode   which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example: Averaging property values and commissions

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example: Averaging profits from regional offices

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• AVERAGEIFS function
• IF function
• Statistical functions

 

 

Excel > Function reference > Statistical

AVERAGEIFS function

Excel 2007

Returns the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range   is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, …   are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, …   are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Remarks

• If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
• If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
• Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero).
• Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell.
• Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range.
• If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.
• If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

 Note    The AVERAGEIFS function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average   which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median   which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode   which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example: Averaging student grades

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example: Averaging real estate prices

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• AVERAGEIF function
• IF function
• Statistical functions

 

 

Excel > Function reference > Statistical

BETADIST function

Excel 2007

Returns the cumulative beta probability density function. The beta distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.

Syntax

BETADIST(x,alpha,beta,A,B)

X   is the value between A and B at which to evaluate the function.

Alpha   is a parameter of the distribution.

Beta   is a parameter of the distribution.

A   is an optional lower bound to the interval of x.

B   is an optional upper bound to the interval of x.

Remarks

• If any argument is nonnumeric, BETADIST returns the #VALUE! error value.
• If alpha ≤ 0 or beta ≤ 0, BETADIST returns the #NUM! error value.
• If x < A, x > B, or A = B, BETADIST returns the #NUM! error value.
• If you omit values for A and B, BETADIST uses the standard cumulative beta distribution, so that A = 0 and B = 1.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• BETAINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

BETAINV function

Excel 2007

Returns the inverse of the cumulative beta probability density function for a specified beta distribution. That is, if probability = BETADIST(x,...), then BETAINV(probability,...) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability.

Syntax

BETAINV(probability,alpha,beta,A,B)

Probability   is a probability associated with the beta distribution.

Alpha   is a parameter of the distribution.

Beta   is a parameter the distribution.

A   is an optional lower bound to the interval of x.

B   is an optional upper bound to the interval of x.

Remarks

• If any argument is nonnumeric, BETAINV returns the #VALUE! error value.
• If alpha ≤ 0 or beta ≤ 0, BETAINV returns the #NUM! error value.
• If probability ≤ 0 or probability > 1, BETAINV returns the #NUM! error value.
• If you omit values for A and B, BETAINV uses the standard cumulative beta distribution, so that A = 0 and B = 1.

Given a value for probability, BETAINV seeks that value x such that BETADIST(x, alpha, beta, A, B) = probability. Thus, precision of BETAINV depends on precision of BETADIST. BETAINV uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• BETADIST function
• Statistical functions

 

 

Excel > Function reference > Statistical

BINOMDIST function

Excel 2007

Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOMDIST can calculate the probability that two of the next three babies born are male.

Syntax

BINOMDIST(number_s,trials,probability_s,cumulative)

Number_s   is the number of successes in trials.

Trials   is the number of independent trials.

Probability_s   is the probability of success on each trial.

Cumulative   is a logical value that determines the form of the function. If cumulative is TRUE, then BINOMDIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if FALSE, it returns the probability mass function, which is the probability that there are number_s successes.

Remarks

• Number_s and trials are truncated to integers.
• If number_s, trials, or probability_s is nonnumeric, BINOMDIST returns the #VALUE! error value.
• If number_s < 0 or number_s > trials, BINOMDIST returns the #NUM! error value.
• If probability_s < 0 or probability_s > 1, BINOMDIST returns the #NUM! error value.
• The binomial probability mass function is:

where:

is COMBIN(n,x).

The cumulative binomial distribution is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COMBIN function
• CRITBINOM function
• FACT function
• HYPGEOMDIST function
• NEGBINOMDIST function
• PERMUT function
• PROB function
• Statistical functions

 

 

Excel > Function reference > Statistical

CHIDIST function

Excel 2007

Returns the one-tailed probability of the chi-squared distribution. The ק2 distribution is associated with a ק2 test. Use the ק2 test to compare observed and expected values. For example, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid.

Syntax

CHIDIST(x,degrees_freedom)

X   is the value at which you want to evaluate the distribution.

Degrees_freedom   is the number of degrees of freedom.

Remarks

• If either argument is nonnumeric, CHIDIST returns the #VALUE! error value.
• If x is negative, CHIDIST returns the #NUM! error value.
• If degrees_freedom is not an integer, it is truncated.
• If degrees_freedom < 1 or degrees_freedom > 10^10, CHIDIST returns the #NUM! error value.
• CHIDIST is calculated as CHIDIST = P(X>x), where X is a ק2 random variable.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHIINV function
• CHITEST function
• Statistical functions

 

 

Excel > Function reference > Statistical

CHIINV function

Excel 2007

Returns the inverse of the one-tailed probability of the chi-squared distribution. If probability = CHIDIST(x,...), then CHIINV(probability,...) = x. Use this function to compare observed results with expected ones in order to decide whether your original hypothesis is valid.

Syntax

CHIINV(probability,degrees_freedom)

Probability   is a probability associated with the chi-squared distribution.

Degrees_freedom   is the number of degrees of freedom.

Remarks

• If either argument is nonnumeric, CHIINV returns the #VALUE! error value.
• If probability < 0 or probability > 1, CHIINV returns the #NUM! error value.
• If degrees_freedom is not an integer, it is truncated.
• If degrees_freedom < 1 or degrees_freedom > 10^10, CHIINV returns the #NUM! error value.

Given a value for probability, CHIINV seeks that value x such that CHIDIST(x, degrees_freedom) = probability. Thus, precision of CHIINV depends on precision of CHIDIST. CHIINV uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHIDIST function
• CHITEST function
• Statistical functions

 

 

Excel > Function reference > Statistical

CHITEST function

Excel 2007

Returns the test for independence. CHITEST returns the value from the chi-squared (ק2) distribution for the statistic and the appropriate degrees of freedom. You can use ק2 tests to determine whether hypothesized results are verified by an experiment.

Syntax

CHITEST(actual_range,expected_range)

Actual_range   is the range of data that contains observations to test against expected values.

Expected_range   is the range of data that contains the ratio of the product of row totals and column totals to the grand total.

Remarks

• If actual_range and expected_range have a different number of data points, CHITEST returns the #N/A error value.
• The ק2 test first calculates a ק2 statistic using the formula:

where:

Aij = actual frequency in the i-th row, j-th column

Eij = expected frequency in the i-th row, j-th column

r = number or rows

c = number of columns

• A low value of ק2 is an indicator of independence. As can be seen from the formula, ק2 is always positive or 0, and is 0 only if Aij = Eij for every i,j.
• CHITEST returns the probability that a value of the ק2 statistic at least as high as the value calculated by the above formula could have happened by chance under the assumption of independence. In computing this probability, CHITEST uses the ק2 distribution with an appropriate number of degrees of freedom, df. If r > 1 and c > 1, then df = (r - 1)(c - 1). If r = 1 and c > 1, then df = c - 1 or if r > 1 and c = 1, then df = r - 1. r = c= 1 is not allowed and #N/A is returned.
• Use of CHITEST is most appropriate when Eij’s are not too small. Some statisticians suggest that each Eij should be greater than or equal to 5.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHIDIST function
• CHIINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

CONFIDENCE function

Excel 2007

Returns a value that you can use to construct a confidence interval for a population mean. The confidence interval is a range of values. Your sample mean, x, is at the center of this range and the range is x ± CONFIDENCE. For example, if x is the sample mean of delivery times for products ordered through the mail, x ± CONFIDENCE is a range of population means. For any population mean, ל0, in this range, the probability of obtaining a sample mean further from ל0 than x is greater than alpha; for any population mean, ל0, not in this range, the probability of obtaining a sample mean further from ל0 than x is less than alpha. In other words, assume that we use x, standard_dev, and size to construct a two-tailed test at significance level alpha of the hypothesis that the population mean is ל0. Then we will not reject that hypothesis if ל0 is in the confidence interval and will reject that hypothesis if ל0 is not in the confidence interval. The confidence interval does not allow us to infer that there is probability 1 – alpha that our next package will take a delivery time that is in the confidence interval.

Syntax

CONFIDENCE(alpha,standard_dev,size)

Alpha   is the significance level used to compute the confidence level. The confidence level equals 100*(1 - alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level.

Standard_dev   is the population standard deviation for the data range and is assumed to be known.

Size   is the sample size.

Remarks

• If any argument is nonnumeric, CONFIDENCE returns the #VALUE! error value.
• If alpha ≤ 0 or alpha ≥ 1, CONFIDENCE returns the #NUM! error value.
• If standard_dev ≤ 0, CONFIDENCE returns the #NUM! error value.
• If size is not an integer, it is truncated.
• If size < 1, CONFIDENCE returns the #NUM! error value.
• If we assume alpha equals 0.05, we need to calculate the area under the standard normal curve that equals (1 - alpha), or 95 percent. This value is ± 1.96. The confidence interval is therefore:

Example

Suppose we observe that, in our sample of 50 commuters, the average length of travel to work is 30 minutes with a population standard deviation of 2.5. With alpha = .05, CONFIDENCE(.05, 2.5, 50) returns 0.692952. The corresponding confidence interval is then 30 ± 0.692952 = approximately [29.3, 30.7]. For any population mean, ל0, in this interval, the probability of obtaining a sample mean further from ל0 than 30 is more than 0.05. Likewise, for any population mean, ל0, outside this interval, the probability of obtaining a sample mean further from ל0 than 30 is less than 0.05.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Statistical functions
• ZTEST function

 

 

Excel > Function reference > Statistical

CORREL function

Excel 2007

Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.

Syntax

CORREL(array1,array2)

Array1   is a cell range of values.

Array2   is a second cell range of values.

Remarks

• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If array1 and array2 have a different number of data points, CORREL returns the #N/A error value.
• If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value.
• The equation for the correlation coefficient is:

where x and y are the sample means AVERAGE(array1) and AVERAGE(array2).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COVAR function
• FISHER function
• FISHERINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

COUNT function

Excel 2007

This article describes the formula syntax and usage of the COUNT function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20:

=COUNT(A1:A20)

In this example, if five of the cells in the range contain numbers, the result is 5.

Syntax

COUNT(value1, [value2],...)

The COUNT function syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• value1  Required. The first item, cell reference, or range within which you want to count numbers.
• value2, ...  Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.

 Note    The arguments can contain or refer to a variety of different types of data, but only numbers are counted.

Remarks

• Arguments that are numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as "1") are counted.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• Arguments that are error values or text that cannot be translated into numbers are not counted.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.
• If you want to count logical values, text, or error values, use the COUNTA function.
• If you want to count only numbers that meet certain criteria, use the COUNTIF function or the COUNTIFS function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• Count cells that contain numbers
• Count how often a value occurs
• COUNTA function
• DCOUNT function
• DCOUNTA function
• Statistical functions
• SUM function

 

 

Excel > Function reference > Statistical

COUNTA function

Excel 2007

This article describes the formula syntax and usage of the COUNTA function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).

Syntax

COUNTA(value1, [value2], ...)

The COUNTA function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• value1  Required. The first argument representing the values that you want to count.
• value2, ...  Optional. Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

Remarks

• The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.
• If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.
• If you want to count only cells that meet certain criteria, use the COUNTIF function or the COUNTIFS function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• COUNT function
• Count nonblank cells
• DCOUNT function
• DCOUNTA function
• PRODUCT function
• Statistical functions
• SUM function

 

 

Excel > Function reference > Statistical

COUNTBLANK function

Excel 2007

Counts empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Range   is the range from which you want to count the blank cells.

Remark

Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUNTIF function
• Information functions
• Statistical functions

 

 

Excel > Function reference > Statistical

COUNTIF function

Excel 2007

This article describes the formula syntax and usage of the COUNTIF function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person. For example:

=COUNTIF(B2:B25,"Nancy")

 Note    To count cells based on multiple criteria, see COUNTIFS function.

Syntax

COUNTIF(range, criteria)

The COUNTIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• range  Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• criteria  Required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

 Notes 

• You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Criteria are case insensitive; for example, the string "apples" and the string "APPLES" will match the same cells.

Example 1: Common COUNTIF formulas

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2: COUNTIF formulas using wildcard characters and handling blank values

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note     To view a number as a percentage, select the cell and then, on the Sheet tab, in the Number group, click Percentage Style .

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I get the filter mode to show the good row count?
• How can I count items in a filtered list?
• Can I use a Reference inside a SUMIF or COUNTIF Function?
• Can I Use a Count Function for Text?
• How do I count cells beginning with specific letter?
• How to not count cells which contain a formula that returns " "?

See Also

• COUNTA function
• COUNTBLANK function
• COUNTIFS function
• Math and trigonometry functions
• Statistical functions

 

 

Excel > Function reference > Statistical

COUNTIFS function

Excel 2007

This article describes the formula syntax and usage of the COUNTIFSfunction (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• criteria_range1  Required. The first range in which to evaluate the associated criteria.
• criteria1  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".
• criteria_range2, criteria2, ...  Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Important  Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

Remarks

• Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
• If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
• You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUNTIF function
• Math and trigonometry functions
• Statistical functions

 

 

Excel > Function reference > Statistical

COVAR function

Excel 2007

Returns covariance, the average of the products of deviations for each data point pair. Use covariance to determine the relationship between two data sets. For example, you can examine whether greater income accompanies greater levels of education.

Syntax

COVAR(array1,array2)

Array1   is the first cell range of integers.

Array2   is the second cell range of integers.

Remarks

• The arguments must either be numbers or be names, arrays, or references that contain numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If array1 and array2 have different numbers of data points, COVAR returns the #N/A error value.
• If either array1 or array2 is empty, COVAR returns the #DIV/0! error value.
• The covariance is:

where x and y are the sample means AVERAGE(array1) and AVERAGE(array2), and n is the sample size.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CORREL function
• FISHER function
• FISHERINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

CRITBINOM function

Excel 2007

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Use this function for quality assurance applications. For example, use CRITBINOM to determine the greatest number of defective parts that are allowed to come off an assembly line run without rejecting the entire lot.

Syntax

CRITBINOM(trials,probability_s,alpha)

Trials   is the number of Bernoulli trials.

Probability_s   is the probability of a success on each trial.

Alpha   is the criterion value.

Remarks

• If any argument is nonnumeric, CRITBINOM returns the #VALUE! error value.
• If trials is not an integer, it is truncated.
• If trials < 0, CRITBINOM returns the #NUM! error value.
• If probability_s is < 0 or probability_s > 1, CRITBINOM returns the #NUM! error value.
• If alpha < 0 or alpha > 1, CRITBINOM returns the #NUM! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• BINOMDIST function
• COMBIN function
• FACT function
• HYPGEOMDIST function
• NEGBINOMDIST function
• PERMUT function
• PROB function
• Statistical functions

 

 

Excel > Function reference > Statistical

DEVSQ function

Excel 2007

Returns the sum of squares of deviations of data points from their sample mean.

Syntax

DEVSQ(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want to calculate the sum of squared deviations. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• The equation for the sum of squared deviations is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVEDEV function
• Statistical functions
• STDEV function
• STDEVP function
• VAR function
• VARP function

 

 

Excel > Function reference > Statistical

EXPONDIST function

Excel 2007

Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.

Syntax

EXPONDIST(x,lambda,cumulative)

X   is the value of the function.

Lambda   is the parameter value.

Cumulative   is a logical value that indicates which form of the exponential function to provide. If cumulative is TRUE, EXPONDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Remarks

• If x or lambda is nonnumeric, EXPONDIST returns the #VALUE! error value.
• If x < 0, EXPONDIST returns the #NUM! error value.
• If lambda ≤ 0, EXPONDIST returns the #NUM! error value.
• The equation for the probability density function is:

• The equation for the cumulative distribution function is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• GAMMADIST function
• POISSON function
• Statistical functions

 

 

Excel > Function reference > Statistical

FDIST function

Excel 2007

Returns the F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine the test scores of men and women entering high school and determine if the variability in the females is different from that found in the males.

Syntax

FDIST(x,degrees_freedom1,degrees_freedom2)

X   is the value at which to evaluate the function.

Degrees_freedom1   is the numerator degrees of freedom.

Degrees_freedom2   is the denominator degrees of freedom.

Remarks

• If any argument is nonnumeric, FDIST returns the #VALUE! error value.
• If x is negative, FDIST returns the #NUM! error value.
• If degrees_freedom1 or degrees_freedom2 is not an integer, it is truncated.
• If degrees_freedom1 < 1 or degrees_freedom1 ≥ 10^10, FDIST returns the #NUM! error value.
• If degrees_freedom2 < 1 or degrees_freedom2 ≥ 10^10, FDIST returns the #NUM! error value.
• FDIST is calculated as FDIST=P( F>x ), where F is a random variable that has an F distribution with degrees_freedom1 and degrees_freedom2 degrees of freedom.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FINV function
• FTEST function
• Statistical functions

 

 

Excel > Function reference > Statistical

FINV function

Excel 2007

Returns the inverse of the F probability distribution. If p = FDIST(x,...), then FINV(p,...) = x.

The F distribution can be used in an F-test that compares the degree of variability in two data sets. For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of income diversity.

Syntax

FINV(probability,degrees_freedom1,degrees_freedom2)

Probability   is a probability associated with the F cumulative distribution.

Degrees_freedom1   is the numerator degrees of freedom.

Degrees_freedom2   is the denominator degrees of freedom.

Remarks

• If any argument is nonnumeric, FINV returns the #VALUE! error value.
• If probability < 0 or probability > 1, FINV returns the #NUM! error value.
• If degrees_freedom1 or degrees_freedom2 is not an integer, it is truncated.
• If degrees_freedom1 < 1 or degrees_freedom1 ≥ 10^10, FINV returns the #NUM! error value.
• If degrees_freedom2 < 1 or degrees_freedom2 ≥ 10^10, FINV returns the #NUM! error value.

FINV can be used to return critical values from the F distribution. For example, the output of an ANOVA calculation often includes data for the F statistic, F probability, and F critical value at the 0.05 significance level. To return the critical value of F, use the significance level as the probability argument to FINV.

Given a value for probability, FINV seeks that value x such that FDIST(x, degrees_freedom1, degrees_freedom2) = probability. Thus, precision of FINV depends on precision of FDIST. FINV uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FDIST function
• FTEST function
• Statistical functions

 

 

Excel > Function reference > Statistical

FISHER function

Excel 2007

Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient.

Syntax

FISHER(x)

X   is a numeric value for which you want the transformation.

Remarks

• If x is nonnumeric, FISHER returns the #VALUE! error value.
• If x ≤ -1 or if x ≥ 1, FISHER returns the #NUM! error value.
• The equation for the Fisher transformation is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CORREL function
• COVAR function
• FISHERINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

FISHERINV function

Excel 2007

Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x.

Syntax

FISHERINV(y)

Y   is the value for which you want to perform the inverse of the transformation.

Remarks

• If y is nonnumeric, FISHERINV returns the #VALUE! error value.
• The equation for the inverse of the Fisher transformation is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CORREL function
• COVAR function
• FISHER function
• Statistical functions

 

 

Excel > Function reference > Statistical

FORECAST function

Excel 2007

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

Syntax

FORECAST(x,known_y's,known_x's)

X   is the data point for which you want to predict a value.

Known_y's   is the dependent array or range of data.

Known_x's   is the independent array or range of data.

Remarks

• If x is nonnumeric, FORECAST returns the #VALUE! error value.
• If known_y's and known_x's are empty or contain a different number of data points, FORECAST returns the #N/A error value.
• If the variance of known_x's equals zero, then FORECAST returns the #DIV/0! error value.
• The equation for FORECAST is a+bx, where:

and:

and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known y's).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• GROWTH function
• LINEST function
• LOGEST function
• Statistical functions
• TREND function

 

 

Excel > Function reference > Statistical

FREQUENCY function

Excel 2007

Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.

Syntax

FREQUENCY(data_array,bins_array)

Data_array   is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array   is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

Remarks

• FREQUENCY is entered as an array formula after you select a range of adjacent cells into which you want the returned distribution to appear.
• The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the count of any values above the highest interval. For example, when counting three ranges of values (intervals) that are entered into three cells, be sure to enter FREQUENCY into four cells for the results. The extra cell returns the number of values in data_array that are greater than the third interval value.
• FREQUENCY ignores blank cells and text.
• Formulas that return arrays must be entered as array formulas.

Example

This example assumes all test scores are integers.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A12:A15, press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, there will be only one result in cell A12 (1).

See Also

• Add numbers
• COUNT function
• Count unique values among duplicates
• DCOUNT function
• Statistical functions

 

 

Excel > Function reference > Statistical

FTEST function

Excel 2007

Returns the result of an F-test. An F-test returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Use this function to determine whether two samples have different variances. For example, given test scores from public and private schools, you can test whether these schools have different levels of test score diversity.

Syntax

FTEST(array1,array2)

Array1   is the first array or range of data.

Array2   is the second array or range of data.

Remarks

• The arguments must be either numbers or names, arrays, or references that contain numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If the number of data points in array1 or array2 is less than 2, or if the variance of array1 or array2 is zero, FTEST returns the #DIV/0! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FDIST function
• FINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

GAMMADIST function

Excel 2007

Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.

Syntax

GAMMADIST(x,alpha,beta,cumulative)

X   is the value at which you want to evaluate the distribution.

Alpha   is a parameter to the distribution.

Beta   is a parameter to the distribution. If beta = 1, GAMMADIST returns the standard gamma distribution.

Cumulative   is a logical value that determines the form of the function. If cumulative is TRUE, GAMMADIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Remarks

• If x, alpha, or beta is nonnumeric, GAMMADIST returns the #VALUE! error value.
• If x < 0, GAMMADIST returns the #NUM! error value.
• If alpha ≤ 0 or if beta ≤ 0, GAMMADIST returns the #NUM! error value.
• The equation for the gamma probability density function is:

The standard gamma probability density function is:

• When alpha = 1, GAMMADIST returns the exponential distribution with:

• For a positive integer n, when alpha = n/2, beta = 2, and cumulative = TRUE, GAMMADIST returns (1 - CHIDIST(x)) with n degrees of freedom.
• When alpha is a positive integer, GAMMADIST is also known as the Erlang distribution.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CHIDIST function
• EXPONDIST function
• GAMMAINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

GAMMAINV function

Excel 2007

Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x.

You can use this function to study a variable whose distribution may be skewed.

Syntax

GAMMAINV(probability,alpha,beta)

Probability   is the probability associated with the gamma distribution.

Alpha   is a parameter to the distribution.

Beta   is a parameter to the distribution. If beta = 1, GAMMAINV returns the standard gamma distribution.

Remarks

• If any argument is text, GAMMAINV returns the #VALUE! error value.
• If probability < 0 or probability > 1, GAMMAINV returns the #NUM! error value.
• If alpha ≤ 0 or if beta ≤ 0, GAMMAINV returns the #NUM! error value.

Given a value for probability, GAMMAINV seeks that value x such that GAMMADIST(x, alpha, beta, TRUE) = probability. Thus, precision of GAMMAINV depends on precision of GAMMADIST. GAMMAINV uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• GAMMADIST function
• Statistical functions

 

 

Excel > Function reference > Statistical

GAMMALN function

Excel 2007

Returns the natural logarithm of the gamma function, ֳ(x).

Syntax

GAMMALN(x)

X   is the value for which you want to calculate GAMMALN.

Remarks

• If x is nonnumeric, GAMMALN returns the #VALUE! error value.
• If x ≤ 0, GAMMALN returns the #NUM! error value.
• The number e raised to the GAMMALN(i) power, where i is an integer, returns the same result as (i - 1)!.
• GAMMALN is calculated as follows:

where:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FACT function
• Statistical functions

 

 

Excel > Function reference > Statistical

GEOMEAN function

Excel 2007

Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.

Syntax

GEOMEAN(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want to calculate the mean. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If any data point ≤ 0, GEOMEAN returns the #NUM! error value.
• The equation for the geometric mean is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• HARMEAN function
• MEDIAN function
• MODE function
• Statistical functions
• TRIMMEAN function

 

 

Excel > Function reference > Statistical

GROWTH function

Excel 2007

Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values.

Syntax

GROWTH(known_y's,known_x's,new_x's,const)

Known_y's   is the set of y-values you already know in the relationship y = b*m^x.

• If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable.
• If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.
• If any of the numbers in known_y's is 0 or negative, GROWTH returns the #NUM! error value.

Known_x's   is an optional set of x-values that you may already know in the relationship y = b*m^x.

• The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector (that is, a range with a height of one row or a width of one column).
• If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.

New_x's   are new x-values for which you want GROWTH to return corresponding y-values.

• New_x's must include a column (or row) for each independent variable, just as known_x's does. So, if known_y's is in a single column, known_x's and new_x's must have the same number of columns. If known_y's is in a single row, known_x's and new_x's must have the same number of rows.
• If new_x's is omitted, it is assumed to be the same as known_x's.
• If both known_x's and new_x's are omitted, they are assumed to be the array {1,2,3,...} that is the same size as known_y's.

Const   is a logical value specifying whether to force the constant b to equal 1.

• If const is TRUE or omitted, b is calculated normally.
• If const is FALSE, b is set equal to 1 and the m-values are adjusted so that y = m^x.

Remarks

• Formulas that return arrays must be entered as array formulas after selecting the correct number of cells.
• When entering an array constant for an argument such as known_x's, use commas to separate values in the same row and semicolons to separate rows.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

This example uses the same data as the LOGEST example. The first formula shows corresponding values to the known values. The second formula predicts the next months values, if the exponential trend continues.

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range C2:C7 or B9:B10 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single results are 32618.20377 and 320196.7184.

See Also

• LINEST function
• LOGEST function
• Statistical functions
• TREND function

 

 

Excel > Function reference > Statistical

HARMEAN function

Excel 2007

Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

Syntax

HARMEAN(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want to calculate the mean. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• The harmonic mean is always less than the geometric mean, which is always less than the arithmetic mean.
• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If any data point ≤ 0, HARMEAN returns the #NUM! error value.
• The equation for the harmonic mean is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• GEOMEAN function
• MEDIAN function
• MODE function
• Statistical functions
• TRIMMEAN function

 

 

Excel > Function reference > Statistical

HYPGEOMDIST function

Excel 2007

Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOMDIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.

Syntax

HYPGEOMDIST(sample_s,number_sample,population_s,number_population)

Sample_s   is the number of successes in the sample.

Number_sample   is the size of the sample.

Population_s   is the number of successes in the population.

Number_population   is the population size.

Remarks

• All arguments are truncated to integers.
• If any argument is nonnumeric, HYPGEOMDIST returns the #VALUE! error value.
• If sample_s < 0 or sample_s is greater than the lesser of number_sample or population_s, HYPGEOMDIST returns the #NUM! error value.
• If sample_s is less than the larger of 0 or (number_sample - number_population + population_s), HYPGEOMDIST returns the #NUM! error value.
• If number_sample ≤ 0 or number_sample > number_population, HYPGEOMDIST returns the #NUM! error value.
• If population_s ≤ 0 or population_s > number_population, HYPGEOMDIST returns the #NUM! error value.
• If number_population ≤ 0, HYPGEOMDIST returns the #NUM! error value.
• The equation for the hypergeometric distribution is:

where:

x = sample_s

n = number_sample

M = population_s

N = number_population

HYPGEOMDIST is used in sampling without replacement from a finite population.

Example

A sampler of chocolates contains 20 pieces. Eight pieces are caramels, and the remaining 12 are nuts. If a person selects 4 pieces at random, the following function returns the probability that exactly 1 piece is a caramel.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• BINOMDIST function
• COMBIN function
• FACT function
• NEGBINOMDIST function
• PERMUT function
• Statistical functions

 

 

Excel > Function reference > Statistical

INTERCEPT function

Excel 2007

Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero). For example, you can use the INTERCEPT function to predict a metal's electrical resistance at 0°C when your data points were taken at room temperature and higher.

Syntax

INTERCEPT(known_y's,known_x's)

Known_y's   is the dependent set of observations or data.

Known_x's   is the independent set of observations or data.

Remarks

• The arguments should be either numbers or names, arrays, or references that contain numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• If known_y's and known_x's contain a different number of data points or contain no data points, INTERCEPT returns the #N/A error value.
• The equation for the intercept of the regression line, a, is:

where the slope, b, is calculated as:

and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known_y's).

• The underlying algorithm used in the INTERCEPT and SLOPE functions is different than the underlying algorithm used in the LINEST function. The difference between these algorithms can lead to different results when data is undetermined and collinear. For example, if the data points of the known_y's argument are 0 and the data points of the known_x's argument are 1:
  • INTERCEPT and SLOPE return a #DIV/0! error. The INTERCEPT and SLOPE algorithm is designed to look for one and only one answer, and in this case there can be more than one answer.
  • LINEST returns a value of 0. The LINEST algorithm is designed to return reasonable results for collinear data, and in this case at least one answer can be found.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• FORECAST function
• GROWTH function
• LINEST function
• LOGEST function
• PEARSON function
• RSQ function
• SLOPE function
• Statistical functions
• STEYX function

 

 

Excel > Function reference > Statistical

KURT function

Excel 2007

Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Syntax

KURT(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want to calculate kurtosis. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If there are fewer than four data points, or if the standard deviation of the sample equals zero, KURT returns the #DIV/0! error value.
• Kurtosis is defined as:

where s is the sample standard deviation.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• SKEW function
• Statistical functions
• STDEV function
• STDEVP function
• VAR function
• VARP function

 

 

Excel > Function reference > Statistical

LARGE function

Excel 2007

Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.

Syntax

LARGE(array,k)

Array   is the array or range of data for which you want to determine the k-th largest value.

K   is the position (from the largest) in the array or cell range of data to return.

Remarks

• If array is empty, LARGE returns the #NUM! error value.
• If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.

If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Calculate the smallest or largest number in a range
• PERCENTILE function
• PERCENTRANK function
• QUARTILE function
• SMALL function
• Statistical functions

 

 

Excel > Function reference > Statistical

LINEST function

Excel 2007

This article describes the formula syntax and usage of the LINEST function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Find links to more information about charting and performing a regression analysis in the See Also section.

Description

The LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line. You can also combine LINEST with other functions to calculate the statistics for other types of models that are linear in the unknown parameters, including polynomial, logarithmic, exponential, and power series. Because this function returns an array of values, it must be entered as an array formula. Instructions follow the examples in this article.

The equation for the line is:

y = mx + b

–or–

y = m1x1 + m2x2 + ... + b (if there are multiple ranges of x-values)

where the dependent y-values are a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that the LINEST function returns is {mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics.

Syntax

LINEST(known_y's, [known_x's], [const], [stats])

The LINEST function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• known_y's  Required. The set of y-values that you already know in the relationship y = mx + b.
  • If the range of known_y's is in a single column, each column of known_x's is interpreted as a separate variable.
  • If the range of known_y's is contained in a single row, each row of known_x's is interpreted as a separate variable.
• known_x's  Optional. A set of x-values that you may already know in the relationship y = mx + b.
  • The range of known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a vector (that is, a range with a height of one row or a width of one column).
  • If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.
• const  Optional. A logical value specifying whether to force the constant b to equal 0.
  • If const is TRUE or omitted, b is calculated normally.
  • If const is FALSE, b is set equal to 0 and the m-values are adjusted to fit y = mx.
• stats  Optional. A logical value specifying whether to return additional regression statistics.
  • If stats is TRUE, LINEST returns the additional regression statistics; as a result, the returned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
  • If stats is FALSE or omitted, LINEST returns only the m-coefficients and the constant b.

The additional regression statistics are as follows.

The following illustration shows the order in which the additional regression statistics are returned.

Remarks

• You can describe any straight line with the slope and the y-intercept:

Slope (m):
To find the slope of a line, often written as m, take two points on the line, (x1,y1) and (x2,y2); the slope is equal to (y2 - y1)/(x2 - x1).

Y-intercept (b):
The y-intercept of a line, often written as b, is the value of y at the point where the line crosses the y-axis.

The equation of a straight line is y = mx + b. Once you know the values of m and b, you can calculate any point on the line by plugging the y- or x-value into that equation. You can also use the TREND function.

• When you have only one independent x-variable, you can obtain the slope and y-intercept values directly by using the following formulas:

Slope:
=INDEX(LINEST(known_y's,known_x's),1)

Y-intercept:
=INDEX(LINEST(known_y's,known_x's),2)

• The accuracy of the line calculated by the LINEST function depends on the degree of scatter in your data. The more linear the data, the more accurate the LINEST model. LINEST uses the method of least squares for determining the best fit for the data. When you have only one independent x-variable, the calculations for m and b are based on the following formulas:

where x and y are sample means; that is, x = AVERAGE(known x's) and y = AVERAGE(known_y's).

• The line- and curve-fitting functions LINEST and LOGEST can calculate the best straight line or exponential curve that fits your data. However, you have to decide which of the two results best fits your data. You can calculate TREND(known_y's,known_x's) for a straight line, or GROWTH(known_y's, known_x's) for an exponential curve. These functions, without the new_x's argument, return an array of y-values predicted along that line or curve at your actual data points. You can then compare the predicted values with the actual values. You may want to chart them both for a visual comparison.
• In regression analysis, Excel calculates for each point the squared difference between the y-value estimated for that point and its actual y-value. The sum of these squared differences is called the residual sum of squares, ssresid. Excel then calculates the total sum of squares, sstotal. When the const argument = TRUE or is omitted, the total sum of squares is the sum of the squared differences between the actual y-values and the average of the y-values. When the const argument = FALSE, the total sum of squares is the sum of the squares of the actual y-values (without subtracting the average y-value from each individual y-value). Then regression sum of squares, ssreg, can be found from: ssreg = sstotal - ssresid. The smaller the residual sum of squares is, compared with the total sum of squares, the larger the value of the coefficient of determination, r2, which is an indicator of how well the equation resulting from the regression analysis explains the relationship among the variables. The value of r2 equals ssreg/sstotal.
• In some cases, one or more of the X columns (assume that Y’s and X’s are in columns) may have no additional predictive value in the presence of the other X columns. In other words, eliminating one or more X columns might lead to predicted Y values that are equally accurate. In that case these redundant X columns should be omitted from the regression model. This phenomenon is called “collinearity” because any redundant X column can be expressed as a sum of multiples of the non-redundant X columns. The LINEST function checks for collinearity and removes any redundant X columns from the regression model when it identifies them. Removed X columns can be recognized in LINEST output as having 0 coefficients in addition to 0 se values. If one or more columns are removed as redundant, df is affected because df depends on the number of X columns actually used for predictive purposes. For details on the computation of df, see Example 4. If df is changed because redundant X columns are removed, values of sey and F are also affected. Collinearity should be relatively rare in practice. However, one case where it is more likely to arise is when some X columns contain only 0 and 1 values as indicators of whether a subject in an experiment is or is not a member of a particular group. If const = TRUE or is omitted, the LINEST function effectively inserts an additional X column of all 1 values to model the intercept. If you have a column with a 1 for each subject if male, or 0 if not, and you also have a column with a 1 for each subject if female, or 0 if not, this latter column is redundant because entries in it can be obtained from subtracting the entry in the “male indicator” column from the entry in the additional column of all 1 values added by the LINEST function.
• The value of df is calculated as follows, when no X columns are removed from the model due to collinearity: if there are k columns of known_x’s and const = TRUE or is omitted, df = n – k – 1. If const = FALSE, df = n - k. In both cases, each X column that was removed due to collinearity increases the value of df by 1.
• Formulas that return arrays must be entered as array formulas.
• When entering an array constant (such as known_x's) as an argument, use commas to separate values that are contained in the same row and semicolons to separate rows. Separator characters may differ, depending on your locale setting in Regional and Language Options in Control Panel.
• Note that the y-values predicted by the regression equation may not be valid if they are outside the range of the y-values you used to determine the equation.
• The underlying algorithm used in the LINEST function is different than the underlying algorithm used in the SLOPE and INTERCEPT functions. The difference between these algorithms can lead to different results when data is undetermined and collinear. For example, if the data points of the known_y's argument are 0 and the data points of the known_x's argument are 1:
  • LINEST returns a value of 0. The algorithm of the LINEST function is designed to return reasonable results for collinear data and, in this case, at least one answer can be found.
  • SLOPE and INTERCEPT return a #DIV/0! error. The algorithm of the SLOPE and INTERCEPT functions is designed to look for only one answer, and in this case there can be more than one answer.
• In addition to using LOGEST to calculate statistics for other regression types, you can use LINEST to calculate a range of other regression types by entering functions of the x and y variables as the x and y series for LINEST. For example, the following formula:

=LINEST(yvalues, xvalues^COLUMN($A:$C))

works when you have a single column of y-values and a single column of x-values to calculate the cubic (polynomial of order 3) approximation of the form:

y = m1*x + m2*x^2 + m3*x^3 + b

You can adjust this formula to calculate other types of regression, but in some cases it requires the adjustment of the output values and other statistics.

Example 1

Slope and Y-Intercept

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Important  The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A7:B7, starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.

When entered as an array, the slope (2) and the y-intercept (1) are returned.

Example 2

Simple Linear Regression

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

In general, SUM({m,b}*{x,1}) equals mx + b, the estimated y-value for a given x-value. You can also use the TREND function.

Example 3

Multiple Linear Regression

Suppose a commercial developer is considering purchasing a group of small office buildings in an established business district.

The developer can use multiple linear regression analysis to estimate the value of an office building in a given area based on the following variables.

This example assumes that a straight-line relationship exists between each independent variable (x1, x2, x3, and x4) and the dependent variable (y), the value of office buildings in the area.

The developer randomly chooses a sample of 11 office buildings from a possible 1,500 office buildings and obtains the following data. "Half an entrance" means an entrance for deliveries only.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Important  The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A14:E18 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is -234.2371645.

When entered as an array, the following regression statistics are returned. Use this key to identify the statistic you want.

The multiple regression equation, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, can be obtained by using the values from row 14:

y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318

The developer can now estimate the assessed value of an office building in the same area that has 2,500 square feet, three offices, and two entrances and is 25 years old, by using the following equation:

y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261

Alternatively, you can copy the following table to cell A21 of the worksheet that you created for this example.

You can also use the TREND function to calculate this value.

Example 4

Using the F and r2 Statistics

In the preceding example, the coefficient of determination, or r2, is 0.99675 (see cell A17 in the output for LINEST), which would indicate a strong relationship between the independent variables and the sale price. You can use the F statistic to determine whether these results, with such a high r2 value, occurred by chance.

Assume for the moment that in fact there is no relationship among the variables, but that you have drawn a rare sample of 11 office buildings that causes the statistical analysis to demonstrate a strong relationship. The term "Alpha" is used for the probability of erroneously concluding that there is a relationship.

The F and df values in output from the LINEST function can be used to assess the likelihood of a higher F value occurring by chance. F can be compared with critical values in published F-distribution tables or the FDIST function in Excel can be used to calculate the probability of a larger F value occurring by chance. The appropriate F distribution has v1 and v2 degrees of freedom. If n is the number of data points and const = TRUE or omitted, then v1 = n – df – 1 and v2 = df. (If const = FALSE, then v1 = n – df and v2 = df.) The FDIST function — with the syntax FDIST(F,v1,v2) — will return the probability of a higher F value occurring by chance. In this example, df = 6 (cell B18) and F = 459.753674 (cell A18).

Assuming an Alpha value of 0.05, v1 = 11 – 6 – 1 = 4 and v2 = 6, the critical level of F is 4.53. Since F = 459.753674 is much higher than 4.53, it is extremely unlikely that an F value this high occurred by chance. (With Alpha = 0.05, the hypothesis that there is no relationship between known_y’s and known_x’s is to be rejected when F exceeds the critical level, 4.53.) You can use the FDIST function in Excel to obtain the probability that an F value this high occurred by chance. For example, FDIST(459.753674, 4, 6) = 1.37E-7, an extremely small probability. You can conclude, either by finding the critical level of F in a table or by using the FDIST function, that the regression equation is useful in predicting the assessed value of office buildings in this area. Remember that it is critical to use the correct values of v1 and v2 that were computed in the preceding paragraph.

Example 5

Calculating the t-Statistics

Another hypothesis test will determine whether each slope coefficient is useful in estimating the assessed value of an office building in Example 3. For example, to test the age coefficient for statistical significance, divide -234.24 (age slope coefficient) by 13.268 (the estimated standard error of age coefficients in cell A15). The following is the t-observed value:

t = m4 ק se4 = -234.24 ק 13.268 = -17.7

If the absolute value of t is sufficiently high, it can be concluded that the slope coefficient is useful in estimating the assessed value of an office building in Example 3. The following table shows the absolute values of the 4 t-observed values.

If you consult a table in a statistics manual, you will find that t-critical, two tailed, with 6 degrees of freedom and Alpha = 0.05 is 2.447. This critical value can also be found by using the TINV function in Excel. TINV(0.05,6) = 2.447. Because the absolute value of t (17.7) is greater than 2.447, age is an important variable when estimating the assessed value of an office building. Each of the other independent variables can be tested for statistical significance in a similar manner. The following are the t-observed values for each of the independent variables.

These values all have an absolute value greater than 2.447; therefore, all the variables used in the regression equation are useful in predicting the assessed value of office buildings in this area.

See Also

• Add, change, or remove a trendline in a chart
• GROWTH function
• LOGEST function
• Statistical functions
• TREND function

 

 

Excel > Function reference > Statistical

LOGINV function

Excel 2007

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. If p = LOGNORMDIST(x,...) then LOGINV(p,...) = x.

Use the lognormal distribution to analyze logarithmically transformed data.

Syntax

LOGINV(probability,mean,standard_dev)

Probability   is a probability associated with the lognormal distribution.

Mean   is the mean of ln(x).

Standard_dev   is the standard deviation of ln(x).

Remarks

• If any argument is nonnumeric, LOGINV returns the #VALUE! error value.
• If probability < 0 or probability > 1, LOGINV returns the #NUM! error value.
• If standard_dev <= 0, LOGINV returns the #NUM! error value.
• The inverse of the lognormal distribution function is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• EXP function
• LN function
• LOG function
• LOG10 function
• LOGNORMDIST function
• Statistical functions

 

 

Excel > Function reference > Statistical

LOGNORMDIST function

Excel 2007

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev. Use this function to analyze data that has been logarithmically transformed.

Syntax

LOGNORMDIST(x,mean,standard_dev)

X   is the value at which to evaluate the function.

Mean   is the mean of ln(x).

Standard_dev   is the standard deviation of ln(x).

Remarks

• If any argument is nonnumeric, LOGNORMDIST returns the #VALUE! error value.
• If x ≤ 0 or if standard_dev ≤ 0, LOGNORMDIST returns the #NUM! error value.
• The equation for the lognormal cumulative distribution function is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• EXP function
• LN function
• LOG function
• LOG10 function
• LOGINV function
• Statistical functions

 

 

Excel > Function reference > Statistical

MAX function

Excel 2007

Returns the largest value in a set of values.

Syntax

MAX(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want to find the maximum value.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored.
• If the arguments contain no numbers, MAX returns 0 (zero).
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Calculate the smallest or largest number in a range
• DMAX function
• MAXA function
• MIN function
• MINA function
• Statistical functions

 

 

Excel > Function reference > Statistical

MAXA function

Excel 2007

Returns the largest value in a list of arguments.

MAXA is similar to MINA. For more information, see the examples for MINA.

Syntax

MAXA(value1,value2,...)

Value1, value2, ...   are 1 to 255 values for which you want to find the largest value.

Remarks

• Arguments can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
• If the arguments contain no values, MAXA returns 0 (zero).
• If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAX function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DMAX function
• MINA function
• Statistical functions

 

 

Excel > Function reference > Statistical

MEDIAN function

Excel 2007

Returns the median of the given numbers. The median is the number in the middle of a set of numbers.

Syntax

MEDIAN(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want the median.

Remarks

• If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second formula in the example.
• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.

 Note    The MEDIAN function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average   which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median   which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode   which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• Calculate the median of a group of numbers
• COUNT function
• COUNTA function
• DAVERAGE function
• MODE function
• Statistical functions

 

 

Excel > Function reference > Statistical

MIN function

Excel 2007

Returns the smallest number in a set of values.

Syntax

MIN(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want to find the minimum value.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored.
• If the arguments contain no numbers, MIN returns 0.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MINA function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Calculate the smallest or largest number in a range
• DMIN function
• MAX function
• MAXA function
• MINA function
• Statistical functions

 

 

Excel > Function reference > Statistical

MINA function

Excel 2007

Returns the smallest value in the list of arguments.

Syntax

MINA(value1,value2,...)

Value1, value2, ...   are 1 to 255 values for which you want to find the smallest value.

Remarks

• Arguments can be the following: numbers; names, arrays, or references that contain numbers; text representations of numbers; or logical values, such as TRUE and FALSE, in a reference.
• If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored.
• Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If the arguments contain no values, MINA returns 0.
• If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the MIN function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• DMIN function
• MAXA function
• Statistical functions

 

 

Excel > Function reference > Statistical

MODE function

Excel 2007

Returns the most frequently occurring, or repetitive, value in an array or range of data.

Syntax

MODE(number1,number2,...)

Number1, number2, ...   are 1 to 255 arguments for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• Arguments can either be numbers or names, arrays, or references that contain numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If the data set contains no duplicate data points, MODE returns the #N/A error value.

 Note    The MODE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

• Average   which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
• Median   which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
• Mode   which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• GEOMEAN function
• HARMEAN function
• MEDIAN function
• Statistical functions
• TRIMMEAN function