Question

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

Answer 1

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.

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.

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.

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

My final chart looks like this.

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"`