Results 1 to 4 of 4
Thread: XY scatter chart (2000)

20030224, 21:58 #1
 Join Date
 Feb 2003
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
XY scatter chart (2000)
I am attempting to construct an XY scatter chart for some data in my chemistry course. Attached is a copy of the worksheets that I am working on thus far. I have concentrated most of my time on sheet 2 columns N and O. I am to calculate the mean absorbance (column I), calculate the %B/Bo (column O) and construct a standard curve by plotting %B/Bo on the yaxis vs log(conc) on the xaxis using Excel. Somehow, after that, I am to see interpolate using the standard curve. If anyone can help me at least get the standard curve, that would be great!
Thanks for helping. Instructions for LINEST can be found at the following site: http://www.colby.edu/chemistry/PChem/notes/linest.pdf.

20030224, 23:37 #2
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XY scatter chart (2000)
Hang in there TXTucker...someone will likely jump on it sooner or later.
NannetteNMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20030225, 01:05 #3
 Join Date
 Jan 2001
 Location
 Newcastle, New South Wales, Australia
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XY scatter chart (2000)
1. The three points are clearly nonlinear on the log scale. Usually this is why you use a log scale  to make something that is nonlinear into something that is linear. But assume that your real data are suitable for a loglinear scale (log on X and linear on Y). There are three ways you can do it.
2. You can create an ordinary XY scatter plot using "conc" (not Log(conc)) on the X and %B/Bo on the Y. Then select the X axis and choose Format X axis. From the Scale tab, check the box for Log. This changes the chart to a loglinear XY scatter plot. Select the curve and either rightclick and choose Add Trendline, or from the menu choose Chart AddTrendline. Select the kind of curve you want; from the options tab choose to display the equation and the Rsquared. Then the trendline through the points is now another range that can be formatted as you wish. The equation is in a text box, that you probably have to drag elsewhere to see easily. This is a quick & dirty way of seeing whether the curve through the data is any good.
3. From the data themselves: I use the two functions, Slope() and Intercept() rather than LineEst(). I moved your data down, so that N4:N6 [log(conc)]contain the numbers 1,0.07 and O4:O6 [%B/Bo] contain 62.8, 53.7, 32.4. Then in P2 goes the formula =INTERCEPT(O4:O6,N4:N6); in P3 goes the formula =SLOPE(O4:O6,N4:N6) and in P4 the formula =$P$2+$P$3*N4 which is copied to P5 and P6. Then column P is the calculated values of %B/Bo.
If you need to interpolate/ extrapolate to other values, then the formula
=$Intercept + $Slope*(log(conc)) will give the result.
There are also functions like RSQ() that give results to test the suitability of the regression.
4. You can use the Tools Data Analysis Regression command to do the regression also. If you don't have Data Analysis on the Tools menu, use ToolsAddInsAnalysis ToolPak to add it. This way puts all the regression statistics into a new worksheet.
Does this make sense?
ruth C

20030226, 22:28 #4
 Join Date
 Feb 2003
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XY scatter chart (2000)
Thank you so much for taking the time to explain this to me. It was very helpful.