2

I have a spreadsheet with a number of graphs on, and I have displayed the trend-lines / regression lines on these graphs. These are not simple linear regression lines, but are high order polynomial ones.

Is there any way that I can use the equations of these regression lines in my formulae, without hand-coding all the co-efficients? The equations are constantly changing, and I don't want to have to rewrite all my formulae every time I update the chart.


EDIT: The co-efficients vary because I am still adding data, and the regression lines are becoming more accurate as I add more data. There won't be a limit on the data - it will keep coming in, which is why I don't want to have to rewrite all the formulae each time.

1
  • This should be possible if the functions are continuous. Are you just saying that the coefficients of the powers in the function definition vary? Presumably they vary according to another known funtion? Perhaps you could give an example?
    – mas
    Sep 15, 2009 at 7:26

3 Answers 3

1

The built in functions only cover the coefficients for a straight line.

In the past I have done this by using the matrix functions in Excel and the standard Least Squares fitting method

I stuck a quick demo sheet together, you can download here. This is designed for fitting a Cubic, but can be expanded to fit any other you wish. In terms of tracking new data, you can expand the range of data as far as you like and it will recalculate as you add new data.

0
0

I don't know specifically, but have a look at the object model of the graph object in VBA to see if you can access the regression equation inside VBA. Then you can put the coefficients back into cells.

1
  • The only programatic access to the equations are as a string. Example: ?Worksheets("Sheet1").ChartObjects(1).Chart. SeriesCollection(1).Trendlines(1).DataLabel.Text. You'd still hve to parse the string. There are better techniques - see @JDunkerley Oct 9, 2009 at 16:31
-1

Polynomial n-grade regression in one variable ~ Linear regression in n variables

where ~ denotes "equivalent to".

So, in your source data table, add columns to calculate powers of "independent" variable values, and then apply formula to calculate coefficients for linear regression with many variables.

2
  • Care to give a simple example? Can't quite understand what you are talking about there.
    – a_m0d
    Sep 20, 2009 at 23:37
  • Put y values in A1:A10, put 1s in B1:B10 (the "intercept" coefficient), put x values in C1:C10, put x^2 values in D1:D10. Then, put the following (matrix) formula in E1:E3: =MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(B1:D10),B1:D10)),TRANSPOSE(B1:D10)),A1:A10) as said in en.wikipedia.org/wiki/Linear_regression These are your coefficients.
    – Toc
    Sep 22, 2009 at 14:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .