Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bug with gradient fills and max scale | Charts and Charting in Excel | |||
Calculating slope, intercept, LOGEST with empty cells in data | Excel Worksheet Functions | |||
Cell Gradient | Excel Discussion (Misc queries) | |||
Gradient of line chart | Charts and Charting in Excel | |||
plot 10 data pts in least square fit to find slope and intercept | Excel Worksheet Functions |