CSci 105

Introduction to Computers

Laboratory Assignment # 5


Intermediate Spreadsheets

1. Assume your friend in the physics lab has measured four sets of X-Y data listed in Table 1.3 and has asked you, as an Excel spreadsheet expert, to do a statistical analysis of the data. Key in the four sets of data in columns B Æ L and, below the data, compute the following statistical properties of each of the 8 columns:

Quantity                           Excel Function Call
Number of data items                =count(B4:B14)
Largest value in list               =max(B4:B14)
Smallest value in list              =min(B4:B14)
Median (middle value)               =median(B4:B14)
(§) Average of items in column      =average(B4:B14)
(§) Standard deviation(§)           =stdev(B4:B14)

Based on the (§) statistical tests, how would you advise your physics friend as to whether these four sets of X-Y measurements are or are not drawn from the same parent population (i.e., are they four different measurements of the "same thing" or measurements of four different things based on these statistics)?
 

2. Next, do a graphical analysis of the data by plotting Y vs. X for all four sets of data.  Based on similarities or differences in the shapes of these curves, re-evaluate your advice from Problem 1.  That is, do the shapes of the curves suggest that all four curves measure the "same thing" or different things?
 


Table 1.3

Four Sets of  X-Y Measurements
X1
Y1 
 
X2 
Y2
 
X3
Y3 
 
X4 
Y4
10.00 8.04  10.00  9.14 10.00 7.46  8.00 6.58
8.00 6.95  8.00 8.14 8.00  6.77 8.00 5.76 
13.00 7.58  13.00  8.74 13.00 12.74  8.00 7.71
9.00 8.81  9.00 8.77 9.00  7.11 8.00 8.84 
11.00 8.33  11.00  9.26 11.00 7.81  8.00 8.47
14.00 9.96  14.00  8.10 14.00 8.84  8.00 7.04
6.00 7.24  6.00 6.13 6.00  6.08 8.00 5.25 
4.00 4.26  4.00 3.10 4.00  5.39 19.00 12.50 
12.00 10.84  12.00  9.13 12.00 8.15  8.00 5.56
7.00 4.82  7.00 7.26 7.00  6.42 8.00 7.91 
5.00 5.68  5.00 4.74 5.00  5.73 8.00 6.89