Kung Foo Masters Only

[Alex A]

Whoever can answer this one is the Master. I have
successfully solved for and used the coefficients of a
trendline through VBA and formulated the linear
regression one period forward. Now I want to do the same
thing with a second order polynomial trendline.

Solving for Y with the linear equation went like this:
'Variables
x1 = IVSLH2Range.Row + 1
x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 2
'Coefficients
m = Application.WorksheetFunction.Slope(Range(Cells(x1,
5), Cells(x2, 5)), Range(Cells(x1, 1), Cells(x2, 1)))
x = (Year(Date) + 1 - 2000)
b = Application.WorksheetFunction.Intercept(Range(Cells
(x1, 5), Cells(x2, 5)), Range(Cells(x1, 1), Cells(x2, 1)))
'Solve for Y
y = m * x + b

Now, solving for Y with a second degree polynomial
equation involves:

y = (c2 * x^2) + (c1 * x ^1) + b

But I need to be able to solve for c1, c2, and b. This
is where I am not sure how to proceed. And if I do how
do I get VBA to crunch that equation.

How do I interpret this???:
c2: =INDEX(LINEST(y,x^{1,2}),1)

C1: =INDEX(LINEST(y,x^{1,2}),1,2)

b = =INDEX(LINEST(y,x^{1,2}),1,3)


(Thanks to John Walkenbach and an anonymous user that got
me this far.)

 

[Guest]

Ale

A couple of options

1) If you have the graph with the equation, then you can grab the equation, parse it to get the variables and progress. The following should give you the idea from the graph
Sub aaa(
aa = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1).DataLabel.Tex
c1start = InStr(1, aa, "=", 1) +
x2start = InStr(1, aa, "x2", 1
xstart = InStr(x2start + 1, aa, "x", 1


c1 = Mid(aa, c1start, x2start - c1start
c2 = Mid(aa, x2start + 2, xstart - x2start - 2
b = Mid(aa, xstart + 1

'MsgBox c
'MsgBox c
'MsgBox
MsgBox c1 * 2 ^ 2 + c2 * 2 +

End Su

2) If you use the standard 1,2,3... as one variable and 1,4,9 as a second (ie they are x and x^2) then you can again use LINEST to get the variables in the way that you have done

Ton

----- Alex A wrote: ----

Whoever can answer this one is the Master. I have
successfully solved for and used the coefficients of a
trendline through VBA and formulated the linear
regression one period forward. Now I want to do the same
thing with a second order polynomial trendline

Solving for Y with the linear equation went like this
'Variable
x1 = IVSLH2Range.Row +
x2 = IVSLH2Range.Row + intRowCountIVSLH2 -
'Coefficient
m = Application.WorksheetFunction.Slope(Range(Cells(x1,
5), Cells(x2, 5)), Range(Cells(x1, 1), Cells(x2, 1))
x = (Year(Date) + 1 - 2000
b = Application.WorksheetFunction.Intercept(Range(Cell
(x1, 5), Cells(x2, 5)), Range(Cells(x1, 1), Cells(x2, 1))
'Solve for
y = m * x +

Now, solving for Y with a second degree polynomial
equation involves

y = (c2 * x^2) + (c1 * x ^1) +

But I need to be able to solve for c1, c2, and b. This
is where I am not sure how to proceed. And if I do how
do I get VBA to crunch that equation

How do I interpret this???
c2: =INDEX(LINEST(y,x^{1,2}),1

C1: =INDEX(LINEST(y,x^{1,2}),1,2

b = =INDEX(LINEST(y,x^{1,2}),1,3


(Thanks to John Walkenbach and an anonymous user that got
me this far.

 

[Alex A]

Tony, can you elaborate on this. I understand parsing
the equation of the graph, and that is a great idea. But
I am having problems still getting the VBA to set the
XValues property of the Series Class. See my post
above "Assigning a Range to".

So in case I can't get that working I would really like
to access the coefficients through VBA directly. I just
need to better understand what you said. Are you saying
that there is a LINEST function that will help me?
Because I don't understand what these lines are saying (I
got them from a website):
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

What do you mean by if I am using the standard 1,2,3?
And using the LINEST function?

Thanks

Tony Wrote:
2) If you use the standard 1,2,3... as one variable and
1,4,9 as a second (ie they are x and x^2) then you can
again use LINEST to get the variables in the way that you
have done.

Tony

 

[Guest]

Alex

Set your spreadsheet up so that you have:
Months July - June in the range A1:L1
Values 10,12,14,11,8,5,7,11,15,18,20,24 in the range A2:L2
Values 1,2,3,4,5....12 in the range A3:L3
Values 1,4,9,16,25....144 in the range A4:L4 (ie the square of the values in row 3.

You can then use the following to extract the c1, c2 and b values from this data.
c1 = WorksheetFunction.Index(WorksheetFunction.LinEst(Range("a2:l2"), Range("a3:l4")), 1)
c2 = WorksheetFunction.Index(WorksheetFunction.LinEst(Range("a2:l2"), Range("a3:l4")), 2)
b = WorksheetFunction.Index(WorksheetFunction.LinEst(Range("a2:l2"), Range("a3:l4")), 3)

MsgBox c1 & ", " & c2 & ", " & b

If you graph the data (only rows 1 and 2) then insert a polynomial trend line and show the equation on the chart, then you should find that they are the same.

Problems will occur if you use the months in the form Jul 03, etc (ie they are proper dates) as the graph will use the dates as the basis for the graph function.

Hope this explains.

Tony


----- Alex A wrote: -----

Tony, can you elaborate on this. I understand parsing
the equation of the graph, and that is a great idea. But
I am having problems still getting the VBA to set the
XValues property of the Series Class. See my post
above "Assigning a Range to".

So in case I can't get that working I would really like
to access the coefficients through VBA directly. I just
need to better understand what you said. Are you saying
that there is a LINEST function that will help me?
Because I don't understand what these lines are saying (I
got them from a website):
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

What do you mean by if I am using the standard 1,2,3?
And using the LINEST function?

Thanks

Tony Wrote:
2) If you use the standard 1,2,3... as one variable and
1,4,9 as a second (ie they are x and x^2) then you can
again use LINEST to get the variables in the way that you
have done.

Tony

 

[Alex A]

Tony, so then the
b =INDEX(LINEST(y,x^{1,2}),1,3)
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)

From the website translates to your
b = WorksheetFunction.Index(WorksheetFunction.LinEst(Range
("a2:l2"), Range("a3:l4")), 3)

Where the 'y' is the Y Series Range from the spreadsheet,
the 'x^{1,2}' is the X Series Range & the X Squared Range
and then the '1,3' just needs to be 3 (for b).

And the same for C2 and C1 except the last part is 2 and
1 respectively. Right?

So if I understand what you are saying then I just need
to create an X squared range next to my X series range
and grab them together as one Range, and you my friend
have just leveled up another degree in your Black Belt!

Thanks a million!
Alex.

p.s.
Would you mind telling me another way to set the XValues
Property and Values Property dynamically because the way
I am trying it only works with static ranges. Please see
above post Assigning a Range to.