Excel

To conduct data analyses in Excel, you first need to load the Analysis ToolPak. To do this,

Click the Microsoft Office Button and then click Excel Options.
Click Add-Ins, and then in the Manage box, select Excel Add-ins.
Click Go.
In the Add-Ins available box, select the Analysis ToolPack, check the box, and then click OK.

Excel Formulas to know. Make sure to include “=” before each formula and separate arguments with ;

Description	English	German	Example	Notes
Arithmethic mean	AVERAGE	MITTELWERT	=MITTELWERT(A:A)
Median	MEDIAN	MEDIAN	=MEDIAN(A:A)
Standard deviation	STDEV	STABW	=STABW(A:A)
Counting cases	COUNTIF	ZÄHLENWENN	=COUNTIF(A:A, “male”)	The first argument is the cells you want to perform the count on. The second argument is the criteria you are trying to count. For example, “male” means you want to count all cells that are “male”.
Pearson correlation	PEARSON	PEARSON	=PEARSON(A1:A10;B1:B10)
T-test	TTEST	TTEST	=TTEST(A1:A10;B1:B10;2;3)	The third argument is the number of tails (1 or 2 - you will usually use 2), the fourth argument indicates the test type (where 1 is paired samples, 2 is two-samples with equal variance, and 3 is two samples with unequal variance - you will use 3)

Opening data and sorting by columns

Download the following dataset and open it in Excel: https://www.dropbox.com/s/8py8hlgeavai4yj/exceldataforclass.txt?dl=0
Sort the data by columns in the following order: drink, participant

Adding Columns

To add columns that are functions of existing columns, enter the appropriate formula in the top cell, then copy and paste the cell to cover all cells in the column. For example, to create a column C that is column A times column B, enter =(A1*B1) in cell C1, then copy and paste cell C1 down.

(Demonstration) Create a column called “time.1.seconds” that shows time.1 in seconds instead of milliseconds

Create a new column called “change” that shows the change in response time from time.1 to time.2 (that is, time.2 - time.1)

Descriptive Statistics

To calculate a descriptive statistic, enter the apppropriate formula (starting with =) and select the data you want to analyze as the argument. For example, =AVERAGE(A1:A10) will calculate the mean of the data in cells A1 through A10

(Demonstration). How many participants were in each of the three drink groups?

How many participants were male? Female? Other/No Response? (use COUNTIF)

(Demonstration). What was the mean, median, and standard deviation of ages of participants?

What was the mean, median, and standard deviation of response times at time.1 for each drink type?
What was the mean, median, and standard deviation of response times at time.2 for each drink type?
What is the mean, median, and standard deviation of change for each drink type?

Plotting

To create a plot (aka chart), click ‘Charts’ and select the type of chart you want. Then follow the dialog box to create the chart. Once you’ve created a chart, you can change aspects of it using the ‘Chart Layout’ and ‘Format’ menus

(Demonstration). Create a scatterplot with age on the x-axis and time.1 on the y-axis.

Create a scatterplot with time.1 on the x-axis and time.2 on the y-axis.

(Demonstration). Create a bar graph showing the mean age for each drink group.

Create a bar graph showing the mean time change for each drink group.

Correlation

To calculate a pearson correlation coefficient, use the PEARSON function. For example, to calculate the correlation between columns A and B, use the formula =PEARSON(A1:A10;B1:B10)

(Demonstration) What is the pearson correlation between age and time.1?

What is the pearson correlation between response times at time.1 and time.2 (use PEARSON)?

T-test

To calculate a t-test, use the TTEST function. Enter the two columns of data as the first two arguments, then indicate the number of tails (1 for a one-tailed test and 2 for a two-tailed test), and the test type (1, 2 or 3, see the help menu for a description of each).

(Demonstration) Was there a difference in ages between tea and coffee drinkers? Conduct a two-sample t-test comparing the average age of the two groups. Report the results in APA format (t(XX) = YYY, p = ZZZ).

Was the change in reaction times different between coffee and water drinkers? Conduct a one-sample t-test comparing change between coffee and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between tea and water drinkers? Conduct a one-sample t-test comparing change between tea and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between coffee and tea drinkers? Conduct a one-sample t-test comparing change between tea and coffee. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)

ANOVA

To calculate an ANOVA, use the data-analysis toolpak

(Demonstration) Was there a difference in age between the drink groups? Conduct an ANOVA on age across drink types.

Was the reaction time in time.1 different across drink groups? Conduct an ANOVA on time.1 across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).
Was the change in reaction times different across drink groups? Conduct an ANOVA on change across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).

SPSS

Opening data and sorting by columns

File / Open / Data

Download the following dataset and open it in SPSS: https://www.dropbox.com/s/8py8hlgeavai4yj/exceldataforclass.txt?dl=0.

