Forum Discussion
Accessing Chart Linear Trendline slope (m) and intercept (b) values
This is somewhat similar to another request - but I'm new to this, and got a little lost within the search system.
I have several charts with linear trendlines - the trendline neatly shows y=mx +b on the chart. I wish to access these variables (m & b) from within cells on the spreadsheet - separately for each chart - so that a system that uses these values then can automatically recalculate as the chart(s) datapoints are changed - instead of mee having to manually read and type the values into cells.
e.g. a change in a data point will change m to m', and b to b' - and I wish to use the new figures elsewhere.
Am I off-track here - should be using another function directly on the data points?
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!
