*16*

The term **bivariate analysis **refers to the analysis of two variables. You can remember this because the prefix “bi” means “two.”

The purpose of bivariate analysis is to understand the relationship between two variables

There are three common ways to perform bivariate analysis:

**1.** Scatterplots

**2.** Correlation Coefficients

**3.** Simple Linear Regression

The following example shows how to perform each of these types of bivariate analysis in Excel using the following dataset that contains information about two variables: **(1)** Hours spent studying and **(2)** Exam score received by 20 different students:

**1. Scatterplots**

To create a scatterplot of hours vs. score, we can highlight cells **A2:B21**, then click the **Insert** tab along the top ribbon, then click **Insert Scatter Chart** within the **Charts** group:

We can also modify the y-axis limits to gain a better view of the data points.

To do so, double click the y-axis. In the **Format Axis** panel that appears on the right side of the screen, click **Axis Options** and then change the **Minimum** and **Maximum** bounds to 60 and 100, respectively.

The y-axis will automatically update:

The x-axis shows the hours studied and the y-axis shows the exam score received.

From the plot we can see that there is a positive relationship between the two variables. As hours studied increases, exam score tends to increase as well.

**2. Correlation Coefficients**

A Pearson Correlation Coefficient is a way to quantify the linear relationship between two variables.

We can use the following formula in Excel to calculate the correlation coefficient between hours studied and exam score:

=CORREL(A2:A21, B2:B21)

The correlation coefficient turns out to be **0.891**.

This value is close to 1, which indicates a strong positive correlation between hours studied and exam score received.

**3. Simple Linear Regression**

Simple linear regression is a statistical method we can use to quantify the relationship between two variables.

To fit a simple linear regression model in Excel, click the **Data** tab along the top ribbon, then click the **Data Analysis** option in the **Analyze** group. In the new panel that appears, click **Regression** and then click **OK**.

**Note**: If you don’t see the Data Analysis option, you need to first load the Excel Analysis ToolPak.

In the panel that appears, enter the following information and then click **OK**:

Once you click **OK**, the results of the regression model will appear:

The fitted regression equation turns out to be:

Exam Score = 69.0734 + 3.8471*(hours studied)

This tells us that each additional hour studied is associated with an average increase of **3.8471** in exam score.

We can also use the regression equation to estimate the score that a student will receive based on their total hours studied.

For example, a student who studies for 3 hours is estimated to receive a score of **81.6147**:

- Exam Score = 69.0734 + 3.8471*(hours studied)
- Exam Score = 69.0734 + 3.8471*(3)
- Exam Score = 81.6147

**Additional Resources**

The following tutorials provide additional information about bivariate analysis:

An Introduction to Bivariate Analysis

5 Examples of Bivariate Data in Real Life

An Introduction to Simple Linear Regression