First off, thanks to Bernard, Tushar & Jerry for their helpful comments
and suggestions. I learned alot about a useful function (LINEST) and
regression curve fitting in general, something I hadn't expected when I
posted my question.
Jerry W. Lewis Wrote:
Right click on the displayed trendline equation and format as
scientific
notation with 14 decimal places.
It worked, and that was exactly what I had been trying to do
originally. With more (numeric)precision, my re-ploted data matched my
original line exactly.
I find that sometimes Excel is 'fussy' about whether a text box is
already selected when you open a formatting menu - sometimes it only
shows the "Font" menu and not the "Colors &
Lines/Font/Number/Alignment" menu depending on exactly what you have
selected. Once I figured that out, I was able to increase the displayed
precisions as I needed.
One general observation (which might be old news to some)- whenever I
work with trendlines and curve fitting, I find that Excel does not
always properly 'refresh' the trendline equation on the chart. If I
switch curves (exponential to log etc) or change the order of the
polynomial, the equation doesn't change, or shows something different
than If I plot an entirely new trendline with the same fitting
equation.
This was the case when I tried your (Jerry's) suggestion as well. I
increased the precision on the equation I had showing in Excel, and
plotted it, but the data diverged again. When I set a new trendline and
compared the equations, the coefficient of the 4th term was completely
different, and the new equation worked properly.
I'd still like to be able to use the LINEST function sometimes in the
future, but until I can figure out my issue with replicating Bernard's
example,
(<http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm)
I'll have to be cautious. I will be back at my office this week, and
will try the example on my Windows computer and report back on any
results. For simplicity's sake, I hope the problem was between the
keyboard and the chair.
Thanks again
Kevin
Often fitting a polynomial with this high a degree is overfitting the
data. Even if the polynomial degree is theoretically justified,
fitting
it will often be an extremely difficult numerical problem, well beyond
the capabilities of pre-2003 LINEST. If you provide your data (inline
text, not attachments in newsgroups. please), I could provide more
information.
Jerry
KevinW wrote:
How can I increase the precision in Excel's "Display Equation"
option
for trendlines?
I am trying to fit a polynomial regression (trendline) to a X, Y
scatterplot I have created......
Just to be clear, I am -not- trying to find the 'best fit' for a plot
of scattered data. Rather, I am trying to find an equation to describe
an existing line. I scanned and digitized a Larson-Miller curve I will
be using extensively for my thesis. The data points are very close
together. I'd like to be able to enter a value and return the
corresponding value from the curve. As long as the answer is the same
as the original curve, then I'm happy with the equation for the line.
Digitizing and inputing the curve into Excel is probably more accurate
than trying to manually read values over and over from a hardcopy
plot.
Here is an example of my data;
Row X Y
1) 30.07 62.08
2) 30.08 62.08
3) 30.09 62.08
4) 30.09 61.96
5) 30.10 61.96
6) 30.11 61.83
7) 30.12 61.83
8) 30.13 61.71
...(snip 1000 data points)....
1047) 38.81 6.25
1048) 38.82 6.25
1049) 38.83 6.24
I may want to know what the 'X' is for Y=61. Since my digitizer didn't
input a number for exactly 61, I would have to interpolate, maybe using
some sort of look up table etc or just use something close. Either way
it would be slow and semi-manual. However, Since I have *alot* of data,
the polynomial equation equation fits the line well (at least within the
accuracy of the scan etc). I can get my X for any Y I select (but bound
by {30.07,62.08} and {38.83,6.24} i.e. no extrapolating).
I hope that makes sense - if you like I could still post the data,
however there is alot of it.
--
KevinW
------------------------------------------------------------------------
KevinW's Profile:
http://www.excelforum.com/member.php...o&userid=30019
View this thread:
http://www.excelforum.com/showthread...hreadid=497104