Excel Exercise #3
In this exercise, you will use Excel to create bar charts with various types of error bars.
- Step 1 - Do together Use Excel to generate a set of 25 random values. Label them at the top as "data." As in Exercise 2, 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. Now, create a second set of 25 values labeled "data 2" where the mean is 10 and the standard deviation is 10. DO NOT use the technique from exercise 1 of copy-pasting the data you create so it will not change every time you modify the worksheet (you'll see why in a second).
- Step 2 - Do together Compute some "descriptive statistics" of the data including the mean, standard deviation, and the standard error
- Step 3 - Do together Using the Excel chart wizard make a column chart where the height of the bar is equal to the mean of each column of our data set. You do this by selecting the two mean values and then clicking "column chart" under the "charts menu". Add error bars by selecting the "format data series" option and editing the error bars. Make the error bars be equal to the standard deviation of each column.
- Step 4 - Repeat this but using the standard error as the error bars
- Step 5 - Repeat this but using the 95% confidence interval as the error bars
- Step 6 - Compute a paired t-test Following the technique from Exercise 2, do a t-test on the data in the two columns (two sample, paired). Are the results significant? Which type of error bars did you use?
- Step 7 - Adjust the mean of group 2 so that it is closer to the mean of group 1 As the data gets close the value of the t-test should indicate a higher p-value and a lower t-value. What happens to the error bars? Does the overlap in the bars ever contradict the outcome of the t-test?