Using the Trendline feature. This quick method provides a line of best fit on an Excel chart and can also provide a set of equation values for predictive purposes. 1\. Create a graph (chart) of your data. Enter the data in two columns within your spreadsheet, select the data array (highlight using left mouse button) and then, using the 'Insert' icon, select 'Scatter' and then the icon 'Scatter only with markers'. 2\. Add a trend line. Right-click on any of the data points on your graph, and select the AddTrendline menu. Choose the Linear trend line option, but do not click OK at this stage. Select: (i) Display equation on chart and (ii) Display R-squared value on chart. Now click OK. The equation (shown in the form \(y=b x+a)\) gives the slope and intercept of the line of best fit, while the \(R\)-squared value (coefficient of determination, p. 499) gives the proportional fit to the line (the closer this value is to 1, the better the fit of the data to the trend line). 3\. Modify the graph to improve its effectiveness. For a graph that is to be used elsewhere (e.g. in a lab write-up or project report), adjust the display to remove the default background and gridlines and change the symbol shape. Right-click on the trend line and use the Format Trendline \(>\) line style menu to adjust the Weight of the line to make it thinner or thicker. Drag and move the equation panel if you would like to alter its location on the chart. Fig. \(48.4\) shows a typical calibration curve produced in this way. 4\. Add a title and axes labels. Click on the Layout icon \(>\) chart title (to add a title) and Layout icon \(>\) chart axis (horizontal or vertical to add a label to the \(x\) and \(y\) axis respectively). 5\. Use the regression equation to estimate unknown (test) samples. By rearranging the equation for a straight line and substituting a particular \(y\)-value, you can predict the amount/concentration of substance ( \(x\)-value) in a test sample. This is more precise than simply reading the values from the graph using construction lines. If you are carrying out multiple calculations, the appropriate equation, \(x=(y-a) / b\), can be entered into a spreadsheet, for convenience.

Step by step solution

01

Create a scatter plot in Excel

Enter your data in two separate columns in Excel. Then, highlight this data array by left-clicking and dragging the mouse. Go to the 'Insert' tab, select 'Scatter', and then choose the option 'Scatter only with markers'.

02

Add a trendline

Right-click on any of the data points in your graph and select the 'Add Trendline' option from the menu that appears. Choose the 'Linear' trendline option but do not click 'OK' yet. Also select the options 'Display equation on chart' and 'Display R-squared value on chart'. Now, you can click 'OK'. The equation will be displayed in the form y = bx + a, where b is the slope and a is the intercept.

03

Improve the graph effectivness

For use in other contexts, such as a lab report, adjust the graph display to remove the default background and gridlines. Change the symbol's shape and weight of the trend line by right-clicking on it and using the 'Format Trendline > Line style' menu. The equation panel can also be moved to alter its location on the chart.

04

Add title and labels

Click on the 'Layout' icon > 'Chart title' to add a title to your graph. Add labels to the axes by selecting 'Layout' icon > 'Axis Titles' > 'Primary Horizontal Axis Title' for the x-axis or 'Primary Vertical Axis Title' for the y-axis.

05

Implement the regression equation

The regression equation can be used to predict the amount or concentration of an unknown sample by rearranging the equation for a straight line and substituting a particular y-value. More specifically, x = (y - a)/b. This equation can be entered into a spreadsheet for multiple calculations.

Unlock Step-by-Step Solutions & Ace Your Exams!

• Full Textbook Solutions

  Get detailed explanations and key concepts

• Unlimited Al creation

  Al flashcards, explanations, exams and more...

• Ads-free access

  To over 500 millions flashcards

• Money-back guarantee

  We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!