
Part 4. One-Sample t-Test Using Excel
1. Setting Up Excel 2. Entering Data 3. Calculating Descriptive Statistics 5. Pearson's Correlation Coefficient
	I.	Darin wants to determine at the .05 level of significance whether the average number of sick days taken by workers who did not graduate from high school increased from the 8 day average of last year. A simple random sample was drawn from a normally distributed population with an unknown standard deviation. Nothing happened during the year to cause Darin to feel the observations were not independent of each other.
		A.	Excel does not have a specific test entitled one-sample t-test. We will use Excel to do the mathematical calculations. Open the Excel book entitled 1 and 2 sample t-tests. Use Employee Performance Analysis.
		B.	Make an alphabetical array by placing the cursor into cell C1, the Education label, and click on the A/Z down arrow icon next to the f_x icon in the Standard Toolbar (row 3).
		C.	Resize column G to 25 using Format, Column, and Width. Beginning with G2, enter these labels: sample mean, hypothesized population mean, sample standard deviation, count, standard error of the mean, t from the data, alpha, df, probability for a one-tail test, t critical for a one-tail test, probability for a two-tail test, and t critical for a two-tail test.
		D.	Using column G, enter 8 for the hypothesized population mean and .05 for alpha.
		E.	Conducting a one-tail hypothesis test
			1.	Insert the cursor into cell H2. Choose f_{x ,}Statistical, AVERAGE, and OK. If necessary, next to number 1 put D2:A13 and choose OK.
			2.	Insert the cursor into cell H4. Choose f_{x ,}Statistical, STDEV, and OK. If necessary, next to number 1 put D2:A13 and choose OK.
			3.	Insert the cursor into cell H5. Choose f_{x ,}Statistical, COUNT, and OK. Next to number 1 put A2:A13 and choose OK. Next to the location of your answer put column D next to count.
			4.	Place the cursor next to standard error of the mean (H6) and insert a formula that divides the sample standard deviation by the square root of the count. My formula was H4/SQRT/(H5).
			5.	Place the cursor next to df and insert =H5-1.
			6.	Place the cursor next to t from the data (H7) and insert the formula (sample mean- hypothesized mean)/standard error of the mean. My formula was =(H2-H3)/H6.
			7.	We will now calculate p, the probability for the right tale of the data. If it is less than the level of significance, we will reject the null hypothesis. Why? If the sample mean of 10 is so far above the hypothesized mean of 8 that a number this large or larger happens less than 5% of the time, we will conclude Sick Days went up. Note we will not know Sick Days went up unless we take a census and that takes too much time and money.
				a)	Place the cursor next to probability for a one-tail test and choose f_{x ,}Statistical, TDIST, and OK.
				b)	Next to x (or x f_x) place the formula =ABS(H7) where H7 is the location of t from the data.
				c)	Enter 11 for the degrees of freedom and 1 because this is a one-tail problem.
				d)	The answer .000236193 is substantially less than .05 and the null hypothesis is rejected. Non-graduates took more days off! Reasoning, the difference between last year and our sample was so great that it happens less than 5% of the time.
			8)	Using the critical value approach to hypothesis will require determining the critical value for a one-tail alpha of .05.
				a)	Locate the cursor in H11and choose f_{x ,}Statistical, TINV, and OK.
				b)	TINV is set up for a two-tail problem so enter .10 for probability and 11 for degrees of freedom.
				c)	The answer, called the critical value for t, of 1.795883691is the maximum allowable t for the .05 level of significance. Our data has a t value of 4.898979486 and the null hypothesis is again rejected. Non-graduates took more days off! Reasoning, the difference between last year and our sample was so great that t from the data was substantially larger than the t associated with a 5% level of significance. If our decision to reject is wrong, it will be so less than 5% of the time!
		F.	Conducting a two-tail hypothesis test
			1)	Direction 7c of the one-tail test will require inserting a 2 for 2 tails.
			2)	Direction 8b will require entering the actual alpha of .05.

Proceed to 5. Pearson's Correlation Coefficient

Related Free Stuff
Our free Excel Statistics Lab Manual
loads into spreadsheets which contains
directions, problems with data set, and answers
Use Our Free Quick Notes Statistic Using Microsoft Excel Course Materials
Free Statistics Books

Research Paper Internet Library

Statistics Software Tutorials

Visit Free Internet Libraries to improve knowledge, grades and careers.