A Gentle Introduction to Excel



In this exercise, you will use Excel to generate a description of some data.

  1. Step 1 - Do Together
    Use Excel to generate a set of 25 random values. Label them at the top as "Data"
  2. 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)
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. Step 6 - do together
    Compute the following statistical descriptions of the data: mean, median, std. deviation.
  8. 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).
  9. Step 8 - on your own
    repeat this on the second column
  10. 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 todd.gureckis@nyu.edu. Please remember to include the letter 'lhc' in the subject line. Please complete before the next class.