Excel Statistical Functions: FINV (828332)

MORE INFORMATION

Note In this article, p is a probability with 0 < p < 1 and df1 >= 1 and df2 >= 1 are the numbers of degrees of freedom for the numerator and denominator of the ratio that is assumed to have an F-distribution. Because df1 and df2 are integers, Excel truncates (or rounds down) the value to an integer value if a non-integer value is used for either df1 or df2.

The FINV(p, df1, df2) function is the inverse function for FDIST(x, df1, df2). For any particular x, FDIST(x, df1, df2) returns the probability that an F-distributed random variable with df1 and df2 degrees of freedom is greater than or equal to x.

The FINV(p, df1, df2) function returns the value of x where FDIST(x, df1, df2) returns p. Therefore, FINV is evaluated by a search process that returns the appropriate value of x by evaluating FDIST for various candidate values of x until it finds a value of x where FDIST(x, df1, df2) is "acceptably close" to p.

Syntax

FINV(p, df1, df2)

Example Usage

To illustrate the FINV function, follow these steps:

1. Create a blank Excel worksheet, copy the following table, and then click cell A1 in your blank Excel worksheet.
2. On the Edit menu, click Paste so that the entries in the table fill cells A1:D21 in your worksheet.
3. After you paste the table into your new Excel worksheet, click Paste Options, and then click Match Destination Formatting.
4. While the pasted range is selected, on the Format menu, point to Column, and then click AutoFit Selection.

y	x	forecast	error
10	1	=FORECAST(B2, $A$2:$A$7, $B$2:$B$7)	=C2-A2
16	2	=FORECAST(B3, $A$2:$A$7, $B$2:$B$7)	=C3-A3
33	3	=FORECAST(B4, $A$2:$A$7, $B$2:$B$7)	=C4-A4
36	4	=FORECAST(B5, $A$2:$A$7, $B$2:$B$7)	=C5-A5
47	5	=FORECAST(B6, $A$2:$A$7, $B$2:$B$7)	=C6-A6
51	6	=FORECAST(B7, $A$2:$A$7, $B$2:$B$7)	=C7-A7
LINEST output
=LINEST(A2:A7,B2:B7,TRUE,TRUE)	=LINEST(A2:A7,B2:B7,TRUE,TRUE)
=LINEST(A2:A7,B2:B7,TRUE,TRUE)	=LINEST(A2:A7,B2:B7,TRUE,TRUE)
=LINEST(A2:A7,B2:B7,TRUE,TRUE)	=LINEST(A2:A7,B2:B7,TRUE,TRUE)
=LINEST(A2:A7,B2:B7,TRUE,TRUE)	=LINEST(A2:A7,B2:B7,TRUE,TRUE)
=LINEST(A2:A7,B2:B7,TRUE,TRUE)	=LINEST(A2:A7,B2:B7,TRUE,TRUE)
=DEVSQ(A2:A7)
=SUMSQ(D2:D7)
=((A16-A17)/1)/(A17/4)
=FDIST(A18,1,4)
=FINV(A19,1,4)
=FINV(0.05,1,4)

The F-distribution is used in testing that is related to linear regression. In this example, you can test whether the linear regression with a non-zero slope (the slope value is 8.6 in cell A10) gives a significantly better fit than a linear regression with slope that is forced to be 0. Cells A2:B7 show data that is used for the LINEST function whose results are displayed in cells A10:B14. Cell A13 displays the value of the F-statistic and cell B13 contains the degrees of freedom for the denominator (df2 = number of data points - 2). The numerator has 1 degree of freedom so this F-statistic is from a distribution with 1 and 4 degrees of freedom.

The F-statistic is calculated by computing two different sums of squares. First, compute the total sum of squares, the sum of squared deviations of the y-values about their mean. This result is in cell A16. This value provides a measure of the goodness of fit of the model with a slope that is forced to 0. The second sum of squares is found by first computing the forecast y-values for specific x-values (in cells C2:C7), finding the differences between forecast and actual y-values (cells D2:D7), and then summing the squares of these differences. The result is in cell A17. The value in cell A17 is smaller than the value in cell A16 because there is a better fit with the best value of the slope than with a slope that is forced to 0. The F-statistic for the test is computed in cell A18 and its value agrees with that shown in the LINEST output in cell A13.

The FDIST value in cell A19 shows the probability of an F-statistic value that is greater than the value that is obtained in cell A18 under the null hypothesis that there is no relationship between the x-values and the y-values. The value of FINV in cell A20 illustrates the inverse relationship between FINV and FDIST. The value of FINV in cell A21 provides the cutoff that is used for a significance test with a probability 0.05 of rejecting the null hypothesis when it is true. In this example, the observed F-statistic (106.67) is much higher than this cutoff (7.71), so the null hypothesis of no relationship between the x-values and the y-values is rejected.

Results in Earlier Versions of Excel

The FINV(p, df1, df2) function is found through an iterative process that repeatedly evaluates FDIST(x, df1, df2) and returns a value of x such that FDIST(x, df1, df2) is "acceptably close" to p. The accuracy of FINV depends on two factors:

• The accuracy of FDIST
• The design of the search process and the definition of "acceptably close."

In rare cases, "acceptably close" in earlier versions of Excel might not be sufficiently close. This issue is not likely to affect most users. If you request FINV(p, df1, df2), the search continues until a value of x is found where FDIST(x, df1, df2) differs from p by less than 0.0000003.

Results in Excel 2003

No changes were made in Excel 2003 to FDIST. However, the definition of "acceptably close" was changed in the search process so that it is much closer to the actual value. The search now continues until the closest possible value of x is found (within the limits of finite precision arithmetic in Excel). The resulting x should have an FDIST(x, df1, df2) value that differs from p by about 10^(-15).

Conclusions

Many inverse functions have been improved in Excel 2003. Some functions have been improved in Excel 2003 only by allowing the search process to reach a higher level of refinement. The following inverse functions have been improved:

• BETAINV
• CHIINV
• FINV
• GAMMAINV
• TINV

No modifications were made to the following functions that are called by the inverse functions:

• BETADIST
• CHIDIST
• FDIST
• GAMMADIST
• TDIST

Additionally, the same improvement in the search process was made for the NORMSINV function in Excel 2002. In Excel 2003, the accuracy of the NORMSDIST function (called by NORMSINV) was also improved. These changes affect NORMINV and LOGINV (which call NORMSINV) and NORMDIST and LOGNORMDIST (which call NORMSDIST).

For more information about FINV, click Microsoft Excel Help on the Help menu, type finv in the Search for box in the Assistance pane, and then click Start searching to view the topic.