how to find r squared in excel

Linear Regression in Excel

Create an initial scatter plot
Creating a linear regression line (trendline)
Using the regression equation to calculate slope and intercept
Using the R-squared coefficient adding to judge fit

Introduction

Regression lines tin can exist used as a way of visually depicting the relationship between the independent (x) and dependent (y) variables in the graph. A directly line depicts a linear trend in the data (i.east., the equation describing the line is of first order. For case, y = 3x + four. In that location are no squared or cubed variables in this equation). A curved line represents a trend described by a higher order equation (e.g., y = 2xⁱⁱ + 5x - eight). It is important that you are able to defend your utilize of either a straight or curved regression line. That is, the theory underlying your lab should betoken whether the relationship of the independent and dependent variables should be linear or not-linear.

In improver to visually depicting the trend in the data with a regression line, y'all tin can besides calculate the equation of the regression line. This equation can either be seen in a dialogue box and/or shown on your graph. How well this equation describes the data (the 'fit'), is expressed as a correlation coefficient, R² (R-squared). The closer R² is to 1.00, the better the fit. This also tin exist calculated and displayed in the graph.

The information below was outset introduced in the basic graphing module and is from a chemistry lab investigating lite absorption by solutions. Beer's Law states that there is a linear relationship betwixt concentration of a colored compound in solution and the light absorption of the solution. This fact tin be used to calculate the concentration of unknown solutions, given their assimilation readings. This is done by fitting a linear regression line to the collected data.

Creating an initial scatter plot

Before you can create a regression line, a graph must be produced from the data. Traditionally, this would be a scatter plot. This module will start with the scatter plot created in the bones graphing module.

Figure one.

Return to Acme

Creating a Linear Regression Line (Trendline)

When the chart window is highlighted, you can add a regression line to the chart by choosing Chart > Add together trendline...

A dialogue box appears (Figure 2). Select the Linear Tendency/Regression type:

Figure 2.

Choose the Options tab and select Brandish equation on chart (Figure 3):

Figure 3.

Click OK to shut the dialogue. The chart now displays the regression line (Effigy 4)

Effigy iv.

Return to Peak

Using the Regression Equation to Calculate Concentrations

The linear equation shown on the chart represents the relationship between Concentration (x) and Absorbance (y) for the compound in solution. The regression line can be considered an adequate interpretation of the truthful relationship betwixt concentration and absorbance. Nosotros have been given the absorbance readings for ii solutions of unknown concentration.

Using the linear equation (labeled A in Effigy 5), a spreadsheet prison cell can have an equation associated with it to exercise the calculation for us. Nosotros have a value for y (Absorbance) and need to solve for x (Concentration). Below are the algebraic equations working out this calculation:

y = 2071.9x + 0.111

y - 0.0111 = 2071.9x

(y - 0.0111) / 2071.9 = x

At present we have to convert this terminal equation into an equation in a spreadsheet jail cell. The equation associated with the spreadsheet cell will look like what is labeled C in Effigy 8. 'B12' in the equation represents y (the absorbance of the unknown). The solution for 10 (Concentration) is then displayed in cell 'C12'.

Highlight a spreadsheet cell to concord 'ten', the consequence of the concluding equation (cell C12, labeled B in Figure v).

Click in the equation area (labeled C, figure v)

Type an equal sign so a parentheses

Click in the cell representing 'y' in your equation (prison cell B12 in Figure 5) to put this cell label in your equation

Finish typing your equation

Notation: If your equation differs for the one in this case, use your equation

Duplicate your equation for the other unknown.

Highlight the original equation cell (C12 in Figure five) and the cell below it (C13)

Choose Edit > Fill up > Down

Return to Meridian

Note that if you highlight your new equation in C13, the reference to cell B12 has also incremented to cell B13.

Figure v.

Return to Top

Using the R-squared coefficient calculation to guess fit

Double-click on the trendline, cull the Options tab in the Format Trendlines dialogue box, and check the Display r-squared value on chart box. Your graph should now look like Figure vi. Annotation the value of R-squared on the graph. The closer to 1.0, the better the fit of the regression line. That is, the closer the line passes through all of the points.

Figure half dozen.

Now lets expect at another set of data done for this lab (Figure seven). Notice that the equation for the regression line is different than is was in Effigy 6. A different equation would summate a different concentration for the two unknowns. Which regression line better represents the 'truthful' relationship betwixt absorption and concentration? Look at how closely the regression line passes through the points in Figure seven. Does information technology seem to 'fit' too as it does in Figure vi? No, and the R-squared value confirms this. Information technology is 0.873 in Figure vii compared to 0.995 in Figure 6. Though we would need to accept in to account information such equally the number of data points collected to brand an accurate statistical prediction equally to how well the regression line represents the true relationship, we can generally say that Figure 6 represents a better representation of the relationship of absorption and concentration.

Figure seven.

Render to Top