Chemistry EE excel problem.

[nntl]

So, in my EE, I'm graphing some concentration-time graphs. The problem is that, the last point on one of my graphs is anomalous (see the picture).

 

On Excel, how do I add a trendline that ignores that anomalous point while keeping that point on the graph?

 

 

 

[King112]

I'm not an Excel genius, but I'm not sure that's possible. I guess if you removed the point all together, it would work. but I don't think Excel allows you to skip one particular point or anything like that.

So either you could delete the anomaly all together, or maybe you could put the trendline in with it, and explain the weak correlation?

Thats what I would do anyways.

[Vioh]

So, in my EE, I'm graphing some concentration-time graphs. The problem is that, the last point on one of my graphs is anomalous (see the picture).

 

On Excel, how do I add a trendline that ignores that anomalous point while keeping that point on the graph?

 

Yes, in fact you can. You just have to plot 2 different sets of data on the same scatter graph (one for the good data, and one for the anomaly). You can then put a regression line through the first data-set to figure out the relationship between concentration and time. The steps to do are as follows (Note that italic words are the buttons on Excel):

1. Click Insert → Choose Scatter graph
2. Right click on the graph → Choose Select Data
3. Click Add → Select the range for your X-values (which are basically values for the time), and your Y-values (which are the corresponding concentrations) by highlighting your data on the screen. Remember that you must ignore the anomalous value here, as this will be your first data-set (i.e. Series 1)
4. Continue to click Add → Select the range for your X-values (which is the time that the anomaly occurs), and your Y-values (which is the anomalous concentration) by highlighting your data on the screen. This will be your second data-set (i.e. Series 2) that takes care of the anomaly
5. Click OK to go back to the main screen → Right click on any point on the graph (not the anomalous one) → Choose Add Trendline → Select appropriate type of trendline
6. Click on the anomalous point → Click Layout on Excel Tab → Choose Format Selection to change the appearance of that point
7. To make you graph looks nice, you can:

• Click on the little box on the right-hand-side of the graph (where it says Series 1, Series 2, Log. (Series 1) and delete it
• Click on the y-axis → Click Layout on Excel Tab → Choose Axis Title for the axis
• Do the same thing for the x-axis

Note that I chose Logarithmic graph for the trendline; that was the reason why I had to put the first value for the time to be 0.1 minutes (because a logarithmic graph is undefined when t=0). Here's the Excel version:

Graph & Anomaly.xlsx

 

Edited April 23, 2015 by Vioh