Tutorial for chapter4 lab ex

Skang

Description

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. • Formulas always start with an = sign. • Remember to press ENTER after you finish typing in a cell, press ESC to cancel the current operation. Getting started First, you must download and open the data set into the EXCEL program. To download the data set, go to the 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 ...

Informations

• Publié par: Thoim
• Nombre de lectures: 11
• Langue: English

Extrait

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.

•

Formulas always start with an = sign.

•

Remember to press ENTER after you finish typing in a cell, press ESC to cancel the current operation.

Getting started

First, you must download and open the data set into the

EXCEL program. To download the data set, go to the

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.

At this point, your spreadsheet should be similar to the following:

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) .

YOU ARE DONE! Your spreadsheet should look like this:

Here is a list of the basic functions used in the formulas, where x denotes a cell label:

Mean: =AVERAGE(xx : xx)

Sum: =SUM(xx : xx)

Square root: =SQRT (xx)

Standard deviation: =STDEV(xx : xx)

Variance: =VAR(xx : xx)