Sorry for the long delay in responding, but you posted in an inappropriate
group. Since your concern is either with worksheet functions or with graph,
you should have posted to one of those groups, where you would have gotten an
almost immediate response. I see nothing in your post to suggest that Excel
crashed or caused a GPF, which is the topic of this group.
Details are lacking in your post. What degree polynomial? How are you
getting the coefficients to calculate the trendline? What does the data
represent?
If you displayed the trendline equation on a graph and copied the displayed
coefficients, then likely you used the heavily rounded values that Excel
displays by default. Right click on the trendline formula and format to
scientific notation with 14 decimal places. The chart polynomial trendline
is exceptionally good numerically (better than lm() in S-PLUS and R and far
better than GLM or REG in SAS). With your data the chart trendline computes
all coefficients of a 6th degree polynomial with 9-figure accuracy, despite a
condition number for X'X of 10^35.
Since you mention a "function", you may be using LINEST to fit the polynomial
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm
In Excel 2003, LINEST also gives 9-figure accuracy for a 6th degree
polynomial with your data. In earlier versions, LINEST only gives 2-figure
accuracy for the same problem. That pre-2003 LINEST got any figures right
with a problem this ill-conditioned surprised me
http://groups.google.com/group/microsoft.public.excel/msg/969a2bb33e6cdbb8
More generally, I question whether you should be fitting a polynomial at
all. Your data suggests a monotonic S-shaped curve, whereas the polynomial
will wiggle around near the asymptotes. There may be standard models to fit
your data, given a knowledge of how it was produced. In the absence of that
knowledge, you might try a nonlinear fit to a monotonic S-shaped curve, such
as a 4 or 5-parameter logistic.
y = d + (a-d)/(1+(x/c)^b)^g
Assume g=1 for the 4-parameter. d is the upper asymptote (near 1) and b is
the lower asymptote(near 0). If g=1, then c is the value of x for which y is
halfway between a and d.
Jerry
