Exatidão da linha de tendência do Excel

Question

Este é um problema que tenho de vez em quando, e isso me irrita tremendamente, porque eu sempre tenho que reavaliar cada linha de tendência que recebo.

Um exemplo:

r       L
(mm)    
30,00   97,0  
60,00   103,2  
90,00   106,0  
110,00  101,0  
125,00  88,0  
140,00  62,0  
148,00  36,7  
152,50  17,0   

Ao desenhar uma linha de tendência (usando o tipo de regressão polinomial de 3ª ordem) com r no eixo x, e L no y um, o Excel fornecerá a fórmula

y = -0,0002x³ + 0,0341x² - 1,8979x + 128,73

com R² = 0,994. Se eu interpolar valores usando essa fórmula para os mesmos valores de r como os da fórmula, eu obtenho

r   y  
(mm)      
30,00   97,083  
60,00   94,416  
90,00   88,329  
110,00  66,371  
125,00  33,68  
140,00  -17,416  
148,00  -53,5912  
152,50  -76,97725  

que são bem diferentes?

Por que isso acontece? Qual é a razão para isso?

    

Answer 1

Parece que a fórmula que o Excel forneceu tem coeficientes arredondados. Usando uma rotina de cálculo do OpenOffice para regressão, eu recebo esta fórmula, que tem um ajuste muito melhor dos dados:

y=-0.00017257x³+0.034107417x²-1.89794239x+128.7325785

Como o termo x³ é tão grande, uma pequena diferença no coeficiente tem uma grande influência no resultado previsto.

    

Answer 2

Como W_Whalley discutimos isso é porque o Excel está arredondando os valores exibido na fórmula, a correção é simplesmente alterar a formatação de exibição do marcador e veja como:

1. Crie o gráfico, adicione a linha de tendência, torne o rótulo da equação visível.

2. Clique com o botão direito do mouse no rótulo da equação e selecione Formatar rótulos de dados ...

3. Na guia Número , selecione o tipo Número e digite o número de casas decimais que você deseja.

4. Feche a janela Formatar.

Aqui está o resultado de definir o número de casas decimais como 20 (por exemplo) para seus dados de exemplo, com uma quebra de linha adicionada para evitar barras de rolagem:

y = -0.00017256831201215700x³ + 0.03410741673273060000x²
                  - 1.89794238802443000000x + 128.73257845634200000000

    

Answer 3

Se você não é capaz de derivar os coeficientes de regressão por si mesmo, você pode simplesmente substituir os valores de r com os valores de r divididos por 10. Ou seja: 30 se torna 3, 60 se torna 6 e assim por diante. Você perceberá que o Excel recalcula os coeficientes com mais precisão, porque usará dígitos mais significativos.