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

1 Answer
Dec 28, 2016

Answer:

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.

#color(white)(ll)Acolor(white)(m)c//("mol·L"^"-1")#
#stackrel(——————)(0.68color(white)(mmll)1.0)#
#0.61color(white)(mmll)0.9#
#0.54color(white)(mmll)0.8#
#0.46color(white)(mmll)0.7#
#0.39color(white)(mmll)0.6#
#0.32color(white)(mmll)0.5#
#0.27color(white)(mmll)0.4#
#0.21color(white)(mmll)0.3#
#0.15color(white)(mmll)0.2#
#0.08color(white)(mmll)0.1#
#0color(white)(mmmml)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.

Data

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

Step 2. Create the graph.

(a) Highlight your data.

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.

Chart Menu

Choose the first option (points only).

A basic graph will appear.

Basic Graph

(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

Add Chart Element

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

This will open a Trendline Options menu.

Trendline Options

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

Trendline Options 2

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

Close the menu. The new graph is

Regression

(d) Add chart titles and axis titles.

Return to the Add Chart Element menu and add the titles and any other embellishments.

My final chart looks like this.

With Titles

Step 3. Calculate the concentration of your sample.

The equation of your calibration curve is

#A = 0.6645c + 0.005#

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