LOVE SCHOOL OF BUSINESS | ACCOUNTING & FINANCE | ECONOMICS | BUSINESS ADMINISTRATION | ELON UNIVERSITY 

Statistics Across the Curriculum
An initiative of the Martha & Spencer Love School of Business



 
Home

-----------------------------

Resources for Students

 
Online Textbook
 
Excel Review
 
Distribution Tables
-----------------------------
Resources for Faculty

  
Eco 203 Description

 

Excel Functions

TDIST, TINV,FDIST, FINV,CHIDIST,CHIINV, NORMDIST, NORMSINV

TDIST

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.

  • TDIST is calculated as TDIST = p( x<X ), where X is a random variable that follows the t-distribution.

Example

TDIST(1.96,60,2) equals 0.054645, or 5.46 percent

TINV

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 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 is calculated as TINV = p( t<X ), where X is a random variable that follows the t-distribution.

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

TINV uses an iterative technique for calculating the function. Given a probability value, TINV iterates until the result is accurate to within 3x10^-7. If TINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

TINV(0.054645,60) equals 1.96

FDIST

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 test scores given to 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.

Example

FDIST(15.20675,6,4) equals 0.01

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

FINV

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

FINV uses an iterative technique for calculating the function. Given a probability value, FINV iterates until the result is accurate to within 3x10^-7. If FINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

FINV(0.01,6,4) equals 15.20675

CHIDIST

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.

CHIINV

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

CHIINV uses an iterative technique for calculating the function. Given a probability value, CHIINV iterates until the result is accurate to within 3x10^-7. If CHIINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

CHIINV(0.05,10) equals 18.30703

NORMDIST

Returns the normal cumulative 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 and standard_dev = 1, NORMDIST returns the standard normal distribution, NORMSDIST.

Example

NORMDIST(42,40,1.5,TRUE) equals 0.908789

NORMSINV

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.

NORMSINV uses an iterative technique for calculating the function. Given a probability value, NORMSINV iterates until the result is accurate to within 3x10^-7. If NORMSINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

NORMSINV(0.908789) equals 1.3333


Elon University 
Campus Box 2700
Elon, NC 27244 (800) 334-8448
E-mail: web@elon.edu 
 

Last Modified: 01/06/06
Copyright Elon University