Article ID: 828332  View products that this article applies to. On This PageSUMMARYThe purpose of this article is to describe the FINV function in Microsoft Office Excel 2003 and in later versions of Excel. This article also discusses an improvement in Excel 2003 and in later versions of Excel that might affect your results when they are compared with earlier versions of Excel. MORE INFORMATION 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 Fdistribution. Because df1 and df2 are integers, Excel truncates (or rounds down) the value to an integer value if a noninteger 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 Fdistributed 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
Example of usageTo illustrate the FINV function, create a blank Excel worksheet, copy the following table, click cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:D21 in your worksheet.Collapse this table
The Fstatistic is calculated by computing two different sums of squares. First, compute the total sum of squares, the sum of squared deviations of the yvalues 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 yvalues for specific xvalues (in cells C2:C7), finding the differences between forecast and actual yvalues (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 Fstatistic 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 Fstatistic value that is greater than the value that is obtained in cell A18 under the null hypothesis that there is no relationship between the xvalues and the yvalues. 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 Fstatistic (106.67) is much higher than this cutoff (7.71), so the null hypothesis of no relationship between the xvalues and the yvalues is rejected. Results in earlier versions of ExcelThe 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 the following factors:
Results in Excel 2003 and in later versions of ExcelNo changes were made to FDIST in Excel 2003 and in later versions of Excel. 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).ConclusionsMany inverse functions have been improved in Excel 2003 and in later versions of Excel. Some functions have been improved in Excel 2003 and in later versions of Excel only by allowing the search process to reach a higher level of refinement. The following inverse functions have been improved:
PropertiesArticle ID: 828332  Last Review: January 18, 2007  Revision: 2.2
