# Thread: XY scatter chart (2000)

1. ## 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 y-axis vs log(conc) on the x-axis 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.

2. ## Re: XY scatter chart (2000)

Hang in there TXTucker...someone will likely jump on it sooner or later.
Nannette

3. ## Re: XY scatter chart (2000)

1. The three points are clearly non-linear on the log scale. Usually this is why you use a log scale - to make something that is non-linear into something that is linear. But assume that your real data are suitable for a log-linear 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 log-linear XY scatter plot. Select the curve and either right-click 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 R-squared. Then the trend-line 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 ToolsAdd-InsAnalysis ToolPak to add it. This way puts all the regression statistics into a new worksheet.

Does this make sense?
ruth C

4. ## Re: XY scatter chart (2000)

Thank you so much for taking the time to explain this to me. It was very helpful.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•