Using Trendline Equation for my VBA

Hi!

Is there a way of extracting with a macro the trendline equation from my graph for then using it for calculations?

Something like:

Set y = Range("B1")
Set x = Range("A1")

Then extract the equation from my graph and plot the answer...

Hope someone can guide on this!!!

Thanks so much!!

Rgds,

pezal

Rather than go through the chart trendline to get the coefficients for an equation, I would use the LINEST() function (which is available to VBA). Note that all of the available trendline types (except for moving average) are "linear" (or can be made linear) functions and can be regressed using LINEST(). Search VBA help for "using excel functions in VBA" and the Excel help file for the LINEST() function.

Thanks MrShorty, I will get a look to it.

I don't think its going to work with a linear (will not be accurate) cause Im using a 6th Polynom equation... And what I don't really know about all this is why the equation that gives me the graph (that fits very good to my curve - R2=0.9974-) doesnt even match the results displayed on the graph when introducing tha values in the equation. Seems like the equation does not correspond with the one drawn by excel!!

Regards,

I don't know how much math you've studied, but the linear in LINEST() refers to linear functions as you would study them in linear algebra, so it can do a lot more than just straight lines. For our purposes here, all polynomials are "linear" in this sense.

doesnt even match the results displayed on the graph when introducing tha values in the equation. Seems like the equation does not correspond with the one drawn by excel!!

You haven't supplied an example of what you are seeing, so it is hard to be certain in my response. I see two common causes of this:
1) Using rounded coefficients: this is especially true for high order polynomials. by default, the chart equation display does not show the coefficients of the polynomial to their full 15 digit precision -- often showing only 1 or 2 significant figures for the higher order terms. When someone uses that display in a calculator to test the result, garbage results. If you are going to use the chart trendline for the coefficients, be sure to format it so that it shows all digits for each coefficient.
2) the second issue is when Excel uses something else for "x" in the regression. If Excel decides it doesn't like the x values given (some are text or you are using a line chart instead of a scatter plot), it will use some default series for x (such as 1,2,3,...). If the user doesn't recognize this scenario, it will create confusion for the user. I prefer to take steps to make sure I am giving Excel the x values for a regression so that Excel doesn't come up with its own x values.

Hi,

First of all thanks for you interest and help.

I have attached the graph we are talking about.
Im a bit new on this of making graphs in excel (specially working with trendline equation) and Im sure Im missing most of the tools for calculating and so with this...

Also, there is maybe a better way of displaying the data to find a better equation that fits.

Thanks,

Also, there is maybe a better way of displaying the data to find a better equation that fits.

Anytime you are using a high order polynomial, you have to wonder if there is a better equation for the data. Which equation to use in regression/curve-fitting is not really an Excel question, but more of a data analysis question. First part of choosing an equation is understanding the physical process involved. In many cases, knowing how others have modeled the process is useful.

Just looking at the data, you might consider a more sigmoidal function http://en.wikipedia.org/wiki/Sigmoid_function like the logistics function or a cumulative distribution function. Many of these functions are non-linear, so they can't be regressed using the LINEST() or chart trendline algorithms. You would have to write your own spreadsheet using Solver to regress the parameters for these kinds of functions, but they would seem more likely to fit the kind of data you show.

Thanks!! Looks pretty good and gives me an idea of how to do it now

I will continue playing with the help your help.

Regards!