You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.
Investigate the difference between normal and uniform distributions. Also, make use of the frequency function.
Review the statistical functions in Chapter 3, pages 102-103. (You do not have to get involved in the mortgage exercise at this time.) Review the highlighted boxes on pages 86, 91, 95-98, 103, 113-115, 117-118 and autofill on pages 109-111. Review class notes about statistics.
1. Download the file "lab8.xls" from the class's home pages ( www://www.cs.plu.edu/pub/faculty/csce115/lab8.xls). The spreadsheet on the "Required" page has two columns of data; one set in column A and another in column F. In this lab you will be analyzing these sets of data.
2. Load "lab8.xls" into Excel and do an immediate SaveAs with another name such as "mylab8.xls". (Now if you mess things up badly, "lab8.xls" will still have the raw data which you can use to start over, if needed.)
3. In columns C and D beginning at row 4 create and label a block of statistical information for the data in column A. Include the Maximum, Minimum, Range, Mean, Median, and Standard Deviation. Put a border around these statistics to make them stand out.
4. You are now going to create a frequency table for the data in column A which will look like the table shown below although the "Count" column will have different values. Skip down to row 13 in column C and put in the label Data; next to it in column D put the label Count. Enter the numbers 250 and 300 in C15 and C16 and use Auto fill to generate the remaining numbers in rows 17-26. Put a border around the frequency table.
Data Count
250 0
300 3
350 6
400 12
450 14
500 18
550 20
600 11
650 9
700 5
750 1
800 0
5. You are now ready to use the FREQUENCY function. This is a very special kind of function which produces a whole array of values at once. You will be putting this array of values in column D beginning at cell D15. Carefully select D15 through D26. (Be sure this range is highlighted.) Now type =Frequency(the range of values in column A you are analyzing, C15:C26). (The second parameter range is the set of dividing numbers in column C. Important: Instead of pressing <enter> do a <ctrl><shift><enter>. This should put curly-braces around the formula and copy it into all the highlighted cells. The values in the cells are as follows: the top cell is the number of values less than or equal to 250. The number next to 300 is the number of values greater than 250 but less than or equal to 300. The value next to 500 is the number of values greater than 450 and less than or equal to 500, etc.
Comment: If you make a mistake creating the frequency functions or need to change them, it is normally easiest to delete all of them at the same time (select all of them and press the delete key) and start over.
6. Now copy the set of data and header from column A into column B. Sort the data in column B by selecting the data range and then clicking on the Sort tool. (It has an A over Z with a down arrow.) Notice that columns A and B have the same data in them but now B's data is in ascending order. (You can now easily hand check the frequency table to see that it is correct.)
7. Repeat the whole analysis that you did on column A on the data F. Use the columns to the right of F in the same way you did for columns right of A. (Hint: copy your statistical analysis but not the data.)
8. Now modify the top heading in the left corner. Insert new rows as needed in order to make room for the heading. Add your name, the lab number, the class, today's date (as a constant) and also using the today() function. Your heading should look something like:
your name
Lab Exercise 8
Computer Science 115, Spring 2000
Begun: Today's date as a constant
Finished: =Today()
Do whatever other formatting you want to make it look nice. Perhaps your name in bold and a larger font size hold be appropriate but use your own ideas.
9. Now make two charts showing the information in the frequency tables in order to construct histograms for of them. (Hint: For the first chart, select the data range C14:D26 before picking the Chart Wizard.)
10. Look at the chart(s) and the frequency tables. One of the data sets was generated using uniformly distributed numbers, the other column displays normally distributed values. Can you guess which is which? Why? Using a computer word processor (such as Microsoft Word) write (at least one decent size paragraph) about the results of your analysis to explain your choices.
11. Print enough of the spreadsheet to show all your original work (the data analysis, counts, your name, and the two charts). You do not have to show all the supplied data. Also print the formulas you generated for the data analysis and counts. The formula sheet does not need to include all the data or even the charts. It should include your name.
Turn in the part of your final spreadsheet that shows your original work. Also print your formulas. You do not have to print all the data. Also turn in your word processed paragraph explaining your choices about which column of data is normally distributed and which is uniformly distributed. If you did the option, turn in the parts of spreadsheet showing your work, your choices, and your explanation of your choices. Label each page you turn in.
Lab08.html,3/30/2000