This is not the document you are looking for? Use the search form below to find more!

3.00 (8 votes)

The purpose of this tutorial is to provide instruction on performing basic functions using Microsoft Excel. Although Excel has the ability to perform a large array of mathematical and statistical functions, this resource addresses data entry, and calculating means (averages) for either one time or pre/post survey instruments (or post/pre instruments). In addition, when an instrument is completed at two points in time, Excel provides the ability to perform statistical tests (t-tests) to determine the significance of mean differences between the pre and post test for participants receiving a program (or treatment). Finally, additional resources to help you master Excel are listed at the end of this brief.

- Added:
**September, 11th 2008** - Reads:
**8986** - Downloads:
**1586** - File size:
**197.52kb** - Pages:
**6** - content preview

- Username: desantis
- Name:
**desantis** - Documents:
**37**

Related Documents

Regression analysis can be used to identify the line or curve which provides the best fit through a set of data points. This curve can be useful to identify a trend in the data, whether it is linear, ...

Microsoft Excel Global Information Internship Program Created By: Jamie McPike 2003 How to Use Tutorial Step 1 > The next page will show you the table of contents Step 4…

You have just finished a very basic tutorial in using Microsoft Word. There are many other things you can do with this amazing program. The Question Mark icon at the extreme top right of your screen ...

This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System. Further information about Oracle and SQL can be found on the ...

Adobe Photoshop CS3 is a popular image editing software that provides a work environment consistent with Adobe Illustrator, Adobe InDesign, Adobe ImageReady, and other products in the Adobe Creative ...

This memorandum is a tutorial to make learning C as painless as possible. The first part concentrates on the central features of C; the second part discusses those parts of the language which are ...

This book is a tutorial. Its aim is to teach C to a beginner, but with enough of the details so as not be outgrown as the years go by. It presumes that you have some previous acquittance with ...

Tutorial on C Language Program Teodor Rus The University of Iowa, Department of Computer Science

This tutorial focuses on the basic steps involved in creating an attractive, functional website. Learn to design a site layout, insert images and text, create links, and how to upload your site to ...

Content Preview

The purpose of this tutorial is to provide instruction on performing basic functions using Microsoft

Excel. Although Excel has the ability to perform a large array of mathematical and statistical

functions, this resource addresses data entry, and calculating means (averages) for either one time or

pre/post survey instruments (or post/pre instruments). In addition, when an instrument is completed

at two points in time, Excel provides the ability to perform statistical tests (t-tests) to determine the

significance of mean differences between the pre and post test for participants receiving a program

(or treatment). Finally, additional resources to help you master Excel are listed at the end of this

brief.

You will be put into a blank “workbook” which is

simply a blank spreadsheet. Please note that Excel

Figure 1. Column labels.

automatically numbers the rows in the left hand

A

margin, and assigns letters to the columns – these

1

Participant Number

will not change and you must label both your

2

1

rows and columns for your data entry and

3

2

analysis. (Excel uses these column letters and

4

3

numbered rows to perform mathematical

5

4

functions, which is explained later.)

6

5

7

6

2.

8

7

Start with the top row and label each column, as

9

8

follows:

10

9

a. Column A = Participant Number or

11

10

identifying word, symbol, etc. [For

12

11

example, if you have 12 participants,

13

12

number from 1-12 DOWN the column]

See Figure 1, right.

b. The Column labels across the spreadsheet (B, C, D, etc.) need to be labeled for each item

on your survey that has a response that needs to be coded. [In this example, there are five

items (questions) to measure, so Column B gets labeled Question 1, Column C is labeled

item 2, etc.]

A

B

C

D

E

F

1

Participant Number

Question 1

Question 2

Question 3

Question 4

Question 5

c. Note also that it is helpful to include a key word from each question to help you remember

which question is which. For example, if your statement reads, “I am confident in choosing

books that are appropriate for my children,” then you may want to label that column,

“Question 1 Confidence” so you can understand your spreadsheet more fully.

A

B

C

D

E

F

Participant Question 1 –

Question 2 -

Question 3

Question 4

Question 5

1

Number

Confidence Insert key word Insert key word Insert key word Insert key word

3.

each row of responses, your spreadsheet should look like this:

A

B

C

D

E

F

1

Participant Number

Question 1

Question 2

Question 3

Question 4

Question 5

