NEGBINOMDIST function

Excel 2007

Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.

For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.3. NEGBINOMDIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.

Syntax

NEGBINOMDIST(number_f,number_s,probability_s)

Number_f   is the number of failures.

Number_s   is the threshold number of successes.

Probability_s   is the probability of a success.

Remarks

• Number_f and number_s are truncated to integers.
• If any argument is nonnumeric, NEGBINOMDIST returns the #VALUE! error value.
• If probability_s < 0 or if probability > 1, NEGBINOMDIST returns the #NUM! error value.
• If number_f < 0 or number_s < 1, NEGBINOMDIST returns the #NUM! error value.
• The equation for the negative binomial distribution is:

where:

x is number_f, r is number_s, and p is probability_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

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

Excel > Function reference > Statistical

NORMDIST function

Excel 2007

Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.

Syntax

NORMDIST(x,mean,standard_dev,cumulative)

X   is the value for which you want the distribution.

Mean   is the arithmetic mean of the distribution.

Standard_dev   is the standard deviation of the distribution.

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

Remarks

• If mean or standard_dev is nonnumeric, NORMDIST returns the #VALUE! error value.
• If standard_dev ≤ 0, NORMDIST returns the #NUM! error value.
• If mean = 0, standard_dev = 1, and cumulative = TRUE, NORMDIST returns the standard normal distribution, NORMSDIST.
• The equation for the normal density function (cumulative = FALSE) is:

• When cumulative = TRUE, the formula is the integral from negative infinity to x of the given formula.

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

• NORMINV function
• NORMSDIST function
• NORMSINV function
• STANDARDIZE function
• Statistical functions
• ZTEST function

Excel > Function reference > Statistical

NORMINV function

Excel 2007

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Syntax

NORMINV(probability,mean,standard_dev)

Probability   is a probability corresponding to the normal distribution.

Mean   is the arithmetic mean of the distribution.

Standard_dev   is the standard deviation of the distribution.

Remarks

• If any argument is nonnumeric, NORMINV returns the #VALUE! error value.
• If probability < 0 or if probability > 1, NORMINV returns the #NUM! error value.
• If standard_dev ≤ 0, NORMINV returns the #NUM! error value.
• If mean = 0 and standard_dev = 1, NORMINV uses the standard normal distribution (see NORMSINV).

Given a value for probability, NORMINV seeks that value x such that NORMDIST(x, mean, standard_dev, TRUE) = probability. Thus, precision of NORMINV depends on precision of NORMDIST. NORMINV 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

• NORMDIST function
• NORMSDIST function
• NORMSINV function
• STANDARDIZE function
• Statistical functions
• ZTEST function

Excel > Function reference > Statistical

NORMSDIST function

Excel 2007

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

Syntax

NORMSDIST(z)

Z   is the value for which you want the distribution.

Remarks

• If z is nonnumeric, NORMSDIST returns the #VALUE! error value.
• The equation for the standard normal density 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

• NORMDIST function
• NORMINV function
• NORMSINV function
• STANDARDIZE function
• Statistical functions
• ZTEST function

Excel > Function reference > Statistical

NORMSINV function

Excel 2007

Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.

Syntax

NORMSINV(probability)

Probability   is a probability corresponding to the normal distribution.

Remarks

• If probability is nonnumeric, NORMSINV returns the #VALUE! error value.
• If probability < 0 or if probability > 1, NORMSINV returns the #NUM! error value.

Given a value for probability, NORMSINV seeks that value z such that NORMSDIST(z) = probability. Thus, precision of NORMSINV depends on precision of NORMSDIST. NORMSINV 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

• NORMDIST function
• NORMINV function
• NORMSDIST function
• STANDARDIZE function
• Statistical functions
• ZTEST function

Excel > Function reference > Statistical

PEARSON function

Excel 2007

Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.

Syntax

PEARSON(array1,array2)

Array1   is a set of independent values.

Array2   is a set of dependent values.

Remarks

• The arguments must be either numbers or names, array constants, 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 are empty or have a different number of data points, PEARSON returns the #N/A error value.
• The formula for the Pearson product moment correlation coefficient, r, 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

• INTERCEPT function
• LINEST function
• RSQ function
• SLOPE function
• Statistical functions
• STEYX function

Excel > Function reference > Statistical

