Extract formula from Text box

[Guest]

"GAD" Tom.........

That is really neat......."and more accurate to boot"?..........it don't get
much better than that.......

Thanks muchly,
Vaya con Dios,
Chuck, CABGx3

Regardless of the fact that you are using LINEST.
If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
quoted by David Braden (Phd, Statistics related) stating that Linest isn't
as good at formulating the formula for the trendline as the code that builds
the formula in the trendline itself. So while Linest will probably do the
job, this code will put the formula you need in the cell. It does use
maximum precision - not just the precision as displayed.

As written, select the cell for which you want to make a forecast. (the
cell with a ? in your example). and run the code. It will deposit a
formula referencing the cell to the left as the source for the value of X.
It will also use maximum precision. (this isn't as complex or as flexible as
Dave's code nor does it require breaking the string up as Peter T's code
does. it is much more flexible than Peter's, handling missing orders and
higher/lower order ).

Sub GetFormula1()
Dim sFormula As String
Dim ser As Series
Dim tLine As Trendline
Dim cht As Chart, sNum As String
Set cht = ActiveSheet.ChartObjects(1).Chart
Set ser = cht.SeriesCollection(1)
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
If tLine.DisplayEquation Then
sNum = tLine.DataLabel.NumberFormat
tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
sFormula = tLine.DataLabel.Text
tLine.DataLabel.NumberFormat = sNum
sFormula = Application.Substitute(sFormula, _
"y = ", "")
sFormula = Application.Substitute(sFormula, _
"x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
sFormula = Application.Substitute(sFormula, _
"^ ", " ")
ActiveCell.Formula = "=" & sFormula
End If
End If
End Sub

--
Regards,
Tom Ogilvy

Hi Ron.......
You're right , of course, as you guys usually are.......it just takes me
awhile sometimes to get to the point where I recognize it.....<g>

I talked with my user this morning and he related that he actually did want
the right answer and not just the formula in the text box like he told me
originally......so, I finally got the LINEST thing working, and the numbers
it produced was what he was actually looking for.......so, as long as he is
satisfied, the story has a happy ending......

I really appreciate you hanging in there with me to the end of this thing...

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

I talked with my user this morning and he related that he actually did want
the right answer and not just the formula in the text box like he told me
originally......so, I finally got the LINEST thing working, and the numbers
it produced was what he was actually looking for.......so, as long as he is
satisfied, the story has a happy ending......

Unfortunately, there are certain conditions in which LINEST will not give the
correct answer. The formula used in the TRENDLINE box is said to be more
"robust". I think that means it will give correct answers in situations where
LINEST gives an incorrect answer.

Since your user just wants the extrapolated answer, I would recommend using the
TLEval UDF from the code which I sent you. It uses the chart Trendline
formula, but only gives the answer, instead of the formula.


--ron

 

[Tom Ogilvy]

ActiveCell.Formula = "=" & sFormula
followed by
ActiveCell.Formula = ActiveCell.Value

will quiet that concern.

--
Regards,
Tom Ogilvy

 

[Peter T]

Hi Chuck,

With Ron's NumberFormat suggestion, if you insert into my previous macros as
follows:

..DisplayEquation = True
..DataLabel.NumberFormat = "0.000000000000000E+00"
sEqu = .DataLabel.Text

You should get the same accuracy and results as LINEST, and same as returned
in the other macros in this thread.

Regards,
Peter Thornton

 

[Peter T]

Hi Tom,

it is much more flexible than Peter's, handling missing orders and
higher/lower order ).

I don't quite follow "handling missing orders and higher/lower order". There
are some differences in implementation between our macros, but are they
fundamentally different ?

Small point, trying your macro in XL2K, while an worksheet chart is active
it fails trying to return the ActiveCell. Error 91

Just about to post under Ron's concerning points you've both made, would
also welcome your comments.

Regards,
Peter T

Regardless of the fact that you are using LINEST.
If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
quoted by David Braden (Phd, Statistics related) stating that Linest isn't
as good at formulating the formula for the trendline as the code that builds
the formula in the trendline itself. So while Linest will probably do the
job, this code will put the formula you need in the cell. It does use
maximum precision - not just the precision as displayed.

As written, select the cell for which you want to make a forecast. (the
cell with a ? in your example). and run the code. It will deposit a
formula referencing the cell to the left as the source for the value of X.
It will also use maximum precision. (this isn't as complex or as flexible as
Dave's code nor does it require breaking the string up as Peter T's code
does. it is much more flexible than Peter's, handling missing orders and
higher/lower order ).

