How To Create A 95 Confidence Interval In Excel

Create a Confidence interval with Excel

1. Fetch the galo data back again (for the boys and girls IQ) found here:

http://media.pearsoncmg.com/aw/aw_weiss_elemstats_6/projects/galo.html

Remember…scroll down and click on Excel…While you are there, be sure to read up what each column of data stands for. If you are observant, you will see that they have made a mistake, which we will ignore. We will simply pretend that the Father's professions range from 1-7 and the teacher's advice ranges from 1-6.

2. We will do our computations on sheet 2. First we will sort out the IQ by boys and girls and save it on sheet 2. Then we will compute a confidence interval for the true population mean for boys and then again for girls. We will see if they are the same or not. Do you suppose one group is smarter? We will use the z-chart because it is easier to do in Excel. We will use s in place of sigma, which we don't know, although it is supposed to be 16 for an IQ test, but I'm not sure I believe them. We will go with a 95% confidence interval. Our sample sizes are large enough to use s in place of sigma.

3. Go to sheet 2 and type in the following stuff. Notice I stretched out column E by grabbing the vertical line between the E and the F:

Now, return to sheet1 and click on the grey box in the upper left corner between the A and the 1. This will highlight the data in one easy step. Or, use your mouse to highlight all the data. Now we will sort the data so that the boys are first and the girls are underneath. Go to the Data menu and choose sort. In the first dropdown box, choose to sort it by gender and sort it ascending by picking that circle. Click OK. Now all the boys are first and the girls are down at the bottom. Use your mouse to highlight all the IQs for the boys. Scroll down to get them all. Press and hold control (ctrl) and then press C. The data is copied. Go to sheet2 and paste (press and hold control and then press V) it in under the title Boys IQ. Go back to sheet1 and highlight all the IQs for girls. Copy and paste this list to sheet2 under the title Girls IQ.

4. Now we will use Excel to fill in the blanks in column E on sheet2. Here is what to type into each blank. You will get numbers, however, after you press enter for each. As a check, if you have done it right, cell E8 will read 106.2834646. Print out your finished table:

5. Now you get to do it again without directions, but for a different scenario. Do your new computations on sheet3, so you have a clean place to work. I want you to sort out the IQs of the children who had a father with profession 2 and the IQs of the children who had a father with profession 7. So sort all the data on sheet1 by profession and cut and paste the IQs like before. Then make a confidence interval for the profession2 kids and a confidence interval for the profession7 kids. We are going to see if kids with fathers who made lots of money (the profession7 kids) tend to have a higher IQ. In other words, does your income bracket affect your IQ as tested on those standardized tests? As a check, if you do it correctly, the average for the profession2 kid's IQs will be 87.67702. Note, you don't really have to retype all the formulas in the picture above. You can cut and paste it into the same spot in sheet3. Very fast. Please print out the chart you create. Just be sure to rename boys/girls to the profession whose data you put there.

Questions to Answer:

1. Who had the higher average IQ, the boys or the girls?

2. Who had the higher average IQ, the kids with profession7 dads or the kids with profession2 dads?

3. What was your final confidence interval for the girls IQ?

4. Does it overlap the confidence interval for the boys IQ (yes or no)?

5. A value of 3 under "teacher's advice" stands for what, exactly?

6. How many schools were represented in the study (that we know of)?

7. Explain the significance of your answer to question 4 (the one about overlapping CI). Explain what it means in terms of IQs for the kids.

Hand in:

answers to the questions

chart of boys/girls IQ

chart of the profession2/profession7 IQ

stapled together