Gradient and intercept on data with errors

D

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
 
B

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()
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top