PERCENTILE function

Excel 2007

Returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

Syntax

PERCENTILE(array,k)

Array   is the array or range of data that defines relative standing.

K   is the percentile value in the range 0..1, inclusive.

Remarks

• If array is empty or contains more than 8,191 data points, PERCENTILE returns the #NUM! error value.
• If k is nonnumeric, PERCENTILE returns the #VALUE! error value.
• If k is < 0 or if k > 1, PERCENTILE returns the #NUM! error value.
• If k is not a multiple of 1/(n - 1), PERCENTILE interpolates to determine the value at the k-th percentile.

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

• LARGE function
• MAX function
• MEDIAN function
• MIN function
• PERCENTRANK function
• QUARTILE function
• SMALL function
• Statistical functions

Excel > Function reference > Statistical

PERCENTRANK function

Excel 2007

Returns the rank of a value in a data set as a percentage of the data set. This function can be used to evaluate the relative standing of a value within a data set. For example, you can use PERCENTRANK to evaluate the standing of an aptitude test score among all scores for the test.

Syntax

PERCENTRANK(array,x,significance)

Array   is the array or range of data with numeric values that defines relative standing.

X   is the value for which you want to know the rank.

Significance   is an optional value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK uses three digits (0.xxx).

Remarks

• If array is empty, PERCENTRANK returns the #NUM! error value.
• If significance < 1, PERCENTRANK returns the #NUM! error value.
• If x does not match one of the values in array, PERCENTRANK interpolates to return the correct percentage rank.

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.

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

See Also

• LARGE function
• MAX function
• MEDIAN function
• MIN function
• PERCENTILE function
• QUARTILE function
• SMALL function
• Statistical functions

Excel > Function reference > Statistical

PERMUT function

Excel 2007

Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant. Use this function for lottery-style probability calculations.

Syntax

PERMUT(number,number_chosen)

Number   is an integer that describes the number of objects.

Number_chosen   is an integer that describes the number of objects in each permutation.

Remarks

• Both arguments are truncated to integers.
• If number or number_chosen is nonnumeric, PERMUT returns the #VALUE! error value.
• If number ≤ 0 or if number_chosen < 0, PERMUT returns the #NUM! error value.
• If number < number_chosen, PERMUT returns the #NUM! error value.
• The equation for the number of permutations is:

Example

Suppose you want to calculate the odds of selecting a winning lottery number. Each lottery number contains three numbers, each of which can be between 0 (zero) and 99, inclusive. The following function calculates the number of possible permutations:

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
• CRITBINOM function
• FACT function
• HYPGEOMDIST function
• NEGBINOMDIST function
• Statistical functions

Excel > Function reference > Statistical

POISSON function

Excel 2007

Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.

Syntax

POISSON(x,mean,cumulative)

X   is the number of events.

Mean   is the expected numeric value.

Cumulative   is a logical value that determines the form of the probability distribution returned. If cumulative is TRUE, POISSON returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.

Remarks

• If x is not an integer, it is truncated.
• If x or mean is nonnumeric, POISSON returns the #VALUE! error value.
• If x < 0, POISSON returns the #NUM! error value.
• If mean < 0, POISSON returns the #NUM! error value.
• POISSON is calculated as follows.

For cumulative = FALSE:

For cumulative = TRUE:

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

• EXPONDIST function
• Statistical functions

Excel > Function reference > Statistical

PROB function

Excel 2007

Returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit.

Syntax

PROB(x_range,prob_range,lower_limit,upper_limit)

X_range   is the range of numeric values of x with which there are associated probabilities.

Prob_range   is a set of probabilities associated with values in x_range.

Lower_limit   is the lower bound on the value for which you want a probability.

Upper_limit   is the optional upper bound on the value for which you want a probability.

Remarks

• If any value in prob_range ≤ 0 or if any value in prob_range > 1, PROB returns the #NUM! error value.
• If the sum of the values in prob_range is not equal to 1, PROB returns the #NUM! error value.
• If upper_limit is omitted, PROB returns the probability of being equal to lower_limit.
• If x_range and prob_range contain a different number of data points, PROB 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

• BINOMDIST function
• CRITBINOM function
• Statistical functions

Excel > Function reference > Statistical

QUARTILE function

Excel 2007

