Adding new trendlines?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hog.badger@mbox.bol.bg - 01 Jun 2007 08:58 GMT

I'm fitting curve fitting to asymptiotic functions such as y = (ax +
b)/(cx + d) and would like to draw my fitted line through the
scattered data.

The problem is not with the curve fitting, it is with drawing the
trendline.
Excel 2000 only has six trendline curve types, and none of them match
my function.

Is there any way to add new trendlines to Excel 2000?

Failing that, any other way I could add my fitted curve to the scatter-
plot?

Thanks,
Paddy

PS. Apologies if this is repeating ... i get "server error" on
posting.

Reply to this Message

Shane Devenshire - 01 Jun 2007 21:49 GMT

You can't add trendline types to Excel's charting area.

What you can do is design your own formula, in the spreadsheet, that does
the trendline calculation you want and then plot that on the chart.

But you need to know the formula necessary to calculate the trendline.

Also, you can expand some of Excel's trendlines by using the appropriate
formulas in the spreadsheet and going beyond the limits set by the Chart.
For example, in the spreadsheet you can use LINEST to calculate a 16th order
polynomial trendline.

Cheers,
Shane

> I'm fitting curve fitting to asymptiotic functions such as y = (ax +
> b)/(cx + d) and would like to draw my fitted line through the
[quoted text clipped - 15 lines]
> PS. Apologies if this is repeating ... i get "server error" on
> posting.

Reply to this Message

Jerry W. Lewis - 07 Jun 2007 19:37 GMT

While your suggestion is technically correct, I would be highly suspicious of
the numerical accuracy of even a 6th degree polynomial solution by LINEST in
Excel 2000, unless I knew that the design matrix was either orthogonol or
nearly so.

For example,
http://groups.google.com/group/microsoft.public.excel/msg/969a2bb33e6cdbb8
gives an example where pre-2003 LINEST gives no correct figures for any
coefficient of a 6th degree polynomial, where the chart trendline gets 9
correct figures.

In Excel 2003, LINEST is much more numerically accurate, but coefficients of
exactly zero are not to be trusted.
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thr
ead/aaa78a91ec42fd4b/98be08e90c3cfa
LINEST in Excel 2007 resolves this problem, but the chart trendline in 2007
was "improved" such that it is no longer trustworthy.
http://groups.google.com/group/microsoft.public.excel.charting/msg/67353c068ee07b94

Jerry

> You can't add trendline types to Excel's charting area.
>
[quoted text clipped - 30 lines]
> > PS. Apologies if this is repeating ... i get "server error" on
> > posting.

Reply to this Message

Jerry W. Lewis - 07 Jun 2007 19:09 GMT

Your equation has no unique solution. If (a,b,c,d) is a solution, then so is
(a*e,b*e,c*e,d*e) for any non-zero value e. Set one of the four parameters
to an arbitrary constant of your choosing (I tend to set d=1), and linearize
to y*(c*x+d)=(a*x+b) which is linear in the unknowns and can therefore be
re-arranged for solution using LINEST.

Jerry

Reply to this Message