Data / Sort Cases

Sort the data by columns in the following order: drink, participant

Adding Columns

Transform / Compute Variable

(Demonstration) Create a column called “time.1.seconds” that shows time.1 in seconds instead of milliseconds

Create a new column called “change” that shows the change in response time from time.1 to time.2 (that is, time.2 - time.1)

Descriptive Statistics

Analyze / Descriptive Statistics / Descriptives

Analyze / Descriptive Statistics / Explore

(Demonstration). How many participants were in each of the three drink groups?

How many participants were male? Female? Other/No Response?

(Demonstration). What was the mean, median, and standard deviation of ages of participants?

What was the mean, median, and standard deviation of response times at time.1 for each drink type?
What was the mean, median, and standard deviation of response times at time.2 for each drink type?
What is the mean, median, and standard deviation of change for each drink type?

Plotting

Graphs / Chart Builder

(Demonstration). Create a scatterplot with age on the x-axis and time.1 on the y-axis.

Create a scatterplot with time.1 on the x-axis and time.2 on the y-axis.

(Demonstration). Create a bar graph showing the mean age for each drink group.

Create a bar graph showing the mean time change for each drink group.

Correlation

Analyze / Correlate / Bivariate

(Demonstration) What is the pearson correlation between age and time.1?

What is the pearson correlation between response times at time.1 and time.2? What is your conclusion? Write the result in APA style (r(XXX) = YYY, p = ZZZ)

T-test

Analyze / Compare Means / One-Sample T Test (or Indepednent-Samples T Test)

Was the change in reaction times different between coffee and water drinkers? Conduct a one-sample t-test comparing change between coffee and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between tea and water drinkers? Conduct a one-sample t-test comparing change between tea and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between coffee and tea drinkers? Conduct a one-sample t-test comparing change between tea and coffee. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)

ANOVA

Analyze / Compare Means / One-Way ANOVA

(Demonstration) Was there a difference in age between the drink groups? Conduct an ANOVA on age across drink types.

Was the reaction time in time.1 different across drink groups? Conduct an ANOVA on time.1 across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).
Was the change in reaction times different across drink groups? Conduct an ANOVA on change across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).

R

R Notes

Always type your R code in an R script, not the R console. To run your code, highlight the code then use the key-combination “Control + Return”
Add comments to your code using #

Opening data and sorting by columns

Download the following dataset http://nathanieldphillips.com/wp-content/uploads/2015/06/exceldataforclass.txt.

data <- read.table("http://nathanieldphillips.com/wp-content/uploads/2015/06/exceldataforclass.txt", header = T)

View the dataset by running the code

View(data)

Demonstration: Sort the data by age and time.1

# Sort a dataframe by columns
data <- data[order(data$age, data$time.1),]

Sort the data by columns in the following order: drink, participant. Then look at the data using View(data) to make sure it worked!

Adding Columns

Demonstration: Create a column called “time.1.seconds” that shows time.1 in seconds instead of milliseconds

data$time.1.seconds <- data$time.1 / 1000

Create a new column called “change” that shows the change in response time from time.1 to time.2 (that is, time.2 - time.1)

Descriptive Statistics

Demonstration: How many participants were in each of the three drink groups?

# Use the table() function to get counts for a variable
table(data$drink)

## 
## coffee    tea  water 
##     50     50     50

How many participants were male? Female? Other/No Response?

Demonstration: What was the mean, median, and standard deviation of ages of participants?

mean(data$age)

## [1] 21.27333

median(data$age)

## [1] 21.5

sd(data$age)

## [1] 1.867765

What was the mean, median, and standard deviation of response times at time.1?

Demonstration: What was the mean, median, and standard deviation of response times at time.1 for each drink type?

tapply(data$time.1, # DV
       data$drink,  # IV
       mean         # Function
       )

## coffee    tea  water 
## 107.76 106.40 106.40

tapply(data$time.1, data$drink, median)

## coffee    tea  water 
##    109    108    108

tapply(data$time.1, data$drink, sd)

##   coffee      tea    water 
## 9.208603 9.880924 9.880924

What was the mean, median, and standard deviation of response times at time.2 for each drink type?
What is the mean, median, and standard deviation of change for each drink type?

Plotting

Demonstration: Create a scatterplot with age on the x-axis and time.1 on the y-axis.

# To create a scatterplot of x and y, use plot()

plot(x = data$age, 
     y = data$time.1, 
     main = "Main Title", 
     xlab = "X axis title", 
     ylab = "Y axis title"
     )

Create a scatterplot with time.1 on the x-axis and time.2 on the y-axis.

Demonstration: Create a bar graph showing the mean age for each drink group.

# To create a barplot, use barplot() with tapply() as the main argument