Sub GetFormula1()
Dim sFormula As String
Dim ser As Series
Dim tLine As Trendline
Dim cht As Chart, sNum As String
Set cht = ActiveSheet.ChartObjects(1).Chart
Set ser = cht.SeriesCollection(1)
If ser.Trendlines.Count = 1 Then
Set tLine = ser.Trendlines(1)
If tLine.DisplayEquation Then
sNum = tLine.DataLabel.NumberFormat
tLine.DataLabel.NumberFormat = "0.00000000000000E+00"
sFormula = tLine.DataLabel.Text
tLine.DataLabel.NumberFormat = sNum
sFormula = Application.Substitute(sFormula, _
"y = ", "")
sFormula = Application.Substitute(sFormula, _
"x", "*" & ActiveCell.Offset(0, -1).Address(0, 0) & "^")
sFormula = Application.Substitute(sFormula, _
"^ ", " ")
ActiveCell.Formula = "=" & sFormula
End If
End If
End Sub

--
Regards,
Tom Ogilvy

Hi Ron.......
You're right , of course, as you guys usually are.......it just takes me
awhile sometimes to get to the point where I recognize it.....<g>

I talked with my user this morning and he related that he actually did want
the right answer and not just the formula in the text box like he told me
originally......so, I finally got the LINEST thing working, and the numbers
it produced was what he was actually looking for.......so, as long as he is
satisfied, the story has a happy ending......

I really appreciate you hanging in there with me to the end of this thing...

Vaya con Dios,
Chuck, CABGx3

 

[Peter T]

Hi Ron,

Unfortunately, there are certain conditions in which LINEST will not give the
correct answer. The formula used in the TRENDLINE box is said to be more
"robust". I think that means it will give correct answers in situations where
LINEST gives an incorrect answer.

Tom mentioned similar and I also recall reading about this. However an
observation:

All the macros in this thread (mine with change of number format) return
exactly the same results as LINEST to 14dp, based on Chuck's original data
set. With this set, and all others I've tried that "make sense" with 3rd
order polynomial, suggests that the chart's polynomial trendline uses the
exact same calculation as LINEST.

Chuck's data:
=LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
array entered in 4 cells in a row

Would you have an example of data where a polynomial trendline differs from
LINEST, as in "there are certain conditions ...."

Regards,
Peter T

 

[Tom Ogilvy]

Small point, trying your macro in XL2K, while an worksheet chart is active

it fails trying to return the ActiveCell. Error 91

Guess in your eagerness, you didn't read the directions. Of course I could
make it bullet proof, but that wasn't the point.

As written, select the cell for which you want to make a forecast. (the
cell with a ? in your example). and run the code.

But thanks for emphasizing again for the OP that the directions should be
followed.

You seem to see my comment as criticism. There was no criticism. You built
a focused solution to help CLR. That's great. I was just positioning the
code I offered as between the complexity/functionality of yours and David
Braden's.

I didn't go back and analyze your code line by line to write a critique so
perhaps I overstated the limitations. I was working from a previous reading
somewhat earlier. I was thinking it was a bit more restrictive. Your code
should handle lower order and missing terms fine, but not higher order
(.Order = 4 for example). Plus you build the trendline with fixed
attributes, rather than use that established by the user( less flexible).
You sent a new post just before this expanding the precision, so that wasn't
present when I posted.

No, the implementations are not conceptually different, but I would still
say that mine is more flexible (although I overstated that) and at the time
had higher precision. Those are the two things which I considered that
placed it between yours and David Braden's in my thinking. Sorry you seem
to think that is criticism - no criticism intened. It was meant to just help
the OP see why it was posted rather than duplicating what others had posted.

--
Regards,
Tom Ogilvy

Hi Tom,

it is much more flexible than Peter's, handling missing orders and
higher/lower order ).

I don't quite follow "handling missing orders and higher/lower order". There
are some differences in implementation between our macros, but are they
fundamentally different ?

Small point, trying your macro in XL2K, while an worksheet chart is active
it fails trying to return the ActiveCell. Error 91

Just about to post under Ron's concerning points you've both made, would
also welcome your comments.

Regards,
Peter T

Regardless of the fact that you are using LINEST.
If you note in the comment in the Code Jerry Lewis (Phd, Statistics) is
quoted by David Braden (Phd, Statistics related) stating that Linest isn't
as good at formulating the formula for the trendline as the code that builds
the formula in the trendline itself. So while Linest will probably do the
job, this code will put the formula you need in the cell. It does use
maximum precision - not just the precision as displayed.

