  # Tutorial for chapter4 lab ex

-

Documents
4 pages
Le téléchargement nécessite un accès à la bibliothèque YouScribe
Tout savoir sur nos offres

Description

Sujets

##### Sciences formelles

Informations

 Publié par Nombre de lectures 13 Langue English
Signaler un problème
Sun-Mee Kang
PSY 420
Tutorial for the Excel program
1/4
Tutorial for How to use the EXCEL program
The purpose of this exercise is to learn how to use the EXCEL spreadsheet program to compute the
following: means, sums of squares (SS), standard deviation (SD), and variances. Before you proceed, you
should re-read the questions in the handout.
Some things to keep in mind about EXCEL.
EXCEL is a versatile program, there are many different ways to get a desired result.
It is a very good idea to label and organize everything.
Remember to press ENTER after you finish typing in a cell, press ESC to cancel the current operation.
Getting started
course web page (
www.csun.edu/~skang/psy420.html
)
and click on the hyperlink labeled “
Chap4 lab data
.
Once opened, it should look similar to the picture on the
right.
Please note that the “height” and “weight” cells have
been re-labeled to “height(X)” and “weight(Y)”.
How to compute average (mean) of height and weight.
(Question 1. a.)
1.
DOUBLE CLICK onto cell B17, which is under
the “height” column.
2.
Type in the following: =AVERAGE(B2:B16) ,
then press ENTER. Now, a mean of 165.533
should be showing in the same cell. Note the way
that the range of cells is defined between the
parentheses.
3.
Next, there are two options for computing the
mean of the weight, you can either repeat steps 1
and 2 for the weight column OR you can use a
‘smart’ feature in the EXCEL program.
To use
the smart feature:
a.
LEFT CLICK the cell containing the
formula that you want to duplicate (B17 in
this case).
b.
Position the mouse cursor over the bottom right portion of the highlighted cell until it becomes a
skinny-solid-black cross.
c.
CLICK & HOLD the left mouse button while you DRAG one cell to the right, then RELEASE
the mouse button. Now a mean of 61.133 should display in cell C17. Sun-Mee Kang
PSY 420
Tutorial for the Excel program
2/4
4.
Label the cells that you just created. To do this, LEFT CLICK cell
B17, then LEFT CLICK the ‘Name Box’ located at the top right of
the screen. Then, type “xbar” and press ENTER. Repeat these steps
to label cell C17 with “ybar”. The previous step is important so that
we can use the created label to define cells in other formulas.
How to compute the deviation from the mean. (Question 1. b.)
1.
Label the top cell of the D column with “X-
Xbar”. To do this, DOUBLE CLICK cell D1 and
type in
X-Xbar, then press ENTER.
2.
DOUBLE CLICK cell D2 and type in the
formula: =B2-xbar, then press ENTER. A value
of –16.53 should appear. Since we labeled cell
B17 earlier with “xbar”, we can use it as part of
the formula, this is important so that the ‘smart
feature’ works correctly.
3.
Now use the smart feature to DRAG the formula
from D2 down to D16.
4.
Repeat these steps to create a new column called
Y-Ybar, using the appropriate labels.
5.
To verify that the sum of deviations is zero, we
need to sum up the values for the X-Xbar column, and Y-Ybar respectively. To do this, enter the
formula: =SUM(D2:D16) into cell D17, then enter the formula: =SUM(E2:E16) into cell E17. Both
values should be 0.
If you get a number different than 0, it may
be necessary to reduce rounding down to 2
digits. To do this, highlight all values in the
column, RIGHT CLICK the highlighted
area and choose ‘Format Cells’. Change it
from “General” to “Number” and CLICK
“OK”. Sun-Mee Kang
PSY 420
Tutorial for the Excel program
3/4
How to compute the SS using Eq.(4-3) & Eq.(4-4). (Question 1. c.)
Eq. (4-3):
SS =
(X-Xbar)^2
1.
DOUBLE CLICK cell F1 and label it as (X-Xbar)^2.
2.
DOUBLE CLICK cell F2 and type in the formula: =D2^2, you should get a value of 273.35.
3.
Use the smart feature to DRAG the formula from F2 down to F16.
4.
DOUBLE CLICK cell F17 and type in the formula: =SUM(F2:F16), you should get 1749.73. Then label
this cell as SUMxdev2 in the ‘Name Box’.
5.
Repeat these steps to create new column called (Y-Ybar)^2, using the appropriate labels.
Eq. (4-4) “ SS =
X^2 – (
X)^2
N
1.
DOUBLE CLICK cell H2 and label it as X^2
2.
DOUBLE CLICK cell H3 and type in the formula: =B2^2, you should get a value of 22201.
3.
Use the smart feature to DRAG the formula from H2 down to H16.
4.
DOUBLE CLICK cell H17 and type in the formula: =SUM(H2:H16), then label it as SUMx2 in the
‘Name Box’.
5.
DOUBLE CLICK cell B18, and type in the formula: =SUM(B2:B16), then label it as sumx in the ‘Name
Box’.
6.
DOUBLE CLICK cell H18 and type in the formula: =SUMx2-((sumx)^2/15)
7.
Repeat these steps to create new column called Y^2, using the appropriate labels. Sun-Mee Kang
PSY 420
Tutorial for the Excel program
4/4
How to compute the variances and standard deviations. (Question 1.d.)
1.
To get the standard deviations, DOUBLE CLICK cell B19 and type in the formula: =STDEV(B2:B16).
Then DOUBLE CLICK cell C19 and type in the formula: =STDEV(C2:C16).
2.
To get the variances, DOUBLE CLICK cell B20 and type in the formula: =VAR(B2:B16). Then
DOUBLE CLICK cell C20 and type in the formula: =VAR(C2:C16).
3.
To demonstrate a second way of getting the variances, DOUBLE CLICK cell B21 and type in the
formula: = SUMxdev2/14, where 14 = N-1. Then DOUBLE CLICK cell C21 and type in the formula: =
SUMydev2/14 .
4.
To demonstrate a second way of getting the standard deviations, DOUBLE CLICK cell B22 and type in
the formula: =SQRT(B21). Then DOUBLE CLICK cell C22 and type in the formula: =SQRT(C21) . 