Forum Discussion
Accessing Chart Linear Trendline slope (m) and intercept (b) values
Hey,
Don't know if I understood correctly what you wish to perform but, I believe that you simply want to get the slope (m) and intercept (b) directly from the data and not from the chart.
To get the slope you can use =SLOPE(y's_value_range,x's_value_range)
To get the intercept you can use =INTERCEPT(y's_value_range,x's_value_range)
You can select the ranges directly from the cells in your spreadsheet, without inserting a linear trendline to see the equation.
Many thanks for your very prompt reply.
I probably should have done more research first - I have now tried using LINEST - and got the cell data ok - the only thing I noticed, was that LINEST gave a VALUE! fault, if there were gaps in the data range. The TRENDLINE on the chart seemed to handle gaps perfectly well.
I'll try your suggested intercept and slope methods, and see how I go. The results from these may work better for my data column layout - with regard to relative referencing (write once, drag for each column).
Many thanks!
I hope my suggested method works then! Have tried again and I can see that SLOPE() and INTERCEPT() handle gaps beautifully. I guess they simply disregard data points whenever a Y, X or both values are empty, which seems good.
Ok - so I tried your suggestion on my actual data - with gaps.
Many thanks - this was EXACTLY what I was looking for - works perfectly.
For your interest, I am calibrating a 4-channel actively-filtered 4-20mA isolated receiver - the calibration data output is used to 'tune' the raw gain and offset figures in the microprocessor.
Also tried the RSQ function (R2) - got figures exceeding 0.9999999 on all 4 channels - more than 7 decimal places - wow, that's linear!
BTW - I keep being asked to save this using the SAVE button. Where on earth is this button?