2

1

3

2

4

3

5 4

6 5

7 6

8 7

9 8

10

9

11

10

12

11

13

12

14

Mean

Score

4. Now you are ready to

5.

complete when entering data is assigning numerical value to each response on your Likert

scale from your evaluation instrument. (A

response scale. For instance, when parents complete pre and/or post tests about a workshop,

they are asked to answer each question by rating each item on a 1-to-5 response scale.)

a. For example, if your Likert scale includes the items: “strongly agree,” “agree,”

“neutral,” “disagree,” and “strongly disagree” – you could code responses to have

strongly agree = 5, agree = 4, neutral = 3, disagree =2 and strongly disagree = 1 so

that a higher score reflects a higher level of agreement of each item.

b. This is important because after you enter the individual scores, you will calculate

an average – or mean score for the whole group for each survey question. In the

case of assigning higher values to stronger agreement, then higher mean scores

for each question will translate into levels of agreement for each item, and thus,

lower scores will reflect participants’ disagreement with each item asked.

c. It is extremely important to note that how you interpret the level of agreement for

each survey question will depend on each item asked. For example, if your survey

states, “I am confident in choosing books that are appropriate for my children,”

then you would hope for a higher mean – closer to 4 or 5 which indicates that

respondents do feel confident. However, if you had phrased the question

differently, “I am NOT confident in choosing books that are appropriate for my

children,” then you would like to see a lower mean score, closer to 2 or 1 which

would reflect parental confidence.

2

6. After assigning a value to each response, you can

disagree = 1 so that a higher score reflects a higher level of agreement of each item).

a. For example, if Participant #1 responds to question #1 with an “agree” response,

you would enter a 4 in that cell. It would look like this:

A

B

1 Participant Number Question 1

2

1

3

b. You can continue to enter the corresponding number for each response in each

cell of the spreadsheet. If your data was as follows:

i. Participant 1, Question 1, “agree” = 4

ii. Participant 1, Question 2, “disagree” = 2

iii. Participant 1, Question 3, “neutral” = 3

iv. Participant 2, Question 1, “strongly agree” = 5

v. Participant 2, Question 2, “agree” = 4

vi. Participant 2, Question 3, “strongly disagree” = 1

The spreadsheet entry would look like this:

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2

1 4

2

3

3

2 5

4

1

4

7. Once you have filled in the spreadsheet you can

8.

following steps:

a. Place the cursor in the box where you want the mean score to appear (see below

(X) for example).

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2

1 4

2

3

3

2 5

4

1

4 Mean

Score

X

b. Go to the

you look at the table above, you will see that the score in column B row 2 = 4, and

in column B row 3 = 5. By calculating an average for column B, you will know

the mean for that item on the pre-test. Note that if you have more than two

respondents, you will need to include more numbers in your average. For

1 Depending in the version of Excel you use, the

or next to the ? symbol on the top toolbar. Some versions use a pop up menu, in which you would select the

functional category “statistical” and then “average.”

3

example, if you have 12 respondents, with data in boxes B2 through B13, then

you would type

c. Once you type in the formula above, depress the enter key and the mean will

appear in the box you marked, and should look like this:

A

B

C

D

1 Participant Number Question 1 Question 2 Question 3

2 1

4 2 3

3 2

5 4 1

4 Mean

As you can see the mean score of question 1 for this sample of two participants is

4.5, which corresponds between “strongly agree” and “agree.” You could then

report that your mean score on Question 1

and report the mean scores for each item on your survey. If you are using a pre

and post test, step 9 provides instruction for the remaining data entry and then

calculating and testing differences in means for each item at the two points in

time.

9.

steps 1-8 to

if you have one. In many instances, it is possible to make one spreadsheet that contains

both pre and post test scores, which can be arranged as follows:

A

B

C

D

E

F

G

H

1 Participant

Question 1 Question 2

Question 3

Question

1 Question 2

Question 3

Number

PRE

PRE

PRE

POST

POST

POST

2 1

4

2

5 5

4

5

3 2

3

3

3 4

3

4

4

10. After you have calculated both pre and post test means for each item you can

a. For example, if your mean score on an item, “I am confident in choosing books

that are appropriate for my children,” on the pre-test was

“agree” on our scale), and a

agree” on our scale); you could report that on average, participants in your

