Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CLR
 
Posts: n/a
Default Polynimial trandline formula

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




  #3   Report Post  
Jon Peltier
 
Posts: n/a
Default

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/E...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 wrote:

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




  #5   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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/E...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 wrote:

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








  #6   Report Post  
Jon Peltier
 
Posts: n/a
Default

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 wrote:

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" wrote in message
...

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/E...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 wrote:


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







  #7   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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 wrote:

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" wrote in message
...

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/E...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 wrote:


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









  #8   Report Post  
Jon Peltier
 
Posts: n/a
Default

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 A8:D12, 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 wrote:

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" wrote in message
...

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 wrote:


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" wrote in message
...


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/E...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 wrote:



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









  #9   Report Post  
CLR
 
Posts: n/a
Default

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


"Jon Peltier" wrote in message
...
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 A8:D12, 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 wrote:

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" wrote in message
...

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 wrote:


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" wrote in message
...


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/E...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 wrote:



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











  #10   Report Post  
CLR
 
Posts: n/a
Default

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



"CLR" wrote:

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


"Jon Peltier" wrote in message
...
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 A8:D12, 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 wrote:

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" wrote in message
...

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 wrote:


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" wrote in message
...


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/E...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 wrote:



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












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"