As written, select the cell for which you want to make a forecast. (the
cell with a ? in your example). and run the code. It will deposit a
formula referencing the cell to the left as the source for the value of X.
It will also use maximum precision. (this isn't as complex or as

flexible

 

[Tom Ogilvy]

from Microsoft KB article:
(http://support.microsoft.com/default.aspx?scid=kb;en-us;828533)
Microsoft has made extensive changes to the LINEST function to correct
incorrect formulas that are used when the regression line must go through
the origin. The changes also pay more attention to issues that involve
collinear predictor variables. Because of these extensive improvements, this
article focuses more on the improvements and less on instructing users about
how to use LINEST.

Jerry Lewis and David Braden had done a lot of research on the statisical
functions and I believe were major champions/driving forces for changes, som
of which have benn made in xl2003.

 

[Peter T]

Hi Tom,

No, I really didn't take anything you said as criticism, my comments were
intended at face value. But had you wanted to I would not in the least mind,
quite the reverse - same goes for anything I may post in the future.

My main confusion concerned "missing orders and higher/lower order" which I
took to relate to polynomials, but now see referred to precision (lacking in
my original macro).

Thanks for the considered reply.

Regards,
Peter T

 

[Ron Rosenfeld]

Hi Ron,


Tom mentioned similar and I also recall reading about this. However an
observation:

All the macros in this thread (mine with change of number format) return
exactly the same results as LINEST to 14dp, based on Chuck's original data
set. With this set, and all others I've tried that "make sense" with 3rd
order polynomial, suggests that the chart's polynomial trendline uses the
exact same calculation as LINEST.

Chuck's data:
=LINEST({7;10;12;16;26},{5610;11550;16830;22110;26600}^{1,2,3})
array entered in 4 cells in a row

Would you have an example of data where a polynomial trendline differs from
LINEST, as in "there are certain conditions ...."

Regards,
Peter T

Others more knowledgeable than I (Braden and Lewis and Harlan) have discussed
this extensively in the past, so I'm sure there is data available via a Google
search. Look for something like LINEST error in the newsgroup. The explanation
there will be much more detailed than any I could come up with.

For the particular example posted, the differences are minimal, and the
difference in computation of the new 'y' for the OP's new 'x' of 33660 is only
5.258016244624740E-13.

In Excel 2003, there was work done on the Excel statistical functions, with
improvement in LINEST (as well as other functions). For some reason, I thought
the OP was using XL97, but I may have him confused with someone else.


--ron

 

[CLR]

"Whew"............you guys left me in the dust a loooong time ago
<g>...........my problem is solved tho, my user is happy, and I really
really do appreciate all the time and effort you all have put in to help and
educate me...........without these newsgroups some of us would truly be
lost.

By the way, you were right again.........I am using XL97 at work where I
have this problem.

Thanks again to everyone...........
Vaya con Dios,
Chuck, CABGx3

 

[Peter T]

Tom - thanks for the link. Not exactly bed time reading <g> but I've tried
to get to grips with it.

Ron - thanks also for your followup. As you say there is some detail of
LINEST problems in this ng. Presumably all statisticians are fully aware I
but don't suppose it does any harm to have the issue raised from time to
time.

Chuck - did a quick test in XL97 and get same results as in XL2K. Don't
think you should have any problems with LINEST with the particular data set
you have.

Clearly though there may be some sets that error with pre XL2003 and, from
what I interpret, how the function is used. In some of these cases applying
common sense might indicate a problem, such as comparing calculated results
with what one might expect.

On which point neither you nor I did with the early results, eg test the
formula on known x's & y's. (which I did by prior to my second followup to
you).

Your client's original request to go with the simplified formula, at first
glance, seemed very reasonable. It gave coefficients down to very small
albeit rounded decimals. He probably only wanted a result to the nearest
whole number, even that as a rough projection. Might well have expected the
simplified formula to be good enough. But it gave a very wrong result - a
lesson!

Regards,
Peter T

 

[Guest]

A lesson indeed Peter........

Every time I come to these groups for an answer, I usually go away with more
than I was smart enough to ask for in the first place......and even extra
stuff that I can use on the next project. Obviously I know nothing about
statistics, in this instance I was only trying to help a Chemist friend with
his Excel charting problems.....(hence my insistance that I only wanted to
access the label formula) but I certainly learned a lot in the process.

Again, I want to thank you for all your time and efforts in my behalf.

Vaya con Dios,
Chuck, CABGx3