Returns the quartile of a data set. Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE to find the top 25 percent of incomes in a population.

Syntax

QUARTILE(array,quart)

Array   is the array or cell range of numeric values for which you want the quartile value.

Quart   indicates which value to return.

Remarks

• If array is empty, QUARTILE returns the #NUM! error value.
• If quart is not an integer, it is truncated.
• If quart < 0 or if quart > 4, QUARTILE returns the #NUM! error value.
• MIN, MEDIAN, and MAX return the same value as QUARTILE when quart is equal to 0 (zero), 2, and 4, respectively.

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

• LARGE function
• MAX function
• MEDIAN function
• MIN function
• PERCENTILE function
• PERCENTRANK function
• SMALL function
• Statistical functions

Excel > Function reference > Statistical

RANK function

Excel 2007

Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

Syntax

RANK(number,ref,order)

Number   is the number whose rank you want to find.

Ref   is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

Order   is a number specifying how to rank number.

• If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.
• If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

Remarks

• RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
• For some purposes one might want to use a definition of rank that takes ties into account. In the previous example, one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).

Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.

In the following example, RANK(A2,A1:A5,1) equals 3. The correction factor is (5 + 1 – 2 – 3)/2 = 0.5 and the revised rank that takes ties into account is 3 + 0.5 = 3.5. If number occurs only once in ref, the correction factor will be 0, since RANK would not have to be adjusted for a tie.

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

• MAX function
• MIN function
• Statistical functions

Excel > Function reference > Statistical

RSQ function

Excel 2007

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. For more information, see PEARSON. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.

Syntax

RSQ(known_y's,known_x's)

Known_y's   is an array or range of data points.

Known_x's   is an array or range of data points.

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 known_y's and known_x's are empty or have a different number of data points, RSQ returns the #N/A error value.
• If known_y's and known_x's contain only 1 data point, RSQ returns the #DIV/0! error value.
• The equation for the Pearson product moment correlation coefficient, r, is:

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

RSQ returns r2, which is the square of this correlation coefficient.

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

• Statistical functions

Excel > Function reference > Statistical

SKEW function

Excel 2007

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Syntax

SKEW(number1,number2,...)

Number1, number2 ...   are 1 to 255 arguments for which you want to calculate skewness. 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 three data points, or the sample standard deviation is zero, SKEW returns the #DIV/0! error value.
• The equation for skewness is defined as:

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

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

Excel > Function reference > Statistical

SLOPE function

Excel 2007

Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.

Syntax

SLOPE(known_y's,known_x's)

Known_y's   is an array or cell range of numeric dependent data points.

Known_x's   is the set of independent data points.

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 known_y's and known_x's are empty or have a different number of data points, SLOPE returns the #N/A error value.
• The equation for the slope of the regression line is:

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

• The underlying algorithm used in the SLOPE and INTERCEPT 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:
  • SLOPE and INTERCEPT return a #DIV/0! error. The SLOPE and INTERCEPT 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

• INTERCEPT function
• LINEST function
• LOGEST function
• PEARSON function
• RSQ function
• Statistical functions
• STEYX function
• TREND function

Excel > Function reference > Statistical

SMALL function

Excel 2007

Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.

Syntax

SMALL(array,k)

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

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

Remarks

• If array is empty, SMALL returns the #NUM! error value.
• If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM! error value.
• If n is the number of data points in array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest 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
• LARGE function
• MAX function
• MEDIAN function
• MIN function
• PERCENTILE function
• PERCENTRANK function
• QUARTILE function
• Statistical functions

Excel > Function reference > Statistical

STANDARDIZE function

Excel 2007

Returns a normalized value from a distribution characterized by mean and standard_dev.

Syntax

STANDARDIZE(x,mean,standard_dev)

X   is the value you want to normalize.

Mean   is the arithmetic mean of the distribution.

Standard_dev   is the standard deviation of the distribution.

Remarks

• If standard_dev ≤ 0, STANDARDIZE returns the #NUM! error value.
• The equation for the normalized value 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

• NORMDIST function
• NORMINV function
• NORMSDIST function
• NORMSINV function
• Statistical functions
• ZTEST function

Excel > Function reference > Statistical

STDEV function

Excel 2007

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEV(number1,number2,...)

