Excel TDIST

For common statistics, such as the Student s t value, chi-square, and Fisher F, Excel has functions that return the critical value at a given probability and degrees of freedom (e.g., =TINV (0.05,10) for the two-tailed Lvalue at a probability of 95% and 10 degrees of freedom), or which accept a calculated statistic and give the associated probability (e.g., =TDIST( t, 10, 2 ) for 10 degrees of freedom and two tails). Table 2.3 gives common statistics calculated in the course of laboratory quality control. 

There are three ways to perform a t test in Excel. First, a t value can be calculated from the appropriate equation and then the probability calculated from =TDIST (t, df, tails ), where df = degrees of freedom and tails = 2 or... 

The probability of a difference this large occurring because of only random errors can be obtained from the Excel function TDIST(x,deg freedom,tails), where x is the test value of /(4.375), deg freedom is 3 for our case, and tails = 2. The result is TDIST(4.375,3,2) = 0.022. Hence, it is only 2.2% probable to get a value this large because of random error. The critical value of t for a given confidence level can be obtained in Excel from TINV(probability,deg freedom). In our case, TINV(0.05,3) = 3.1825. 

The critical value of t at the 95% confidence level for 10 — 2 = 8 degrees of freedom is 2.31. Since 1.771 < 2.31, we accept the null hypothesis at the 95% confidence level and conclude that there is no difference in the alcohol content of the wines. The probability of getting a t value of 1.771 can be calculated using the Excel function TD1ST() and is TDIST( 1.771,8,2) = 0.11. Thus, there is more than a 10% chance that we could get a value this large due to random error. 

Spreadsheet Summary In the first exercise in Chapter 3 of Applications of Microsoft Excel in Analytical Chemistry, we use Excel to perform the t test for comparing two means assuming equal variances of the two data sets. We first manually calculate the value of t and compare it with the critical value obtained from Excel s function TINV(). We obtain the probability from Excel s TDIST() function. Then, we use Excel s built-in function TTEST() for the same test. Finally, we employ Excel s Analysis ToolPak to automate the t test with equal variances. 

This can simply be done in Excel using the function TDIST with syntax TDIST(f, df, tails). Therefore in a blank cell of a spreadsheet you would input =TDIST(2.23, 10, 2). 

---