Free Microsoft Excel instructions for calculating statistics with Excel followed by doing a one-sample t-test with Excel.

3. Calculating Descriptive Statistics 1. Research Project Startup Directions 2. Entering Data 4. One-Sample t-Test 5. Pearson's Correlation Coefficient
	I.	Creating data files for later use
		A.	A number of different data files (called books by Excel) will be used to store research results.
		B.	Open Excel file Employee Performance Analysis from part II
		C.	Choose File from the Menu Bar (row 2), choose Save As, enter Descriptive Statistics in the File name box at the bottom of the screen, and choose save.
		D.	Repeat direction C using the file name one- sample t-tests.
		E.	Repeat direction C using the file name Pearson's Correlation Coefficient.
	II.	Array
		A.	Load the Descriptive Statistics data file. We are going to make an array based on Sick Days using sheet 1.
		B.	Copy all variables to sheet 2 for later use by highlighting them. From the Menu Bar (row 2) choose Edit, Copy, Sheet 2, Edit, and Paste. Click on sheet 1. This is not required if you have a data disk.
		C.	To make a low to high array, highlight cells D2 to D24 and click on the A/Z down arrow icon next to the f_x icon in the Standard Toolbar (row 3). Notice how the other variables also move to stay with the appropriate Sick Day. A/Z up arrow will make a high to low array.
		D.	Excel will sort alpha variables like gender and names alphabetically keeping related variable like Education and Performance Rating in the in the appropriate location (same row). Excel uses the first letter of a cell to alphabetize so names must be recorded last name first.
		E.	Using Undo, located in Edit of the Menu Bar (row 2), before executing another command will return data to the original order. We will leave this data as reordered.
	III.	Making a Frequency Distribution and Histogram for Sick Days
		A.	Upper class limits are called BINS
		B.	Type BINS in cell G1 of Sheet 1 of Descriptive Statistics.
		C.	Type the upper class limit of each class in the succeeding rows. I typed 4,8,12, and 16 as upper limits for Sick Days.
		D.	In the Menu Bar (row 2), choose Tools and Data Analysis. If Data Analysis is not there, click on Add-In under Tools and check Analysis ToolPak, check Analysis ToolPak-VBA and choose return. Choose Tools and Data Analysis.
		E.	Double click on Histogram.
			1.	Enter the input range for Sick Days (I entered $D$2:$D$24) and the Bin range (I entered $G$2:$G$5), do not check Labels, enter where you want the results to be stored (I chose Output Range and entered $I$2), choose Cumulative Percentage, Chart Output, and return. A frequency distribution (by count and cumulative percentages), histogram, and Ogive will appear.
			2.	The process of entering the input range and Bin range varies with each version of Excel. With Version 8 provided with Office 97, put the cursor in the input range box, use the red down arrow to access the data to be entered, highlight said data, and hit return. The appropriate range will appear in the input range box. Version 7 does not contain a red reduction arrow and requires you drag the data into the input range, bin, and output boxes.
			3.	You can change the x-axis label of Bin in the Histogram by clicking on it and typing your variable name in the space provided. I typed Sick Days. You can stretch the Histogram vertically to make it look better by clicking on the handles and pulling down.
			4.	Make a frequency distribution and histogram for Performance Rating.

IV.	Making a Pivot Table (optional for Franklin Pierce College students)
	A.	Pivot Tables are used to compare many quantitative and qualitative measures of a database.
	B.	Variable can be measured for their sum, count, average, maximum, minimum, product, count numbers, sample standard deviation, population standard deviation, sample variance, and population variance easily with a Pivot Table.
	C.	You are interested in comparing Education, Gender, and Performance Rating using a Pivot Table.
	D.	Open Pivot Table by clicking on Data and Pivot Table Report.
	E.	Highlight all the columns including labels. From the Menu Bar (row 2) choose Data, Pivot Table Report, leave the default Microsoft Excel list or database, and choose next twice.
	F.	Drag Education into ROW, Gender into COLUMN, and Performance Rating into DATA. Choose Next, choose Existing worksheet, locate the table in $G$1, and choose Finish.
	G.	We are not interested in a sum of Performance Rating. Double click on Sum Performance Rating, choose Average, OK, Next, and Finish. Study the result. Double click on Average and use the scroll bar to see the different calculations you can do. Note you can change the name of a measure. I changed average to mean. Click on Options and Normal to see other Pivot Table tools. Drag another Performance Rating into DATA, double click on it, and choose max. Repeat choosing other statistics.
	H.	Enter other variables into ROW, COLUMN, DATA, and PAGE and study the result.
V.	Calculating basic descriptive statistics
	A.	Open Descriptive Statistics. We are going to analyze the sick days and performance ratings for graduates using sheet 2. Click on Sheet 2.
	B.	Place the cursor in the Education label and use A/Z done arrow from the Standard Toolbar (row 3) to make an array. Notice how Graduates listed first.
	C.	From the Menu Bar, choose Tools, Data Analysis, Descriptive Statistics, and OK.
	D.	Enter columns D and E using only graduates and including the labels for the Input Range. With Version 8, you can use the red down arrow to reduce the screen, highlight the location of all three variables, and choose Enter.
	E.	Leave the default Grouped by columns and check Labels in First Row.
	F.	Use $G$1 as the Output location. With Version 8, you can click on the red down arrow to reduce the screen, highlight the location, and choose Enter.
	G.	Check Summary Statistics, check Confidence Level for the mean, enter 8 for Kth largest and 2 for Kth smallest, and choose OK.
	H.	Resize columns using Format (row 2), Column, and Width. Use the Fast Format icon (it looks like a few zeros with decimals) of the Formatting Tool Bar (row 4). They are right below the A/Z down and up arrow icons of the Standard Toolbar (row 3). Use the icon on the right to lower number of decimal places to 3.
	I.	Repeat Part IV for nongraduates and compare the data.
VI.	Using the Paste Function f_x (called the Function Wizard by Version 7) to calculate descriptive statistics. (optional for Franklin Pierce College students)
	A.	Paste Function is useful as it gives you the option of calculation population parameters or sample statistics and it handles missing distribution values well.
	B.	Place the cursor below Performance Rating in location E26. From the Standard Toolbar (row 3) choose f_x, Statistical, and Average, enter the data range E2:E12 for the Performance Rating of Graduates, and choose OK (or Finish). Note the answer is the same as the one calculated earlier once the number of decimals has been increased to 3.
	C.	Spend some time looking at the Statistical and other functions you can calculate using f_x, the Paste Function of Excel.

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.