Number1, number2, ...   are 1 to 255 number arguments corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• STDEV assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the standard deviation using STDEVP.
• The standard deviation is calculated using the "n-1" method.
• 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 counted. Empty cells, logical values, text, or error 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 want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEVA function.
• STDEV uses the following formula:

where x is the sample mean AVERAGE(number1,number2,…) and n is the sample size.

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

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

• DSTDEV function
• DSTDEVP function
• Statistical functions
• STDEVA function
• STDEVP function
• STDEVPA function

Excel > Function reference > Statistical

STDEVA function

Excel 2007

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEVA(value1,value2,...)

Value1, value2, ...   are 1 to 255 values corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• STDEVA assumes that its arguments are a sample of the population. If your data represents the entire population, you must compute the standard deviation using STDEVPA.
• The standard deviation is calculated using the "n-1" method.
• 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.
• Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate 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 STDEV function.
• STDEVA uses the following formula:

where x is the sample mean AVERAGE(value1,value2,…) and n is the sample size.

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

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

• DSTDEV function
• DSTDEVP function
• Statistical functions
• STDEV function
• STDEVP function
• STDEVPA function

Excel > Function reference > Statistical

STDEVP function

Excel 2007

Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEVP(number1,number2,...)

Number1, number2, ...   are 1 to 255 number arguments corresponding to a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• STDEVP assumes that its arguments are the entire population. If your data represents a sample of the population, then compute the standard deviation using STDEV.
• For large sample sizes, STDEV and STDEVP return approximately equal values.
• The standard deviation is calculated using the "n" method.
• 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 counted. Empty cells, logical values, text, or error 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 want to include logical values and text representations of numbers in a reference as part of the calculation, use the STDEVPA function.
• STDEVP uses the following formula:

where x is the sample mean AVERAGE(number1,number2,…) 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

• DSTDEV function
• DSTDEVP function
• Statistical functions
• STDEV function
• STDEVA function
• STDEVPA function

Excel > Function reference > Statistical

STDEVPA function

Excel 2007

Calculates standard deviation based on the entire population given as arguments, including text and logical values. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEVPA(value1,value2,...)

Value1, value2, ...   are 1 to 255 values corresponding to a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

• STDEVPA assumes that its arguments are the entire population. If your data represents a sample of the population, you must compute the standard deviation by using STDEVA.
• For large sample sizes, STDEVA and STDEVPA return approximately equal values.
• The standard deviation is calculated using the "n" method.
• 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.
• 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 text or FALSE evaluate 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 STDEVP function.
• STDEVPA uses the following formula:

where x is the sample mean AVERAGE(value1,value2,…) 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

• DSTDEV function
• DSTDEVP function
• Statistical functions
• STDEVA function
• STDEVP function

Excel > Function reference > Statistical

STEYX function

Excel 2007

Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x.

Syntax

STEYX(known_y's,known_x's)

Known_y's   is an array or range of dependent data points.

Known_x's   is an array or range of independent data points.

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 known_y's and known_x's have a different number of data points, STEYX returns the #N/A error value.
• If known_y's and known_x's are empty or have less than three data points, STEYX returns the #DIV/0! error value.
• The equation for the standard error of the predicted y is:

where x and y are the sample means AVERAGE(known_x’s) and AVERAGE(known_y’s), 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

• INTERCEPT function
• LINEST function
• LOGEST function
• PEARSON function
• RSQ function
• SLOPE function
• Statistical functions

Excel > Function reference > Statistical

TDIST function

Excel 2007

Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.

Syntax

TDIST(x,degrees_freedom,tails)

X   is the numeric value at which to evaluate the distribution.

Degrees_freedom   is an integer indicating the number of degrees of freedom.

Tails   specifies the number of distribution tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails = 2, TDIST returns the two-tailed distribution.

Remarks

• If any argument is nonnumeric, TDIST returns the #VALUE! error value.
• If degrees_freedom < 1, TDIST returns the #NUM! error value.
• The degrees_freedom and tails arguments are truncated to integers.
• If tails is any value other than 1 or 2, TDIST returns the #NUM! error value.
• If x < 0, then TDIST returns the #NUM! error value.
• If tails = 1, TDIST is calculated as TDIST = P( X>x ), where X is a random variable that follows the t-distribution. If tails = 2, TDIST is calculated as TDIST = P(|X| > x) = P(X > x or X < -x).
• Since x < 0 is not allowed, to use TDIST when x < 0, note that TDIST(-x,df,1) = 1 – TDIST(x,df,1) = P(X > -x) and TDIST(-x,df,2) = TDIST(x df,2) = P(|X| > x).

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.

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

