Polynimial trandline formula

[CLR]

Hi All...........

I'm not much into higher math, or charting myself, but one of my users has
drawn a Scatter Chart and added a third order polynomial trendline to try to
pick up the missing value on the high end of his data...........Excel draws
the trendline on the chart just fine, but does not give a tickmark for the
corresponding value in question, rather gives just a formula in a Label-box
which when calculated by hand will give the desired value...........problem
is, my user wants Excel to calculate the value for him.........I have
managed this by Cntrl-C copying the formula out of the label box and
breaking it apart and doing the math, but this seems like the long way
around.........I can't "select" the formula out of the label-box...........

Question is, is there any way for Excel to do this calculation, or any way I
can "grab" that formula, maybe with VBA so as to do the calcs automatically?

TIA,
Vaya con Dios,
Chuck, CABGx3

 

[Tushar Mehta]

Unless you are dealing with some rather extreme functions, the process
outline by Bernard Liengme in
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm will do the
job.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Chuck -

You can use the LINEST worksheet array function to get the coefficients
directly into the worksheet, without all that parsing of the trendline
formula. (If your data is such that LINEST produces inaccurate results,
we can suggest a way to parse the trendline formula automatically, but
for most cases, that's overkill.) A sample using LINEST for polynomial
fits is given by Bernard Liengme:

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

To find the X value where the maximum occurs, use a little calculus to
differentiate the fitted equation, and find the value of X that causes
this slope to equal zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[CLR]

Thanks Tushar..............of course this stuff is 'way over my head but I
will give it a try........

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Uh-huh........BigWord, BigWord, BigWord right back to ya Jon <g>.........I
don't have a clue what you and Tushar are talking about but I went to
Bernards site and tried his formula and I got an answer of 46+ in place of
my original 64+ doing it by hand...........so, maybe you could elaborate on
your offer of a suggestion for a "way to parse the trendline formula
automatically", .........'twould be much appreciated........

if it helps, here's the data I'm working with:

5610 7
11550 10
16830 12
22110 16
27720 26
33660 ?

Thanks,
Vaya con Dios,
Chuck, CABGx3

 

[Jon Peltier]

Chuck -

Let me suggest that neither 46 nor 64 is worth much as a prediction. The
kind of analysis Tushar and I proposed is good for interpolating points
within a range of data, but you're talking about extrapolating more than
25% beyond the range of a small number of observed data values.

Poly fits often look nice, but they reveal nothing about any underlying
mechanisms that control the shape of a curve. You have a great
correlation coefficient, which you'll get if you fit a high order
polynomial relationship to a small number of points. The smooth flowing
curve you get from the poly fit may be masking measurement error in the
data. If I ignore your last point, for example, I get a tolerable linear
fit (R^2 = 0.9811), which predicts y=21.6 at x=33660.

So what's correct? Probably none. Without knowing about the underlying
behavior of what's being measured, the trendline formulas are merely
making a swag at the value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[CLR]

Thanks Jon...........

It sounds so good when you say it! And I do appreciate you taking your time
to convey a description that even I can understand. Those are my thoughts
exactly, about the predicted values not being "probably correct" in this
instance, but not being a higher-math person, I had not the verbage to
describe my feelings.

Fortunately, in this case, mine is not to decide if the prediction method
will give the correct results or not, mine is only the Excel challange of
parsing the formula out of the TEXT box and automating the formula
calculation process for the user. With that given, if you could offer some
help to that end, I would certainly be appreciative. I'm trying to record
the sequence into a macro, but no joy yet. It seems to work, but only for a
time or two, and then quits.

Many thanks again for your time and information,
Vaya con Dios,
Cjuck, CABGx3

 

[Jon Peltier]

Chuck -

You don't need to go to the trouble of parsing the formula for your
case. LINEST, per Bernard Liengme's instructions, is equivalent.

With your data in A1:B6:

X Y
5610 7
11550 10
16830 12
22110 16
27720 26

Select A812, type this in the formula bar, then hold CTRL+SHIFT while
pressing Enter, because this is an array formula:

=LINEST(B2:B6,A2:A6^{1,2,3},,TRUE)

If you do it right, not only will you not get an error, but Excel will
reward you by enclosing the formula in curly braces:

{=LINEST(B2:B6,A2:A6^{1,2,3},,TRUE)}

The range looks like this:

3.2198E-12 -1.2309E-07 1.8858E-03 -2.8227E-01
1.3187E-13 6.6308E-09 9.9109E-05 4.2022E-01
9.9997E-01 8.3766E-02 #N/A #N/A
1.0299E+04 1.0000E+00 #N/A #N/A
2.1679E+02 7.0168E-03 #N/A #N/A

The first row, left to right, are the coefficients for X^3, X^2, X, and
the constant. The rest are other statistical calculations, which you can
read about in the help files.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[CLR]

Thanks Jon..........

With your added clarafications, I was able to get through the LINEST thing
and actually got some numbers this time.........they appeared very similar
to my original ones put in the Text Box by my Trendline, but to a higher
precision..........but when I put them all together with my value for "X"
(33660) the final answer comes up 199+ instead of 64+ like I get with the
original text formula..........I know that much difference can't be just
from the difference in precision, so I must be doing something
wrong............so anyway, my head hurts and I gotta get to bed and try it
all out again tomorrow..........I know I might not NEED to parse the text
formula out and do math on it, but I want to do it that way because I can
see what is happening...........it will all work fine, if I can just get the
code to be able to extract that formula...........

Thanks again for all your help.......
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Jon.....

Well things have changed...surrize/surprize.......this morning my user
relates that he indeed is interested in the more correct answer than in just
the text formula out of the box he originally asked for.....so, I finally got
the LINEST thing working and he was happy with that answer.........

I really do appreciate all your time and help tho.......I've learned a lot
from this experience.

Many thanks again.......
Vaya con Dios,
Chuck, CABGx3