Linest/slope functions with with different data ranges

[Pat]

I want to get the slope for different data sets compared to the same X's. As
my samples don't necessarily have the same start date, the slope/linest
functions consider empty cells as part of the sample, and give me a wrong
result. Is there a way to force the functions to take into account only the
common data sets ?

 

[Jerry W. Lewis]

Slope and LINEST behave differently with empty cells. LINEST does not permit
empty cells or non-numeric values in the range, and will return an error if
they are present. Slope and Intercept do permit empty cells and non-numeric
values in the range and will ignore them if present.

If you have Excel 2003 be sure to patch to at least SP
http://support.microsoft.com/kb/834691

Otherwise, if you are detecting different behavior with Slope and Intercept,
then you are almost certainly passing an array formula to the function, which
is coercing empty cells to zeros. In that case, wrap your array formula
(inside the SLOPE call) in an IF statement, such as
IF(ISNUMBER(range),formula)

Jerry