# A to Z of Excel Functions: the FINV Function

24 June 2019

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FINV function. *

The F-distribution is defined as a continuous distribution obtained from the ratio of two chi-square distributions and used in particular to test the equality of the variances of two normally distributed variances a continuous probability distribution, which means that it is defined for an infinite number of different values. This analysis of variance is often referred to as “ANOVA”, which I still think is a cheap car.

In slightly simpler terms, the F-distribution can be used for several types of applications, including testing hypotheses about the equality of two population variances and testing the validity of what is known as a multiple regression equation, *i.e. *a formulaic attempt to model the relationship between two or more explanatory variables and a response variable by fitting a linear equation to observed data.

The F-distribution shares one important property with the Student’s t-distribution: probabilities are determined by a concept known as **degrees of freedom**. Unlike the Student’s t-distribution, the F-distribution is characterised by two different types of degrees of freedom: **numerator** and **denominator** degrees of freedom.

The F-distribution has two important properties:

- It’s defined only for positive values.
- It’s not symmetrical about its mean; instead, it’s positively skewed.

A distribution is positively skewed if the mean is greater than the median (the mean is the average value of a distribution, and the median is the midpoint; half of the values in the distribution are below the median and half are above). This function returns the (right-tailed) F probability distribution (degree of diversity) for two datasets.

This distribution is a relatively new one. The associated F statistic is a ratio (a fraction). There are two sets of degrees of freedom; one for the numerator and one for the denominator. As mentioned above, the F distribution is derived from the Student’s t-distribution. The values of the F distribution are squares of the corresponding values of the t-distribution.

To calculate the F ratio, two estimates of the variance are made:

**Variance between samples:**An estimate of**σ**that is the variance of the sample means multiplied by^{2}**n**(when the sample sizes are the same). If the samples are different sizes, the variance between samples is weighted to account for the different sample sizes. The variance is also called variation due to treatment or explained variation**Variance within samples:**An estimate of**σ**that is the average of the sample variances (also known as a pooled variance). When the sample sizes are different, the variance within samples is weighted. The variance is also called the variation due to error or unexplained variation:^{2}

**SS _{between}** = the sum of squares that represents the variation among the different samples

**SS _{within}** = the sum of squares that represents the variation within samples that is due to chance.

To find a “sum of squares” means to add together squared quantities that, in some cases, may be weighted. **MS** means “mean square”. **MS _{between}** is the variance between groups, and

**MS**is the variance within groups.

_{within}To calculate the sum of squares and the mean square, let:

**k**= the number of different groups**n**_{j}_{ }= the size of the**j**th group**s**= the sum of the values in the_{j}**j**th group**n**= total number of all the values combined (total sample size:**∑n**)_{j}**x**= one value:**∑x**=**∑s**_{j}

The sum of squares of all values from every group combined: **∑x ^{2}**

The between group variability is therefore

and the total sum of squares is given by

The explained variation is therefore the sum of squares representing variation among the different samples:

This should be compared with the unexplained variation, namely the sum of squares representing variation within samples due to chance:

**SS _{within}=SS_{total}−SS_{between}**

The degrees of freedom for different groups (*i.e.* the degrees of freedom for the numerator) is calculated as **df = k – 1**, whereas the equation for errors within samples (degrees of freedom for the denominator) is calculated as **df _{within} = n – k**.

Further, the mean square (variance estimate) explained by the different groups is

whereas the mean square (variance estimate) that is due to chance (unexplained) is

**MS _{between}** and

**MS**can be written as follows:

_{within}The one-way ANOVA test depends on the fact that **MS _{between}** can be influenced by population differences among means of the several groups. Since

**MS**compares values of each group to its own group mean, the fact that group means might be different does not affect

_{within}**MS**.

_{within}The null hypothesis says that all groups are samples from populations having the same normal distribution. The alternate hypothesis says that at least two of the sample groups come from populations with different normal distributions. If the null hypothesis (**H _{0}**) is TRUE,

**MS**and

_{between}**MS**should both estimate the same value.

_{within}To be clear, the null hypothesis says that all the group population means are equal. The hypothesis of equal means implies that the populations have the same normal distribution, because it is assumed that the populations are normal and that they have equal variances.

So, all of this brings us to the F-ratio or F-statistic, defined as

If **MS _{between}** and

**MS**estimate the same value (following the belief that

_{within}**H**is TRUE), then the F-ratio should be approximately equal to one (1). Mostly, just sampling errors would contribute to variations away from one. As it turns out,

_{0}**MS**consists of the population variance plus a variance produced from the differences between the samples.

_{between}**MS**is an estimate of the population variance. Since variances are always positive, if the null hypothesis is FALSE,

_{within}**MS**will generally be larger than

_{between}**MS**. Then the F-ratio will be larger than one. However, if the population effect is small, it is not unlikely that

_{within}**MS**will be larger in a given sample.

_{within}The foregoing calculations were done with groups of different sizes. If the groups are the same size, the calculations simplify somewhat, and the F-ratio can be written as:

where

**n**= the sample size**df**=_{numerator}**k**– 1**df**=_{denominator}**n – k****s**= the mean of the sample variances (pooled variance)^{2}_{pooled}**s**= the variance of the sample means.^{2/x}

Now that you have had the statistics lecture, you may use the worksheet function **FINV **to return a value for the F-ratio when you supply an area under the curve (*i.e. *the associated probability), plus the number of degrees of freedom for both the numerator and denominator alike, that define the distribution.

The traditional approach has been to obtain a critical F-value early on in an experiment. The researcher would know how many groups would be involved, and would have some knowledge of how many individual observations would be available at the conclusion of the experiment. The variable **alpha**, the level of significance, would be chosen before any outcome data is available.

For example, let’s consider a researcher who was testing four groups consisting of 10 people each, and that **alpha **was set to a level of 0.05 (5%). Then, an F-value could be looked up in a statistics text or else you could use the following formula to determine the critical F-value:

**=FINV(0.05,3,36)**.

**Important:** This **FINV **has been replaced by **F.INV.RT **so that it may provide improved accuracy and whose names better reflect their usage. Although the previous function is still available for backward compatibility, you should consider using this newer function from now on, because the older variant may not be available in future versions of Excel, according to Microsoft.

Further, from Excel 2010, you should use the following formula instead:

**=F.INV(0.95,3,36)**.

These two formulae will return the same value, 2.866. The older function, **FINV**, returns the F-value that cuts off the rightmost 5% of the distribution; the newer **F.INV **function returns the F-value that cuts off the leftmost 95% of the distribution. Be careful!!

Once this has been determined (using either of these Excel functions), the ANOVA test may now be completed , with the calculated F compared to the critical F, and if the former is greater than the latter, the null hypothesis (**H _{0}**) will be rejected.

In summary, the **FINV **function returns the inverse of the (right-tailed) 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.

The **FINV** function has the following arguments:

**probability:**this is required and represents the probability associated with the F-cumulative distribution**deg_freedom1:**this is also required and denotes the numerator degrees of freedom**deg_freedom2:**this is again required and represents the denominator degrees of freedom.

It should be further noted that:

- if any argument is non-numeric,
**FINV**returns the*#VALUE!*error value - if
**probability**< 0 or**probability**>1,**FINV**returns the*#NUM!*error value - if
**deg_freedom1**or**deg_freedom2**is not an integer, it is truncated - If either
**deg_freedom1**or**deg_freedom2**is strictly less than 1, or**deg_freedom1**or**deg_freedom2**is greater than or equal to 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, deg_freedom1, deg_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.

Please see my example below:

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.*

*A full page of the function articles can be found here. *