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 ?



"Del Cotter" wrote:

On Wed, 13 Jun 2007, in microsoft.public.excel.charting,
Mauro said:
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.

--
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.