Linest Function

  • Thread starter Thread starter Alan Cocks
  • Start date Start date
A

Alan Cocks

I have up to 400 rows of data imported into a work sheet coming from a data
logging device.
The work sheet has functions to test each set of data which forms a new
column with the results of the calculation and gaps for the invalid data. I
can plot the valid data up on a scatter graph using NA() for the invalid
data.

I need to put a trend line (straight line) through the valid data and have
it return to me the slope of the line and the y intercept. I am not sure
how to achieve this when there are gaps in the data.

Alan
 
LINEST does not support gaps in data.

Either add a trendline to your X-Y (Scatter) chart of the data, and copy
the coefficients, or use
=SLOPE(IF(ISNUMBER(y),y,""),IF(ISNUMBER(x),x,""))
=INTERCEPT(IF(ISNUMBER(y),y,""),IF(ISNUMBER(x),x,""))
which must be array entered (Ctrl-Shift-Enter)

If you need the additional statistics that LINEST provides, you will
either have to delete all the NA rows, or use a similar IF() based
approach using the formulas from
http://groups.google.com/[email protected]

Jerry
 
Thanks a lot Jerry,

The SLOPE & INTERCEPT functions in your formulas do exactly what I require.

I really do appreciate the help you give me from time to time.
Makes me look good in front of my peers!

Alan.
 
Back
Top