See Also

• Statistical functions
• TINV function
• TTEST function

Excel > Function reference > Statistical

TINV function

Excel 2007

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

Syntax

TINV(probability,degrees_freedom)

Probability   is the probability associated with the two-tailed Student's t-distribution.

Degrees_freedom   is the number of degrees of freedom with which to characterize the distribution.

Remarks

• If either argument is nonnumeric, TINV returns the #VALUE! error value.
• If probability < 0 or if probability > 1, TINV returns the #NUM! error value.
• If degrees_freedom is not an integer, it is truncated.
• If degrees_freedom < 1, TINV returns the #NUM! error value.
• TINV returns that value t, such that P(|X| > t) = probability where X is a random variable that follows the t-distribution and P(|X| > t) = P(X < -t or X > t).
• A one-tailed t-value can be returned by replacing probability with 2*probability. For a probability of 0.05 and degrees of freedom of 10, the two-tailed value is calculated with TINV(0.05,10), which returns 2.28139. The one-tailed value for the same probability and degrees of freedom can be calculated with TINV(2*0.05,10), which returns 1.812462.

 Note     In some tables, probability is described as (1-p).

Given a value for probability, TINV seeks that value x such that TDIST(x, degrees_freedom, 2) = probability. Thus, precision of TINV depends on precision of TDIST. TINV 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

• Statistical functions
• TDIST function
• TTEST function

Excel > Function reference > Statistical

TREND function

Excel 2007

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

Syntax

