CHEM 2204

Chemical Laboratory Techniques

Browsing Posts in Graphing

Excel Trendline

No comments

What does it mean when you add a trendline to your graph in Excel?

Check your graph with the sample graph below and make sure that your graph includes all the information that is displayed on the sample graph. The slope of the line has units of mVg/mL

In our sample graph (click image to enlarge) “Instrument Response Vs. Concentration”,

01-graphThis (Practice)

we selected the Trend/Regression Type as Linear, set the Intercept to be (0,0) and added a Trendline. Here we are assuming that the data are linear, and the mathematical model that the data follows is:

instrument response = m (concentration ) + b

where m is the slope, and b is the intercept.

If we assume that errors affecting the concentrations (the values of the x-axis) are insignificant, then any difference between an experimental data point and the model would be due to error in measuring the instrument’s response.

For each data point, the difference between the voltage measurement, Vmeasured, and the predicted voltage, Vpredicted, is a residual error, RE.

RE = (Vmeasured – Vpredicted)

Because these residual errors can be positive or negative, each data point’s RE is first squared and then summed to give a total residual error, REtot.

REtot = Σ(Vmeasured – Vpredicted)2

If different values for the slope and intercept for the line are picked, it would lead to different total residual errors. Therefore, the best-fit line is the line that has the best values for the slope and intercept, such that it would lead to the smallest total residual error.

Go to this link to see if you can find the best-fit line of a given set of data point using least squares visually.

  • Put your mouse over the red dot that is labeled y-intercept and drag the line up and down the y-axis.
  • Put your mouse over the red dot that is labeled slope and change the slope of the line.

Use a combination of these motions to obtain a line that will give the smallest total area of all the squares. Below is my best-fit line. My best effort is 811. What’s yours?


(Screen capture from http://www.dynamicgeometry.com)

Well done, you have just completed a linear regression analysis on a set of points. Now you understand the algorithm that a computer program uses to find a best-fit line for a set of data that follows a linear mathematical model. The simplest way to get the best-fit line through a set of point in Excel is to add the Trendline. The calculation that goes on to get the best-fit line is done for you. The calculations involved for a set of data with n data points, is as follows:

SLOPE
INTERCEPT

The correlation coefficient, r, gives us a measure of the reliability of the linear relationship between the x and y values. A value of r = 1 indicates an exact linear relationship between x and y. Values of r close to 1 indicate excellent linear reliability. If the correlation coefficient is much less than 1, the predictions based on the linear relationship will not be reliable.

correlation coefficient, r

Recall our sample graph as plotted by Excel (click on the image to enlarge the image). The trendline equation was determined with the y-intercept set to (0.0). The R2 value that was displayed is the square of the correlation coefficient, r.

We can ask Excel to do these calculations manually. Take a look at the slope of the best-fit line done manually. The slope and y-intercept are very similar. In the manual calculation, the y-intercept is not restricted to (0,0). Click on the image to enlarge the image.

That’s it! Now you have some understanding about what Excel does when you add a Trendline in Excel.

Click the image below to enlarge the “Instrument Response Vs. Concentration” graph.
01-graphThis (Practice)
There were some common mistakes made on the practice graphs.

  1. Include a cover page that clearly indicates your name and the peer reviewer’s name. Both should SIGN the cover page.
  2. Not everyone’s graph had their graph reviewed by a classmate. I need to see the peer reviewer’s comments. You may do more than one peer review.
  3. Peer reviewer’s name not available. (This is worth 2 marks for the peer reviewer.)
  4. Need to hand in THREE Excel-generated graphs:
    Graph 1 is your original graph.
    Graph 2 with the peer reviewer’s comments.
    Graph 3 with the peer reviewer’s with the fixes.
    Clearly mark which graph is which.
  5. Include the trendline line equation and the R2 value.
  6. Need minor ticks and draw grid mark for both axes.
  7. Draw in the lines to intersect the y-axis and the x-axis to indicate the unknown sample’s mV reading and concentration. These lines MUST be perpendicular to the axes. These lines must reflect the unknown coordinate (56.5, 0.78)
  8. Write the concentration of the unknown sample on the graph; include units.
  9. Write the slope of the trendline line on the graph; include units.

Your set rep came to pick up your marked Practice graphs on Monday. Some of you did not do a Peer Review of a fellow student’s graph. I encourage you to practice doing Practice Reviews as many of you benefited from the errors that your reviewer spotted and made the corrections before handing the final graph in for marking.

There were some things that were not done properly.

  1. Ensure that you and your peer reviewer(s) sign the graphs that you are handing in
  2. Ensure that you hand in Graph 1 (original graph), Graph 2 (peer reviewed graph), Graph 3 (final graph that I mark)

When you see your marked lab, you will see two marks, which add to a total of 10.

  1. Graph mark: marked out of 8
  2. Peer review mark: marked out of 2

If you did not get 8 out of 8 on the graph mark, you have the option to get up to 1 mark by doing the corrections on your graph. The corrected graph will be due the following week. The corrected graph must be stapled with the marked graphs as I will need to compare it to the previous graphs.

Review the grading of the graphs.
Signatures must appear on the cover page

02-TP1graph