barplot(tapply(data$age, data$drink, mean), 
        main = "Main Title", 
        xlab = "X axis title", 
        ylab = "Y axis title"
        )

Create a bar graph showing the mean time change for each drink group.

Correlation

Demonstration: What is the pearson correlation between age and time.1?

# To calculate a correlation, use cor.test()
cor.test(x = data$age, 
         y = data$time.1
         )

## 
##  Pearson's product-moment correlation
## 
## data:  data$age and data$time.1
## t = 9.2849, df = 148, p-value = 2.22e-16
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.4945218 0.6989980
## sample estimates:
##       cor 
## 0.6067006

# r(148) = 0.61, p < .01

What is the pearson correlation between response times at time.1 and time.2? What is your conclusion? Write the result in APA style (r(XXX) = YYY, p = ZZZ)

T-test

Demonstration: Was there a difference in ages between tea and coffee drinkers? Conduct a two-sample t-test comparing the average age of the two groups. Report the results in APA format (t(XX) = YYY, p = ZZZ).

# To calculate a t-test, use t.test(dv ~ iv, data = data, subset = iv %in% c("group1", "group2"))

t.test(age ~ drink,   # dv ~ iv
       data = data,   # name of dataset
       subset = drink %in% c("coffee", "tea")
       )

## 
##  Welch Two Sample t-test
## 
## data:  age by drink
## t = 1.326, df = 95.574, p-value = 0.188
## alternative hypothesis: true difference in means is not equal to 0
## 95 percent confidence interval:
##  -0.2485149  1.2485149
## sample estimates:
## mean in group coffee    mean in group tea 
##                21.54                21.04

# t(95.58) = 1.33, p = 0.19

Was the change in reaction times different between coffee and water drinkers? Conduct a one-sample t-test comparing change between coffee and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between tea and water drinkers? Conduct a one-sample t-test comparing change between tea and water. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)
Was the change in reaction times different between coffee and tea drinkers? Conduct a one-sample t-test comparing change between tea and coffee. What is your conclusion? Write your result in APA format (t(XX) = YYY, p = ZZZ)

ANOVA

Demonstration: Was there a difference in age between the drink groups? Conduct an ANOVA on age across drink types.

# To calculate an ANOVA, use summary(aov(dv ~ iv, data = data))

summary(aov(age ~ drink, 
            data = data))

##              Df Sum Sq Mean Sq F value Pr(>F)
## drink         2    6.3   3.167   0.907  0.406
## Residuals   147  513.5   3.493

# F(2, 147) = 0.91, p = 0.41

Was the reaction time in time.1 different across drink groups? Conduct an ANOVA on time.1 across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).
Was the change in reaction times different across drink groups? Conduct an ANOVA on change across drink types. What is your conclusion? Write your result in APA format (F(XX) = YYY, p = ZZZ).

Empra Excel and SPSS Class Work

Nathaniel Phillips

15 Jun 2015

Excel

Opening data and sorting by columns

Adding Columns

To add columns that are functions of existing columns, enter the appropriate formula in the top cell, then copy and paste the cell to cover all cells in the column. For example, to create a column C that is column A times column B, enter =(A1*B1) in cell C1, then copy and paste cell C1 down.

Descriptive Statistics

To calculate a descriptive statistic, enter the apppropriate formula (starting with =) and select the data you want to analyze as the argument. For example, =AVERAGE(A1:A10) will calculate the mean of the data in cells A1 through A10

Plotting

To create a plot (aka chart), click ‘Charts’ and select the type of chart you want. Then follow the dialog box to create the chart. Once you’ve created a chart, you can change aspects of it using the ‘Chart Layout’ and ‘Format’ menus

Correlation

To calculate a pearson correlation coefficient, use the PEARSON function. For example, to calculate the correlation between columns A and B, use the formula =PEARSON(A1:A10;B1:B10)

T-test

To calculate a t-test, use the TTEST function. Enter the two columns of data as the first two arguments, then indicate the number of tails (1 for a one-tailed test and 2 for a two-tailed test), and the test type (1, 2 or 3, see the help menu for a description of each).

ANOVA

To calculate an ANOVA, use the data-analysis toolpak

SPSS

Opening data and sorting by columns

File / Open / Data

Data / Sort Cases

Adding Columns

Transform / Compute Variable

Descriptive Statistics

Analyze / Descriptive Statistics / Descriptives

Analyze / Descriptive Statistics / Explore

Plotting

Graphs / Chart Builder

Correlation

Analyze / Correlate / Bivariate

T-test

Analyze / Compare Means / One-Sample T Test (or Indepednent-Samples T Test)

ANOVA

Analyze / Compare Means / One-Way ANOVA

R

Opening data and sorting by columns

Adding Columns

Descriptive Statistics

Plotting

Correlation

T-test

ANOVA