TREND(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 = mx + b.

• 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.

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

• 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 TREND 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 you omit new_x's, it is assumed to be the same as known_x's.
• If you omit both known_x's and new_x's, 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 0.

• If const is TRUE or omitted, b is calculated normally.
• If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx.

Remarks

• For information about how Microsoft Excel fits a line to data, see LINEST.
• You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A.
• Formulas that return arrays must be entered as array formulas.
• 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.

The first formula shows corresponding values to the known values. The second formula predicts the next months values, if the linear 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:C13 or B15:B19 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 133953.3333 and 146171.5152.

See Also

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

Excel > Function reference > Statistical

TRIMMEAN function

Excel 2007

Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.

Syntax

TRIMMEAN(array,percent)

Array   is the array or range of values to trim and average.

Percent   is the fractional number of data points to exclude from the calculation. For example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set.

Remarks

• If percent < 0 or percent > 1, TRIMMEAN returns the #NUM! error value.
• TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set.

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
• MODE function
• Statistical functions

Excel > Function reference > Statistical

TTEST function

Excel 2007

Returns the probability associated with a Student's t-Test. Use TTEST to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.

Syntax

TTEST(array1,array2,tails,type)

Array1   is the first data set.

Array2   is the second data set.

Tails   specifies the number of distribution tails. If tails = 1, TTEST uses the one-tailed distribution. If tails = 2, TTEST uses the two-tailed distribution.

Type   is the kind of t-Test to perform.

Remarks

• If array1 and array2 have a different number of data points, and type = 1 (paired), TTEST returns the #N/A error value.
• The tails and type arguments are truncated to integers.
• If tails or type is nonnumeric, TTEST returns the #VALUE! error value.
• If tails is any value other than 1 or 2, TTEST returns the #NUM! error value.
• TTEST uses the data in array1 and array2 to compute a non-negative t-statistic. If tails=1, TTEST returns the probability of a higher value of the t-statistic under the assumption that array1 and array2 are samples from populations with the same mean. The value returned by TTEST when tails=2 is double that returned when tails=1 and corresponds to the probability of a higher absolute value of the t-statistic under the “same population means” assumption.

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

• Statistical functions
• TDIST function
• TINV function

Excel > Function reference > Statistical

VAR function

Excel 2007

Estimates variance based on a sample.

Syntax

VAR(number1,number2,...)

Number1, number2, ...   are 1 to 255 number arguments corresponding to a sample of a population.

Remarks

• VAR assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance by using VARP.
• 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 counted. Empty cells, logical values, text, or error 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 want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARA function.
• VAR uses the following formula:

where x is the sample mean AVERAGE(number1,number2,…) and n is the sample size.

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

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

• DVAR function
• DVARP function
• Statistical functions

Excel > Function reference > Statistical

VARA function

Excel 2007

Estimates variance based on a sample.

Syntax

VARA(value1,value2,...)

Value1, value2, ...   are 1 to 255 value arguments corresponding to a sample of a population.

Remarks

• VARA assumes that its arguments are a sample of the population. If your data represents the entire population, you must compute the variance by using VARPA.
• 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 text or FALSE evaluate 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 VAR function.
• VARA uses the following formula:

where x is the sample mean AVERAGE(value1,value2,…) and n is the sample size.

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

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

• DVAR function
• DVARP function
• Statistical functions
• VAR function
• VARPA function

Excel > Function reference > Statistical

VARP function

Excel 2007

Calculates variance based on the entire population.

Syntax

VARP(number1,number2,...)

Number1, number2, ...   are 1 to 255 number arguments corresponding to a population.

Remarks

• VARP assumes that its arguments are the entire population. If your data represents a sample of the population, then compute the variance by using VAR.
• 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 counted. Empty cells, logical values, text, or error 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 want to include logical values and text representations of numbers in a reference as part of the calculation, use the VARPA function.
• The equation for VARP is:

where x is the sample mean AVERAGE(number1,number2,…) and n is the sample size.

Example

Suppose all 10 tools stamped from the same machine during a production run are collected and measured for breaking strength.

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

• DVAR function
• DVARP function
• Statistical functions

Excel > Function reference > Statistical

VARPA function

Excel 2007

Calculates variance based on the entire population.

Syntax

VARPA(value1,value2,...)

Value1, value2, ...   are 1 to 255 value arguments corresponding to a population.

Remarks

• VARPA assumes that its arguments are the entire population. If your data represents a sample of the population, you must compute the variance by using VARA.
• 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 text or FALSE evaluate 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 VARP function.
• The equation for VARPA is :

where x is the sample mean AVERAGE(value1,value2,…) and n is the sample size.

Example

Suppose all 10 tools stamped from the same machine during a production run are collected and measured for breaking strength.

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

• DVAR function
• DVARP function
• Statistical functions
• VARA function

Excel > Function reference > Statistical

WEIBULL function

Excel 2007

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.

Syntax

WEIBULL(x,alpha,beta,cumulative)

X   is the value at which to evaluate the function.

Alpha   is a parameter to the distribution.

Beta   is a parameter to the distribution.

Cumulative   determines the form of the function.

Remarks

• If x, alpha, or beta is nonnumeric, WEIBULL returns the #VALUE! error value.
• If x < 0, WEIBULL returns the #NUM! error value.
• If alpha ≤ 0 or if beta ≤ 0, WEIBULL returns the #NUM! error value.
• The equation for the Weibull cumulative distribution function is:

• The equation for the Weibull probability density function is:

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

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

• EXPONDIST function
• Statistical functions

Excel > Function reference > Statistical

ZTEST function

Excel 2007

Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, ל0, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) — that is, the observed sample mean.

To see how ZTEST can be used in a formula to compute a two-tailed probability value, see "Remarks" below.

Syntax

ZTEST(array,ל0,sigma)

Array   is the array or range of data against which to test ל0

ל0    is the value to test.

Sigma   is the population (known) standard deviation. If omitted, the sample standard deviation is used.

Remarks

• If array is empty, ZTEST returns the #N/A error value.
• ZTEST is calculated as follows when sigma is not omitted:

or when sigma is omitted:

where x is the sample mean AVERAGE(array); s is the sample standard deviation STDEV(array); and n is the number of observations in the sample COUNT(array).

• ZTEST represents the probability that the sample mean would be greater than the observed value AVERAGE(array), when the underlying population mean is ל0. From the symmetry of the Normal distribution, if AVERAGE(array) < ל0, ZTEST will return a value greater than 0.5.
• The following Excel formula can be used to calculate the two-tailed probability that the sample mean would be further from ל0 (in either direction) than AVERAGE(array), when the underlying population mean is ל0:

=2 * MIN(ZTEST(array,ל0,sigma), 1 - ZTEST(array,ל0,sigma)).

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.