Regression gives slope of zero when x values smaller than 10^-7

[David H]

I'm trying to do a simple linear regression (y = mx + b) in Excel 2007 using
the Regression function in the Data Analysis Tool Pack. When the x values
are smaller than about 1x10^-7 the output of the calculation is wrong: the
displayed slope is zero, although the trendline equation shows a non-zero
slope. For example, with the data (1e-7,1) ,(3e-7,2), (5e-7,7) ,(7e-7,10)
the Regression calculation produces a zero slope while the trendline shows a
slope of 1.6x10^7. But if I multiply the x & y values by 10 and then rerun
the Regression then the Regression output shows a slope of 1.6x10^7. Why is
Regression malfunctioning when the x values are small, and how can I prevent
this from happening?

 

[David H]

Jan,
scaling the x values is indeed one of the things I've done in my own work,
but it's a less than ideal solution for my students, many of whom are
relatively inexperienced with Excel and somewhat math-phobic (sigh). And the
problem doesn't seem to be related to the large difference in x and y values,
because if I scale BOTH x and y the correct slope is displayed.

 

[Jerry W. Lewis]

The coefficient of variation (STDEV/AVERAGE) of x values is more relevant for
numerical difficulty than the difference in magnitude of x and y values.

In Excel versions prior to 2007, the ATP regression tool appears to use
LINEST as its calculation engine; what does LINEST do for this problem in
2007? In 2003, LINEST agrees with SLOPE and INTERCEPT to 15 decimal digits.

Jerry