Gradient and intercept on data with errors
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 |
Gradient and intercept on data with errors
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() -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Dave Curtis" wrote in message .. . 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 |
Gradient and intercept on data with errors
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 |
All times are GMT +1. The time now is 08:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com