trendline equation solving
Posted by Corey Dale on January 05, 2002 2:17 PM
Hello.
I'm hoping someone can help me with this.
I have an XY chart with 6 data points on it, and have a 4th order polynominal trendline setup to fit the data. When plotted, the trendline resembles a bell curve. I know that you can display the equation of the trendline on the chart, but is there a way to have excel calculate the maximum Y value, and subsequently spit out the corresponding X value?
Also, on some occassions I only have 4 data points instead of the 6 mentioned above. When I have only 4, the trendline does not plot the way I'd like it to (I want it to look like a bell curve, best fitting the data points). Is there a way to make a trendline that will draw this curve for me with only the 4 data points? I've tried the other types of trendlines, and also the best fit lines, etc.
Any help would be greatly appreciated!
Regards,
Corey Dale
Posted by Mike on January 05, 2002 2:45 PM
I was just working on a similar problem. My solution was less than graceful I'm afraid. I used VBA to access the formula. I parsed it into it's 3 terms and solved it. Then I displayed a textbox on the chart with the solution.
If you are conversant in VBA I can send you the code.
-Mike
Posted by Bariloche on January 05, 2002 5:00 PM
Corey, Mike,
Here are two VBA subroutines that you all may find useful: Link
Copy that text over into a VBA module and mess around with the subroutines. NOTE: You'll have to do a little tidying up of the pasted code due to line returns, etc. before running it. Just follow the simple directions and I think you'll have fun with it.
The news group that that was posted on is a good source of technically-oriented code. A search of past posts will usually yield a solution to many Excel problems.
enjoy
Posted by Bariloche on January 05, 2002 5:11 PM
Also,
You can download Tushar Mehta's "Plot Manager" workbook available here. I haven't messed around with this (although I have downloded it) but according to Tushar in addition to calculating the equation of the line his workbook also calculates the first and second derivatives and provides that info on a sheet. You could look at his code and extend it to solve your maxima question.
enjoy
Reference link
Posted by Corey Dale on January 09, 2002 9:08 AM
I'd appreciate it if you could send the code. I've been looking for a VB way of accessing the trendline, but wasn't sure how to go about it.
You can send it to either of these email addresses:
coreyd@powersurfr.com
cdale_shelbyeng@hotmail.com
Thanks!
Corey I was just working on a similar problem. My solution was less than graceful I'm afraid. I used VBA to access the formula. I parsed it into it's 3 terms and solved it. Then I displayed a textbox on the chart with the solution.
Posted by Corey Dale on January 09, 2002 9:46 AM
Wow! Thanks for the great post Bariloche. I'll have to take some time to decipher it (my VB is not the best, to say the least), but the help is greatly appreciated!
Corey Corey, Mike,
