What is an example of a linear regression practice problem using Excel?

Dec 28, 2016

WARNING! Long answer! Here's one example.

Explanation:

In a Beer's Law experiment, you must first plot a calibration curve of absorbance ($A$) vs concentration ($c$). Assume you obtained the following data.

$\textcolor{w h i t e}{l l} A \textcolor{w h i t e}{m} c / \left(\text{mol·L"^"-1}\right)$
stackrel(——————)(0.68color(white)(mmll)1.0)
$0.61 \textcolor{w h i t e}{m m l l} 0.9$
$0.54 \textcolor{w h i t e}{m m l l} 0.8$
$0.46 \textcolor{w h i t e}{m m l l} 0.7$
$0.39 \textcolor{w h i t e}{m m l l} 0.6$
$0.32 \textcolor{w h i t e}{m m l l} 0.5$
$0.27 \textcolor{w h i t e}{m m l l} 0.4$
$0.21 \textcolor{w h i t e}{m m l l} 0.3$
$0.15 \textcolor{w h i t e}{m m l l} 0.2$
$0.08 \textcolor{w h i t e}{m m l l} 0.1$
$0 \textcolor{w h i t e}{m m m m l} 0.0$

Use Excel to plot a calibration curve. What is the concentration of a sample if its absorbance is 0.51?

Step 1. Enter your data into Excel.

Note: I am using Excel for Windows 2016. Your images and procedures may differ slightly from those here.

Step 2. Create the graph.

Drag from the upper left cell [c/(mol/L)] to the lower right cell [0.00].

(b) Click on the Insert tab.

A list of graph types will appear. Click the drop-down arrow next to the XY Scatterplot.

Choose the first option (points only).

A basic graph will appear.

(c) Add the line of best fit.

Now, select the chart so that the "handles" (the small circles at each of the corners and the midpoints of each of the lines that form the box around the graph) are visible.

Click on the Format tab and then on the drop-down arrow next to Add Chart Element

From the menu, select Trendline.More Trendline Options ….

This will open a Trendline Options menu.

Select Linear. Then scroll down to the bottom of the menu.

Select Display Equation on chart and Display R-squared value on chart.

Close the menu. The new graph is

(d) Add chart titles and axis titles.

$A = 0.6645 c + 0.005$
c = (Acolor(white)(l) "- 0.005")/0.6645 = "0.51 - 0.005"/0.6645 = "0.76 mol/L"