How do I adjust a curve ?

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

"Del Cotter" wrote:

On Thu, 14 Jun 2007, in microsoft.public.excel.charting,
Mauro said:

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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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" wrote:

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%27s_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

"Mauro" wrote:

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 ?

"Jerry W. Lewis" wrote:

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

"Mauro" wrote:

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 ?

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%C3%A9_approximation

Jerry

"Mauro" wrote:

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" wrote:

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%27s_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

"Mauro" wrote:

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 ?

"Jerry W. Lewis" wrote:

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

"Mauro" wrote:

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 ?