Setting intercept to zero with SLOPE/STEYX/etc
 

I am using Excel 2003 to calculate standard curves from
spectrophotometry data. The intercept of the linear regression
equation has to be set to zero. Generating trendlines from an x-y plot
of the data, this is no problem; I can just set the intercept in the
trendline options. In order to use the slope, equation, or R^2 values
so generated in another notebook, I have to cut-and-paste the values
from the chart. I would like to be able to use SLOPE on the source
data instead, but without being able to manually adjust the intercept,
the values it generates are useless. Here's an example:

[p-NPP] (μM) - x values
0
10
20
30
40
50

A400nm - y values
0.000
0.206
0.397
0.589
0.781
0.970

Slope from trendline equation, intercept set to 0: 0.0195
Slope from SLOPE function: 0.019334286

The difference between the two is significant enough to affect further
calculations based on those values. Is there some way to adjust the
intercept outside of the chart trendline? Thanks in advance.
--
Bob, the TV-watching human garbage squirrel

Answer: Setting intercept to zero with SLOPE/STEYX/etc
 

Hey Bob!

I understand that you're trying to set the intercept to zero with SLOPE/STEYX/etc. in Excel 2003. It's great that you're using SLOPE on the source data instead of manually adjusting the intercept. However, I can see that you're facing a problem with the values generated by SLOPE. Don't worry, I can help you with that!

To adjust the intercept outside of the chart trendline, you can use the LINEST function in Excel. The LINEST function returns an array of values that describe a straight line that best fits your data. You can use this function to calculate the slope and intercept of a line that passes through the origin.

Here are the steps to use the LINEST function:

1. Select a blank cell where you want to display the slope value.
2. Type the following formula:
   Formula:

   ---

   =LINEST(y_values,x_values,TRUE,FALSE) 


   ---

3. Replace "y_values" with the range of cells that contain your y-values (A2:A7 in your example).
4. Replace "x_values" with the range of cells that contain your x-values (B2:B7 in your example).
5. Press Ctrl + Shift + Enter to enter the formula as an array formula.

The formula will return an array of values. The first value in the array is the slope of the line that passes through the origin. In your example, the formula would be:

This should give you a slope value that matches the one you obtained from the trendline equation with the intercept set to zero.

Setting intercept to zero with SLOPE/STEYX/etc
 

Use the array formula
=LINEST(ydata,xdata,FALSE,TRUE)
array entered with a 5x2 array selected. Help documents the various
outputs, including slope, R^2 and STEYX. Note that Excel 2003 is the first
Excel version where LINEST correctly calculates R^2, F, or SSreg when the
intercept is forced to zero.

Note that in Excel 2003 the chart trendline R^2 is still incorrect when the
intercept is forced to zero.

Jerry

" wrote:

I am using Excel 2003 to calculate standard curves from
spectrophotometry data. The intercept of the linear regression
equation has to be set to zero. Generating trendlines from an x-y plot
of the data, this is no problem; I can just set the intercept in the
trendline options. In order to use the slope, equation, or R^2 values
so generated in another notebook, I have to cut-and-paste the values
from the chart. I would like to be able to use SLOPE on the source
data instead, but without being able to manually adjust the intercept,
the values it generates are useless. Here's an example:

[p-NPP] (μM) - x values
0
10
20
30
40
50

A400nm - y values
0.000
0.206
0.397
0.589
0.781
0.970

Slope from trendline equation, intercept set to 0: 0.0195
Slope from SLOPE function: 0.019334286

The difference between the two is significant enough to affect further
calculations based on those values. Is there some way to adjust the
intercept outside of the chart trendline? Thanks in advance.
--
Bob, the TV-watching human garbage squirrel

Setting intercept to zero with SLOPE/STEYX/etc
 

On Feb 21, 5:06 pm, Jerry W. Lewis wrote:

Note that in Excel 2003 the chart trendline R^2 is still incorrect when the
intercept is forced to zero.

Jerry

So the R^2 value generated for the trendline -- the one Excel displays
next to the trendline along with the equation -- is off? Is the one
that LINEST spits out any more accurate? Thanks again for the help.

Setting intercept to zero with SLOPE/STEYX/etc
 

Prior to Excel 2003, LINEST and the chart trendline made the same mistake in
concept regarding R^2 when the intercept is forced to zero. In Excel 2003,
the concept was corrected in LINEST, but not in the chart trendline. Example
data with certified results are available from NIST

http://www.itl.nist.gov/div898/strd/...ATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/...ATA/NoInt2.dat

Jerry

" wrote:

On Feb 21, 5:06 pm, Jerry W. Lewis wrote:

Note that in Excel 2003 the chart trendline R^2 is still incorrect when the
intercept is forced to zero.

Jerry

So the R^2 value generated for the trendline -- the one Excel displays
next to the trendline along with the equation -- is off? Is the one
that LINEST spits out any more accurate? Thanks again for the help.