PSYC2130-Computer Lab 2-Summation PDF

Preview

Summary

Shows how to do excel problem...

Description

PSYC2130

A.A.Lazarte

1

Computer Lab 2: Summation in Excel. In data analysis, individual data values, or functions of the individual data values, are added to produce basic quantities for further computations. The summation symbol sigma, Σ, represents the addition process. For example, the expression N

∑X

i

represents the summation of all the values (from 1 to N) of the variable X.

i= 1

It is very easy to obtain sum of values in Excel. For example, the first worksheet in the file PSYC2130-FOR EXCEL LAB2-SUMMATION.XLSX we have five values for the variables X and Y (column A contains the id for each value). Then, to obtain the value of the following summation expressions we can do the following: 5

5

∑Y

∑ Xi = 7 i= 1

i

=9

i= 1

In cell B7 enter =SUM(B2:B6)

In cell C7 enter =SUM(C2:C6)

Functions of individual values can take different forms. For example: 5

∑X

(1) The sum of the squared values of X:

2 i

i= 1

5

(2) The sum of four times the values of Y plus two:

∑( 4Y + 2) . i

i= 1

5

(3) The sum of the products XY:

∑XY

i i

i= 1

In the second worksheet in the file, we obtain the values for three functions. For the sum of squared values, we first create a new column in D, labeled X2, where the first element (in cell D2) is =B2^2. Then, we drag down the formula to fill the other cells (up to D6). Finally, we put the sum in cell D7, i.e. SUM(D2:D6). To find the value of the second expression, we create a new column in E where each value of Y is multiplied by 4 and add 2 to the resulting product. For the first value, we put in cell E2 the expression =4*C2+2. Then we drag the value to fill the other cells. Finally, we obtain the sum in Cell E7, i.e. =SUM(E2:E7). For the third expression, we enter in column F the products of X and Y, i.e. in cell F2 we enter =B2*C2. Then drag the result to copy the function to the remaining rows. Finally put in cell F7 the sum of the values, i.e. SUM(F2:F7). Thus, the resulting values are: 5

∑ X 2i = 15 i= 1

5

∑( 4Yi + 2) = 46 i= 1

5

∑ X Y = 14 i i

i= 1

PSYC2130

A.A.Lazarte

2

Deviation and Standard Scores Two of the most common transformations of individual scores in data analysis are deviation scores and standardized scores: Deviation score: we subtract the data average (represented by X-bar) from each of the individual observations.

X i− X Standard scores: we subtract the data average from each of the observation and divide the result by the standard deviation of the data (represented by the letter S)

Xi − X S In the third worksheet in the excel file, we copy only the data for the variable X. We will first compute the Mean and the Standard Deviation (SD) or the original or “raw” scores. The mean of X in cell B7 is obtained by entering =AVERAGE(B2:B6). The Standard Deviation of X in B8 is obtained entering =STDEV.S(B2:B6) In column C, labeled “Dev. Score”, we create the deviation scores by subtracting the average in B7 from each value of X. Thus, in cell C2 we enter the first subtraction, i.e. =B2-$B$7. Notice that we use an absolute reference for the cell with the mean value. Once we enter this first cell, we drag the function to the remaining rows. In column D, labeled “Stnd. Score”, we enter the standard scores. First, we subtract the average in B7 from each value, and then we divide the difference by the standard deviation in cell B8. Thus, in cell D2 we enter the formula = (B2$B$7)/$B$8. Again, we use an absolute reference for the cell with the mean and the standard deviation. Once we enter this first cell, we drag the function to the remaining ones. Of course, we can obtain the mean and standard deviation of the new columns. For the deviation scores we enter in C7 the function =AVERAGE(C2:C6) and in C8 the function =STDEV.S(C2:C6). For the standard scores we enter in cell D7 the function =AVERAGE(D2:D6) and in cell D8 the function =STDEV.S(D2:D6) Notice that the mean of the deviation scores is now 0, but their standard deviation is the same as the original values. The standard scores, on the other hand, has mean 0 but their standard deviation is now 1. More than one way to obtain the same sum A summation expression describe the way to compute a sum. Sometimes there is more than one way to perform the same computation. For example, we can show that for the same variable X: N

(a)

∑( X

i

i= 1

− X)

 n  (b) ∑ X −  ∑ X i   i=1  i= 1 n

2

and

2 i

2

N

expressions (a) and (b) produce the same result. Expression (a) says to subtract the average from each one of the observations in column X, square the differences and add all the results. Expression (b) says to obtain first the sum of X, and the sum of X2 ; afterward square the sum of X, divide the result by the number of values N, and finally subtract the results from the sum of X squares. In the fourth worksheet, we perform these operations. In column B we have the sum, mean, and count for X. In column C we square each values of X and find the sum of the values. In the cell D2 we enter the function =(B2-$B$8)^2 and drag the function to the rest of the rows and fin the sum of the values. Expression (a) result in cell D7 is 5.2. For expression (b) we compute in cell C11 the expression = C7-(B7^2)/B9 and we obtain the same value 5.2. Thus: N

∑( X i= 1

− X ) = 5.2 2

i

 n  ∑ X −  ∑ X i  i= 1 i= 1 n

2 i

2

N = 15 − ( 7 ) 5 = 5.2 2

PSYC2130

A.A.Lazarte

3

Example The number of hours (variable X) that twenty students slept the previous night are given below. 8 8 7 7 8 6 8 8 9 9 5.5 6 9 7 8 5 7 7 9 9 These values will be in column A, from A2 to A21, in the fifth worksheet of the file.

2.

N

Xi i 1

N

4

2

,o i 1

5. Find the mean of X, or X In cell E5 enter: =AVERAGE(A2:A21) and in cell D5 enter as label “Mean” N

6. Find the sum of squares SS X =

∑( X

i

− X)

2

i =1

In cell C2 enter =(A2-$E$5)^2, and drag the formula on the remaining cells. Enter “(X-Xbar)2” as header in C1. In cell E6 enter =SUM(C2: C21), and in cell D6 enter as label “SSx” 7. Find the variance S X2 =

SS X N −1

In cell E7 enter =E6/(E2-1) and enter in D7 as label “(Sx)2” 8. Find the standard deviation S X =

S 2X

In cell E8 enter =SQRT(E6) and in cell D8 enter as label “Sx” The final spreadsheet, after all the computations is in the sixth worksheet of the lab file....