Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thanks Tushar..............of course this stuff is 'way over my head but I
will give it a try........ Vaya con Dios, Chuck, CABGx3 "Tushar Mehta" wrote in message ... Unless you are dealing with some rather extreme functions, the process outline by Bernard Liengme in http://www.stfx.ca/people/bliengme/E...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 In article , says... 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |