How do I adjust a curve ?

[Mauro]

I have a serie of experimental data (x1,...xn) and (y1, ..., yn). I plote the
points on an XY chart and I try to fit the trendline. The equation of this
trendline is like the following: f(x)=a*x^6 + b*x^5+c*x^4+....+h. But when i
evalute the function f(x) in the each value of x, f(xi) I obtained a linear
function that is different from the data tendency. What i am doing worng or
how can i get the correct function of the plot.

The plot of the experimental data is like the nromal distribution, and I
need to obtain the area under the peak ? Is there any function on the excel
that permits this ?

 

[Jerry W. Lewis]

If your equation is from the chart display, then by default the coefficient
values will be too heavily rounded to use. Right click on the dilpayed
equation and format in scientific notation with 14 decimal places. Also make
sure that your chart is an "XY (Scatter)" chart and not a "Line" chart.

You may not have a broad enough range of x-values to permit accurate
numerical calculation of the coefficients (is the data set small enough to
include in the body of a reply?). This can be especially problematic if your
polynomial coefficients are from LINEST() in Excel versions prior to 2003.
In 2003's LINEST, coeffients of exactly zero are also suspect.

Jerry

 

[Del Cotter]

I have a serie of experimental data (x1,...xn) and (y1, ..., yn). I plote the
points on an XY chart and I try to fit the trendline. The equation of this
trendline is like the following: f(x)=a*x^6 + b*x^5+c*x^4+....+h. But when i
evalute the function f(x) in the each value of x, f(xi) I obtained a linear
function that is different from the data tendency. What i am doing worng or
how can i get the correct function of the plot.

It's because the equation on the chart is displayed with only a few
decimal places, so when you evaluate it using those values, the result
doesn't look the same. The real values are stored to many more decimal
places.

The plot of the experimental data is like the nromal distribution, and I
need to obtain the area under the peak ? Is there any function on the excel
that permits this ?

If you want to fit a normal distribution, I would suggest abandoning the
"trendline" facility in the chart program, which is only a convenient
toy for business users, and using a calculating tool which is very near
to hand, and much more powerful and flexible. I refer to the spreadsheet
itself. This will also let you fit your data to an actual normal
distribution, instead of a polynomial caricature of one.

Other posters can tell you how to use the LINEST() function. I don't
know if LINEST() can do what I suggest, but I always used to be most
comfortable making up my own least-squares fitting device using the
Solver add-in in Excel. I would create an estimated normal distribution
using NORMDIST() times a constant A to approximate the y-values,
SUMXMY2() to calculate how much the estimate differs from the actual
values, and the Solver add-in to choose the mean, standard deviation,
and A that minimises the difference.

For example, your setup may look like this:

[cell A1] 5.000 mean
2.029 st. dev.
12.712 area

actual guess
1 0.50 =$C$3*NORMDIST(A6,$C$1,$C$2,0)
2 0.75 =$C$3*NORMDIST(A7,$C$1,$C$2,0)
3 1.50 =$C$3*NORMDIST(A8,$C$1,$C$2,0)
4 2.25 =$C$3*NORMDIST(A9,$C$1,$C$2,0)
5 2.50 =$C$3*NORMDIST(A10,$C$1,$C$2,0)
6 2.25 =$C$3*NORMDIST(A11,$C$1,$C$2,0)
7 1.50 =$C$3*NORMDIST(A12,$C$1,$C$2,0)
8 0.75 =$C$3*NORMDIST(A13,$C$1,$C$2,0)
9 0.50 =$C$3*NORMDIST(A14,$C$1,$C$2,0)

=SUMXMY2(B6:B14,C6:C14)
sum of square deviations

And the results could look like this:

[cell A1] 5.000 mean
2.029 st. dev.
12.712 area

actual guess
1 0.500 0.358
2 0.750 0.838
3 1.500 1.538
4 2.250 2.214
5 2.500 2.500
6 2.250 2.214
7 1.500 1.538
8 0.750 0.838
9 0.500 0.358

0.061
sum of square deviations

To keep this on topic for microsoft.public.excel.charting, you can then
chart the "actual" points and "guess" line to see how they match.

 

[Mauro]

Jerry:

Thank´s a lot.
I do the change in the format on the displayed equation and you have reason.
The data serie is the followin:

542,195 -7,59
542,404 -7,36
542,65 -7,09
542,827 -6,88
543,034 -6,62
543,212 -6,40
543,388 -6,17
543,602 -5,89
543,819 -5,59
543,997 -5,33
544,212 -5,01
544,39 -4,73
544,568 -4,44
544,747 -4,15
544,926 -3,84
545,136 -3,47
545,315 -3,14
545,532 -2,75
545,748 -2,33
545,965 -1,91
546,146 -1,55
546,364 -1,11
546,585 -0,65
546,8 -0,19
547,056 0,37
547,312 0,93
547,607 1,59
547,973 2,43
548,346 3,28
549,004 4,82
549,485 5,93
549,814 6,69
549,995 7,10
550,247 7,67
550,466 8,15
550,649 8,54
550,831 8,92
551,018 9,29
551,199 9,65
551,383 10,00
551,563 10,30
551,708 10,60
551,889 10,90
552,029 11,20
552,209 11,50
552,354 11,70
552,461 11,90
552,566 12,00
552,709 12,20
552,814 12,40
552,921 12,50
553,063 12,70
553,204 12,80
553,344 13,00
553,484 13,10
553,588 13,20
553,728 13,40
553,866 13,50
553,969 13,50
554,072 13,60
554,177 13,70
554,313 13,80
554,449 13,80
554,586 13,90
554,721 13,90
554,857 14,00
554,958 14,00
555,091 14,00
555,226 14,00
555,36 14,00
555,492 14,00
555,622 14,00
555,755 13,90
555,887 13,90
556,054 13,80
556,187 13,80
556,319 13,70
556,447 13,60
556,61 13,50
556,772 13,40
556,935 13,20
557,064 13,10
557,224 13,00
557,385 12,80
557,546 12,60
557,707 12,50
557,868 12,30
558,059 12,00
558,252 11,80
558,442 11,50
558,631 11,20
558,885 10,90
559,171 10,50
559,461 10,00
559,897 9,32
560,496 8,35
560,964 7,59
561,345 6,98
561,753 6,34
562,167 5,70
562,511 5,18
562,894 4,62
563,236 4,12
563,583 3,63
563,933 3,16
564,185 2,82
564,439 2,49
564,758 2,09
565,074 1,70
565,426 1,29
565,714 0,96
566,032 0,60
566,352 0,25
566,673 -0,09
566,964 -0,39
567,257 -0,68
567,384 -0,81
567,608 -1,02
567,832 -1,23
568,123 -1,49
568,477 -1,80
568,863 -2,12
569,283 -2,45
569,607 -2,69
569,993 -2,97
570,411 -3,26
570,738 -3,47
571,192 -3,77
571,678 -4,07
572,199 -4,37
572,655 -4,63
573,046 -4,83
573,673 -5,15
574,292 -5,44
575,276 -5,88
575,828 -6,12


what do you think ? Exist a better solution than
y = -1,56667460056046E-07x6 + 5,02755756786137E-04x5 -
6,70625116968844E-01x4 + 4,75836028914398E+02x3 - 1,89364117750962E+05x2 +
4,00628517416978E+07x - 3,51900855534185E+09 ?

How can I obtain the area under the curve for each (xi) ? in other words how
can I obtained a integral with a high precision using execl ?

 

[Mauro]

Del:

Thanks for your opinion, I change the decimal point and a better solution i
obtained.

I am traing to fit this serie of data, I want to obtain an integral for each
(xi), I try to use your example but i didn´t have the area.

what do you think about this:
x y
542,195 -7,59
542,404 -7,36
542,65 -7,09
542,827 -6,88
543,034 -6,62
543,212 -6,40
543,388 -6,17
543,602 -5,89
543,819 -5,59
543,997 -5,33
544,212 -5,01
544,39 -4,73
544,568 -4,44
544,747 -4,15
544,926 -3,84
545,136 -3,47
545,315 -3,14
545,532 -2,75
545,748 -2,33
545,965 -1,91
546,146 -1,55
546,364 -1,11
546,585 -0,65
546,8 -0,19
547,056 0,37
547,312 0,93
547,607 1,59
547,973 2,43
548,346 3,28
549,004 4,82
549,485 5,93
549,814 6,69
549,995 7,10
550,247 7,67
550,466 8,15
550,649 8,54
550,831 8,92
551,018 9,29
551,199 9,65
551,383 10,00
551,563 10,30
551,708 10,60
551,889 10,90
552,029 11,20
552,209 11,50
552,354 11,70
552,461 11,90
552,566 12,00
552,709 12,20
552,814 12,40
552,921 12,50
553,063 12,70
553,204 12,80
553,344 13,00
553,484 13,10
553,588 13,20
553,728 13,40
553,866 13,50
553,969 13,50
554,072 13,60
554,177 13,70
554,313 13,80
554,449 13,80
554,586 13,90
554,721 13,90
554,857 14,00
554,958 14,00
555,091 14,00
555,226 14,00
555,36 14,00
555,492 14,00
555,622 14,00
555,755 13,90
555,887 13,90
556,054 13,80
556,187 13,80
556,319 13,70
556,447 13,60
556,61 13,50
556,772 13,40
556,935 13,20
557,064 13,10
557,224 13,00
557,385 12,80
557,546 12,60
557,707 12,50
557,868 12,30
558,059 12,00
558,252 11,80
558,442 11,50
558,631 11,20
558,885 10,90
559,171 10,50
559,461 10,00
559,897 9,32
560,496 8,35
560,964 7,59
561,345 6,98
561,753 6,34
562,167 5,70
562,511 5,18
562,894 4,62
563,236 4,12
563,583 3,63
563,933 3,16
564,185 2,82
564,439 2,49
564,758 2,09
565,074 1,70
565,426 1,29
565,714 0,96
566,032 0,60
566,352 0,25
566,673 -0,09
566,964 -0,39
567,257 -0,68
567,384 -0,81
567,608 -1,02
567,832 -1,23
568,123 -1,49
568,477 -1,80
568,863 -2,12
569,283 -2,45
569,607 -2,69
569,993 -2,97
570,411 -3,26
570,738 -3,47
571,192 -3,77
571,678 -4,07
572,199 -4,37
572,655 -4,63
573,046 -4,83
573,673 -5,15
574,292 -5,44
575,276 -5,88
575,828 -6,12

The results obtained are with high precision ?

I have a serie of experimental data (x1,...xn) and (y1, ..., yn). I plote the
points on an XY chart and I try to fit the trendline. The equation of this
trendline is like the following: f(x)=a*x^6 + b*x^5+c*x^4+....+h. But when i
evalute the function f(x) in the each value of x, f(xi) I obtained a linear
function that is different from the data tendency. What i am doing worng or
how can i get the correct function of the plot.

It's because the equation on the chart is displayed with only a few
decimal places, so when you evaluate it using those values, the result
doesn't look the same. The real values are stored to many more decimal
places.

The plot of the experimental data is like the nromal distribution, and I
need to obtain the area under the peak ? Is there any function on the excel
that permits this ?

If you want to fit a normal distribution, I would suggest abandoning the
"trendline" facility in the chart program, which is only a convenient
toy for business users, and using a calculating tool which is very near
to hand, and much more powerful and flexible. I refer to the spreadsheet
itself. This will also let you fit your data to an actual normal
distribution, instead of a polynomial caricature of one.

Other posters can tell you how to use the LINEST() function. I don't
know if LINEST() can do what I suggest, but I always used to be most
comfortable making up my own least-squares fitting device using the
Solver add-in in Excel. I would create an estimated normal distribution
using NORMDIST() times a constant A to approximate the y-values,
SUMXMY2() to calculate how much the estimate differs from the actual
values, and the Solver add-in to choose the mean, standard deviation,
and A that minimises the difference.

For example, your setup may look like this:

[cell A1] 5.000 mean
2.029 st. dev.
12.712 area

actual guess
1 0.50 =$C$3*NORMDIST(A6,$C$1,$C$2,0)
2 0.75 =$C$3*NORMDIST(A7,$C$1,$C$2,0)
3 1.50 =$C$3*NORMDIST(A8,$C$1,$C$2,0)
4 2.25 =$C$3*NORMDIST(A9,$C$1,$C$2,0)
5 2.50 =$C$3*NORMDIST(A10,$C$1,$C$2,0)
6 2.25 =$C$3*NORMDIST(A11,$C$1,$C$2,0)
7 1.50 =$C$3*NORMDIST(A12,$C$1,$C$2,0)
8 0.75 =$C$3*NORMDIST(A13,$C$1,$C$2,0)
9 0.50 =$C$3*NORMDIST(A14,$C$1,$C$2,0)

=SUMXMY2(B6:B14,C6:C14)
sum of square deviations

And the results could look like this:

