Excel Exercise #1



In this exercise, you will use Excel to generate a description of some data.
  1. Step 1 - Do together
  2. Use Excel to generate a set of 25 random values. Label them at the top as "data." To generate a random number type
    =RAND()
    in the cell. This generates a random number between 0 and 1. You can fill this formulate down 25 cells to create 25 random numbers. Finally select all the numbers, choose "copy" then "paste special" and select "paste as values". This will prevent the random numbers from re-generating every time you change the spreadsheet.
  3. Step 2 - Do together
  4. Compute a full set of "descriptive statistics" of the data including the mean, median, sum of squared deviations, standard deviation, variance, max, min.

    You should do this two ways, first by hand computing the sums and deviations. Second, use Excel's built-in functions. Make sure the values by hand match the values computed by Excel. Here is a cheat-sheet of Excel statistics functions:

    statistic excel command
    mean
    =AVERAGE(data)
    median
    =MEDIAN(data)
    standard deviation
    =STDEV(data)
    quartile
    =QUARTILE(data, Q)
    where Q=1,2,3,4
    max
    =MAX(data)
    min
    =MIN(data)


  5. Step 3 - On your own
  6. 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.
  7. Step 4 - On your own
  8. 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.
  9. Bonus
  10. Generate a new set of Data (3). However, make it so that the mean of the data is exactly 10. You can not make all the numbers in the dataset 10, instead you must apply some transform to each number in the list so that the mean of all the numbers becomes 10. Recompute all the standard measures on it. Describe how you did this.