A Gentle Introduction to Excel
In this exercise, you will use Excel to generate a description of some data.
- Step 1 - Do Together
Use Excel to generate a set of 25 random values. Label them at the top as "Data"
- Step 2 - Do Together
Your goal is to compute the following "descriptive statistics" about the data: mean, median, sum of squared deviations, standard deviation, variance, maximum value, minimum value. You should do this two ways. First, by hand computing sums and deviations. Second using Excel's built in functions. Make sure the values computed each way match. For help, here is a cheat-sheet of excel functions.
Function Excel Function Name mean =AVERAGE(data) median =MEDIAN(data) std. dev. =STDEV(data) quartile =QUARTILE(data,Q), where Q=1,2,3,4 maximum =MAX(data) minimum =MIN(data)
- Step 3 - on your own
Create a second set of data (labeled Data 2), which is a copy of Data above but + 10 to each value. Which values will change and which wonʼt? Recompute the descriptive statistics by hand and using excelʼs builtin functions. Make sure the values match. Were you right? Which were wrong? Think about why.
- Step 4 - on your own
Create a fourth set of data (labeled Data 2), which is a copy of Data above but times 4 to each value. Which values will change and which wonʼt? Is is different from step 3? Recompute the descriptive statistics by hand and using excelʼs built-in functions. Make sure the values match. Were you right? Which were wrong? Think about why.
- Bonus - on your own
Generate a new set of Data (3). However make it so that the mean of the data is 10. Recompute all the standard measures on it. Describe how you did this.
- Step 5 - do together
Use Excel to generate a set of 25 random values. Label them at the top as “Data 1”. Make the mean=0 and std. dev. = 10. Remember you can do this with =NORMINV(DATA(), 0, 10). Create a second set of 25 values labeled “Data 2” make the mean=10 and std. dev.=10.
- Step 6 - do together
Compute the following statistical descriptions of the data: mean, median, std. deviation.
- Step 7 - together
For each column, we want to test the null hypothesis that the true mean is zero. To do this in excel, we first need to convert the mean we computed for each column into a mean. Remember the equation for the t-value is:
t = (mean - null hypothesis mean) / (std. dev. / sqrt(N))where our null hypotheses is that the true mean is zero. Once we have our t-value you can convert this to a probability using
= TDIST(t-value, degrees of freedom, tails)where degrees of freedom is N-1 and tails = 2 (we want the two tailed test).
- Step 8 - on your own
repeat this on the second column
- Step 9 - compute a paired t-test
To compute the paired t-test we want to do a t-test (as before) but on the difference scores between the two condition. To do this, we first compute the difference from column 1 to column 2 as a new data set (labeled Data 3). Then we repeat the mean/std. dev./t- calculations again using the null hypothesis of zero (that the true difference is zero).
Please answer the following questions and email the results to firstname.lastname@example.org. Please remember to include the letter 'lhc' in the subject line. Please complete before the next class.