[cell A1] 5.000 mean
2.029 st. dev.
12.712 area

actual guess
1 0.500 0.358
2 0.750 0.838
3 1.500 1.538
4 2.250 2.214
5 2.500 2.500
6 2.250 2.214
7 1.500 1.538
8 0.750 0.838
9 0.500 0.358

0.061
sum of square deviations

To keep this on topic for microsoft.public.excel.charting, you can then
chart the "actual" points and "guess" line to see how they match.

 

[Jon Peltier]

Using LINEST, the predicted zeroth order constant is exactly zero, which is
because of Excel 2003's insistence that small values are really related to
roundoff errors.

Did you compare the actual data to the sixth order fit? You get a good R^2,
but to me the fit still isn't all that great. I'd expect errors if you try
to integrate the fitted curve to estimate the area under the actual curve.
Using the trapezoid rule, I find the actual data has an area of 105.0 while
the fitted curve has an area of 108.0, a difference of about 3%

- Jon

 

[Jerry W. Lewis]

There are two parts to my reply

Integration:

Given the number of points that you have, you would get a more accurate
integral from the trapezoid rule or Simpson's rule
http://en.wikipedia.org/wiki/Trapezoid_rule
http://en.wikipedia.org/wiki/Simpson's_rule
than from integrating a low order polynomial that doesn't fit the curve very
well.

If you know the functional form of this curve, consider Gaussian quadrature
http://en.wikipedia.org/wiki/Gaussian_quadrature
with a high number of points.

If you are determined to follow an approach similar to what you have
started, you will probably get a better fit from a ratio of polynomials
instead of a simple polynomial.

Accuracy of polynomial fit:

Fitting a 6th degree polynomial over this narrow a range of x-values is a
very difficult numerical problem (condition number ~4.4E+54). LINEST gets no
correct figures for any of its coefficients in either Excel 2000 or Excel
2003 (indeed, it even gets the sign wrong for some of the coefficients). The
chart trendline does better, but the accuracy of the chart trendline solution
depends greatly on the accuracy at which the x-values were recorded.

If the x-values were recorded to infinite precision, then chart trendline
gives 8 figure accuracy for its Least Squares polynomial coefficients. To 15
figure accuracy, the coefficients are
-1.56662739976292E-07x6 + 0.000502742552201166x5 - 0.670610342269545x4 +
475.827763896439x3 - 189361.80617287x2 + 40062593.1319661x - 3519008546.02894

Note however, that discrepancies in the 17th figures of the x-values could
change the 15th figures of these coefficients. Your x-values were reported
to no more than 6 figures. I have not done the error analysis, but it would
not surprise me if discrepancies in the unreported 7th figure could impact
the 5th figure of the least squares coefficients.

Jerry

 

[Jerry W. Lewis]

Was that in 2007? In 2003 LINEST I get a zero quadratic coefficient, but a
constant of 4.19E+08.

I did assume that the OP's regional settings use a comma as the decimal
separator.

Jerry

 

[Del Cotter]

I am traing to fit this serie of data, I want to obtain an integral for each
(xi), I try to use your example but i didn´t have the area.

You don't need to have the area; if you set up the Solver add-in to
minimise the sum of square deviations, using the three parameters mean,
std.dev, and area as inputs, the Solver algorithm will home in on the
combination of parameters that fits the data best.

Just put 1.0 into the area to start with, and see what values produce
the lowest s.s.d.

 

[Del Cotter]

Del:

what do you think about this:

I got:

555.603 mean
004.154 st. dev.
152.906 area

But I don't know why you said the data would fit a normal distribution:
it's asymmetrical and has negative values. At the moment a polynomial
will fit the curve at least as well.

You should craft your own model of what you think the curve looks like
and use Solver on that.

 

[Mauro]

Del:

yes, you have reason I was traing to explain how the data fit, but I think
that was not a good explanation. I am traing to undertood you example. Thks

 

[Mauro]

JERRY:

what do you want to say "ratio ratio of polynomials" ?. I do the trapezoid
rule and the data fit well. Thks

 

[Jerry W. Lewis]

I'm glad that the trapezoid rule seems to meet your needs.

As for approximating your function, low order polynomials are severly
limited in the types of curvature they can approximate. Low order rational
functions
http://en.wikipedia.org/wiki/Rational_function
are much more flexible and may give a better approximation involving lower
order polynomials
http://en.wikipedia.org/wiki/Padé_approximation

Jerry