Ecuacion de linea de tendencia en excel.

[micro_pepe]

Estoy teniendo problemas con una linea de tendencia en excel. Cojo una tabla de datos, la grafico, boton derecho sobre la linea, linea de tendencia y mostrar ecuacion, me muestra la ecuacion, pero si la evaluo por ejemplo para x=500, no me da el valor correcto de y.

Hace tiempo creo recordar tuve un problema parecido, pero no encuentro el post. Espero una ayuda.

PD: Dejo en los adjuntos el archivo excel. Y la ecuacion escrita por si evaluais en matlab.

Código: [Seleccionar]

-6e-14*x^6 + 2e-10*x^5 -2e-7*x^4 +0.0002*x^3 - 0.0518*x^2 + 9.2091*x -629.74

Saludos.

[micro_pepe]

Bueno, encontré el post, la solución está por cortar la curva en trozos, y cada trozo aproximarlo por una recta.

Pero sigo sin entender porqué el polinomio por el que aproxima el excel o matlab a esa curva no dá valores correctos al evaluarlo.

Saludos.

[Nocturno]

Pues parece que Excel se hace un lío con las aproximaciones polinómicas, he estado jugando con tu libro y me pasa lo mismo que a ti: no consigo que la ecuación se parezca a la curva original.

[ma4826]

 Buenas, he abierto tu libro con gnumeric y me sale más o menos la ecuación que pones pero con más decimales, y la ecuación se ajusta más o menos a los datos, probando con python:

Código: [Seleccionar]

>>> A=[180,181,182,188,200,227,256,288,317,348,377,410,438,471,500,532,563,596,626,660,689,723,753,759]
>>> B=[10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125]
>>> for i in range(len(A)):
...     x=A[i]
...     y=-6.41871e-14*x**6+1.95499e-10*x**5-2.40135e-7*x**4+0.000151736*x**3-0.0518362*x**2+9.2091*x-629.736
...     d=B[i]-y
...     print "x=" + str(x) + "  y=" + str(B[i]) + "  y'=" + str(y) + "  dif=" + str(d)
...
x=180  y=10  y'=16.0070738201  dif=-6.00707382009
x=181  y=15  y'=16.6492197376  dif=-1.64921973755
x=182  y=20  y'=17.2791920495  dif=2.72080795052
x=188  y=25  y'=20.8146887688  dif=4.18531123121
x=200  y=30  y'=26.7597056  dif=3.2402944
x=227  y=35  y'=35.9678757324  dif=-0.967875732367
x=256  y=40  y'=41.878433392  dif=-1.878433392
x=288  y=45  y'=46.3006367291  dif=-1.30063672906
x=317  y=50  y'=49.9147160261  dif=0.0852839739133
x=348  y=55  y'=54.1767869628  dif=0.823213037154
x=377  y=60  y'=58.7618050913  dif=1.23819490868
x=410  y=65  y'=64.5671866524  dif=0.432813347592
x=438  y=70  y'=69.7325230502  dif=0.267476949768
x=471  y=75  y'=75.752803323  dif=-0.752803322995
x=500  y=80  y'=80.7468125  dif=-0.7468125
x=532  y=85  y'=85.8089772421  dif=-0.808977242141
x=563  y=90  y'=90.3113058279  dif=-0.311305827891
x=596  y=95  y'=94.90182687  dif=0.0981731300036
x=626  y=100  y'=99.2014783028  dif=0.798521697222
x=660  y=105  y'=104.59131487  dif=0.408685129595
x=689  y=110  y'=109.792385508  dif=0.207614492238
x=723  y=115  y'=116.426987077  dif=-1.42698707689
x=753  y=120  y'=122.060676393  dif=-2.06067639292
x=759  y=125  y'=123.051744694  dif=1.94825530624
Parece que excel se come los decimales por alguna extraña razón. De todas formas me parece mejor solución mejor la tabla que andar con polinomios de sexto grado.

Saludos,

Miguel.


PD ¿Es la gráfica de un termistor?.

[micro_pepe]

Pues será por los decimales, pero como dije lo he solucionado poniendo 4 rectas, se come mucho menos código. Lo curioso es que en Matlab la aproximación a un polinomio tampoco dá valores correctos, supongo que también por los decimales.

ma4826 no es de un termistor, es de un medidor de nivel RF (AD8307) en los extremos de la respuesta no es lineal, y queria apurarlo al máximo.

[juanpavz]

En la Universidad use este programa para hallar ecuaciones de datos experimentales, con resultados bastante buenos.
http://creativemachines.cornell.edu/eureqa/

[jorgeaf_59]

Hola a todos,

Quizás ya dieron el tema por solucionado, pero me gustaría aclararlo (si puedo) un poco más.
Como bien concluyeron, el problema está en los decimales, ¿pero en cuales?
En los que excel no imprime cuando escribe la ecuación en el gráfico, pero sin embargo los calcula internamente.
Si lo que queremos, además del gráfico, es obtener los coeficientes de la línea de tendencia con más decimales, podemos pedir que excel nos los presnte mediante la función estimación lineal.
Adjunto la hoja con los valores de y calculados a partir de los coeficientes del gráfico (columna E) y los valores calculados a partir de los coeficientes que se obtienen de la estimación lineal (columna G).
En la columna H está la diferencia entre los valores originales y los calculados; salvo el primero, me parece que no estan mal.
Es interesante notar que los coeficientes calculados por la estimación lineal (valores de J1 a P1) coinciden con los presentados por miguel con gnumeric y/o python, excel puede tener errores, pero no en estos temas.
Como comentario final, les recuerdo tomar siempre con mucho cuidado los valores que surgen de expresiones polinómicas de grados altos: además de estos problemas de estabilidad numérica, tenemos que considerar el sentido físico del ajuste.
Disculpen lo largo del comentario, pero como se darán cuenta, el tema me interesa mucho.
Espero haber aportado algo y no confundido más.
Cordiales saludos,

Jorge

[micro_pepe]

Gracias por la aclaración jorgeaf_59,

si alguna vez me decido a implementar un polinomio de 6º grado, lo tendré en cuenta, pero el trocear la curva y aproximar con rectas come mucho menos memoria del pic, todo depende de la precisión requerida claro.

Saludos.