A to Z of Excel Functions: The F.DIST.RT Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the F.DIST.RT function.

The F.DIST.RT 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 characterized by two different types of degrees of freedom: numerator and denominator degrees of freedom.

The F-distribution has two important properties:

1. It’s defined only for positive values.
2. 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 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:

1. SS_between = the sum of squares that represents the variation among the different samples
2. 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_withinis the variance within groups.

To calculate the sum of squares and the mean square, let:

• k = the number of different groups
• n_j = the size of the jth group
• s_j = the sum of the values in the jth 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²

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_within can be written as follows:

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_within compares values of each group to its own group mean, the fact that group means might be different does not affect 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₀) is TRUE,
MS_between and MS_within should both estimate the same value.

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_within estimate the same value (following the belief that 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, MS_between consists of the population variance plus a variance produced from the differences between the samples. MS_within is an estimate of the population variance. Since variances are always positive, if the null hypothesis is FALSE, MS_between will generally be larger than MS_within. Then the F-ratio will be larger than one. However, if the population effect is small, it is not unlikely that MS_within will be larger in a given sample.

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²_pooled = the mean of the sample variances (pooled variance)
• s^2/x = the variance of the sample means.

Now that you have had the statistics lecture, you may use the worksheet function F.DIST or the function F.DIST.RT in Excel to decide whether or not your calculated F-ratio is in the region of rejection. 

For F.DIST, you supply a value for the value at which to evaluate the function (x), a value for each degree of freedom (numerator and denominator), and a value (TRUE or FALSE) for the final argument (cumulative).  If the value for cumulative is TRUE, F.DIST returns the probability of obtaining an F-ratio of at most as high as yours if the hypothesis is TRUE (in Excel, this is called the “left-tail” probability).  

A level of significance is used to test a hypothesis.  An hypothesis test begins with a null hypothesis; this is a statement that’s assumed to be true unless there is strong contrary evidence.  There is also an alternative hypothesis; this is a statement that is accepted in place of the null hypothesis if there’s sufficient evidence to reject the null hypothesis.

If that probability is greater than 1 – alpha, where alpha is the level of significance, you reject the null hypothesis.  If the value for cumulative is FALSE, F.DIST returns the height of the F-distribution at your value (x).  Unless you are considering creating a chart, you probably (get it?) won’t use FALSE as the value for cumulative.

F.DIST.RT returns the probability of obtaining an F-ratio at least as high as yours if the null hypothesis were TRUE (Excel calls this the “right-tail” probability).  If that value is less than alpha, you should reject H_0.  In practice, F.DIST.RT is more straightforward and replaces FDIST.

Now this is important: This F.DIST.RT function replaces the older function FDIST 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.

The F.DIST.RT function employs the following syntax to operate:

The F.DIST function has the following arguments:

• x: this is required and represents the value at which to evaluate the function
• 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, F.DIST.RT returns the #VALUE! error value
• if x < 0, F.DIST.RT 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, F.DIST returns the #NUM! error value
• F.DIST.RT is calculated as F.DIST.RT = P(F > x), where F is a random variable that has an F-distribution with deg_freedom1 and deg_freedom2 degrees of freedom.

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.