workshop increased 1.0 on that item, reflecting an increase in parent’s confidence

(one of your program goals).

11.

test whether or not the differences in mean scores for each item are significant, rather than

due to chance or other circumstances. For our purposes, we can run a t-test using Excel to

determine the significance of the differences in means between the pre- and post-tests.

Note that the t-test tells us whether or not the difference in means for each question is

statistically significant among all program participants.

4

To begin the t-test, place the cursor in the box where you want the t-test result to appear (see

below (X) for example.)

A

B

C

D

E

F

G

1 Participant

Question 1 Question 1 Question 2 Question 2 Question 3

Question 3

Number

PRE

POST

PRE

POST

PRE

POST

2 1

4

5

4 4 5 5

3 2

3

4

2 3 3 4

4

3.5 4.5 3 3.5 4.0 4.5

5 T-test,

p-value

a. Then place your cursor on the

list of possible mathematical functions that Excel can perform. Under “select a

function” highlight TTEST, and depress your enter key. You will then be asked to

specify an array – or more simply the group of numbers you want to test. In this

case, array number 1 would correspond to the scores given on Question 1 PRE

test, columns B2:B3 on the table above. Therefore, you need to type B2:B3 in the

window to the right of “array 1” in the function box. The scores you are

comparing with are the POST test scores on that same question, columns C2:C3

above, this is your second array, so you need to type C2:C3 to the right of “array

2” in the function box. You then need to specify if you want a one or two tailed

distribution, in this case you need to enter “2” in the window next to the “tails”

box. Finally, because we are testing the same sample at two different time

intervals, we need to do a paired t-test – and thus, need to enter a “1” next to the

“type” window in the function box.

functions check Excel’s website at

will need to include more numbers in your average. For example, if you have 12

respondents, with data in boxes B2 through B13 for the pre-test, then you would

type

b. Once you have entered all the information in the function popup box, depress the

enter key and Excel will perform the test and generate a p-value which will appear

in the box you marked, and should look like this:

1

2

1

5 5 5 5

3

2

5 5 3 5

4

3

5 5 5 5

5

4

5 5 4

6

5

5 5 4 4

7

6

5 5 4 5

8

7

4 5 4 5

9

8

5 5 5 5

10

9

5 5 4 5

11

10

4 5 4

12

11

5 5 5 5

13

12

4 5 4 5

20

Mean

Score

4.78 4.97 4.24 4.88

21 Difference

0.19

0.64

22

5

c.

our assumption that the difference in means on pre and post surveys is real and

not due to chance.

i. As you can see the p-value generated by a t-test for Question 1 is .0488,

and .0014 for Question 2.

ii. In general, researchers say that a p-value of .10 or less is statistically

significant, which means that we are 90% sure that the result we see (the

difference in means for each question) is not due to chance.

iii. Therefore, when reporting the results of your pre/post test on evaluation

surveys, you could report that a t-test confirms that the change on a given

item were “significant at a p<.10 level.”

programs delivered in your community. At a minimum, Excel can compute the mean responses

to each item on your evaluation instrument, whether they are administered once or twice during

your program. This allows an educator to report the average response among program

participants. In addition, when both a pre and post test are completed, Excel can both calculate

the change in means among participants and test whether or not the differences in mean scores

for each item are significant, rather than due to chance.

There are many additional resources available that provide simple instructions on using

Microsoft Excel, many of which are free of charge:

Websites:

Microsoft Office Online Tutorials http://office.microsoft.com/en-us/training.aspx provides many

free tutorials.

http://www.exceltip.com/tutorial/index.html provides free tutorials.

http://www.videoprofessor.com sells tutorials for many different versions of Excel, at a nominal

charge ($6.95).

http://www.vtc.com/products/excel2000.htm sells more advanced tutorials.

Printed Materials:

• Mastering Excel 2000 (for beginner)

• Microsoft Excel Version 2002 Step by Step

• Excel 2002 For Dummies®

• Microsoft Excel 2002 Simply Visual

• Absolute Beginner's Guide to Microsoft Excel 2002

• Absolute Beginner's Guide to Microsoft Office Excel 2003

(All can be accessed through http://www.amazon.com or http://www.exceltip.com/bc-

Microsoft_Excel_books_for_Beginners,3 )

the Department of Policy Analysis and

Management at Cornell University.

6

## Add New Comment