Excel Exercise #2



In this exercise, you will use Excel to perform some inferential statistics.
  1. Step 1 - Do together
  2. Use Excel to generate a set of 25 random values. Label them at the top as "data." Unlike in Exercise 1, we want to generate data where some values are more likely than others. We will do this by generating numbers from a Gaussian distribution with mean equal to 0.0 and standard deviation equal to 10 (see Lecture 4).

    An example Gaussian distribution

    In Excel you can easily do this by replacing our previous command
    =RAND()
    we want to use
    =NORMINV(RAND(), 0, 10)
    Now, create a second set of 25 values labeled "data 2" where the mean is 10 and the standard deviation is 10. Again, you can use the technique from exercise 1 of copy-pasting the data you create so it will not change every time you modify the worksheet.
  3. Step 2 - Do together
  4. Compute some "descriptive statistics" of the data including the mean, median, standard deviation.
  5. Step 3 - Do together
  6. For our first data column, we want to test the null hypothesis that the true mean is zero. This will be a one sample t-test. To do this, we first need to convert the mean we computed in step 2 into a t-value. Remember the equation for the t-value is
    $$t = \frac{\mu - H_{0}}{s/\sqrt{N}}$$
    where $\mu$ is the mean of our sample, $H_{0}$ the value of the mean expected under the "null hypothesis", $s$ is the standard deviation of our sample, and $N$ is the number of observations or data points we have in the group. You have all these numbers readily available now and it is just a matter of plugging the numbers in. Once we have our t-value, we can convert this to a p-value (assign a probability to an event that extreme or more extreme) using
    =TDIST(t-value, degrees of freedom, tails)
    Here, t-value is obviously what we just computed in the previous computation. Degrees of freedom is $N-1$ and the number of tails should be 2 (we want the two-tailed test... for a one-tailed test you put 1 here). Why do we need to tell it the degrees of freedom?
  7. Step 4 - Repeat this on the second column of data
  8. Step 5 - Compute a paired t-test
  9. To compute the paired t-test we want to do a t-test (as before) but on the difference scores between the two conditions. To do this, we first compute the difference between the first data column and the second column (label this new column "differences"). Then we repeat the above calculations specifically using the null hypothesis of zero (in a paired t-test this is ALWAYS the null hypothesis because the difference in the means is expected to be zero if there is no difference between the groups).