Gradient and intercept on data with errors

[Dave Curtis]

Hi,

When I plot a calibration graph (concentration vs response) and add a trend
line, Excel will show the gradient and intercept of the line. I normally
capture these in a couple of cells, either using the SLOPE and INTERCEPT
functions, or using LINEST.

Sometimes, however, there is a data point missing, and although the chart
will continue to give the gradient and intercept, the functions give either
#N/A or #VALUE errors.

I've tried getting the missing data points to return #N/A or "", but can't
seem to get it to work.

Any ideas, please?

Thanks

Dave

url:http://www.ureader.com/gp/1042-1.aspx

 

[Bernard Liengme]

I know it is ugly but here is the answer:
=IF(ISERROR(LINEST(B2:B5,A2:A5)),NA(),LINEST(B2:B5,A2:A5))
But SLOPE and LINEST should return NA() if a cell has =NA()

 

[Dave Curtis]

Thanks Bernard,

Doesn't quite do what I need, though.
I realise that LINEST and SLOPE should return #N/A if any cells in the range
contain an error, but the chart will still give me values for the gradient
and intercept, and it's these values I need to generate in cells.

Dave

url:http://www.ureader.com/msg/104236284.aspx