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

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

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

(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?

(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)?

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

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

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

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

(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?

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

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

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

(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 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 #

- 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!

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)

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?

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.

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)

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)

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