Formel von Trendlinie kommt nicht auf erwartete Ergebnisse

[habichtfreak]

Hallo,

mir ist kürzlich aufgefallen, dass die Formeln die Excel zu Trendlinie ausspuckt nicht richtig sein können. Beispiel: Ich habe Wertepaare (Einheiten spielen an dieser Stelle keine Rolle)

Ich lasse Excel daraus ein Diagramm machen, füge eine Trendlinie hinzu und spiele mit den Einstellungen soweit bis das Bestimmtheitsmaß möglich nah an 1 oder genau 1 ist. Dann kommt das bei raus

Blau sind die Werte, rot die Trendlinie. Beide liege perfekt übereinander. Daher ist R² auch 1. Nach meinem Verständnis, heißt das, die Formel die Excel da ausspuckt liefert für den bereich 0 bis 1050 exakt die Werte für Y die ich bereits vorher hatte. Dem ist aber nicht so. Setzt man die Formel ein, weichen die Werte für Y erheblich an:

Wie kommt diese Abweichung zu Stande? Die Formel müsste exakt die Werte liefern, die schon in der Tabelle stehen. Mir ist auch noch aufgefallen, bei Formel die keine sehr kleinen Zahlen habe (zB 0,0000000003*x^y) ist die fehlerhafte Abweichung geringer. Hat Excel ein Problem bei rechnen mit sehr kleinen Zahlen? Ich sehe ich den Wald vor lauter Bäumen gerade nicht?

Gruß habichtfreak

 

[blalaber]

Zeig mal bitte wie du die Formel getippt hast. Hast du einfach die Formel wie sie im Diagramm steht abgetippt? Weil alleine der Koeffizient von x^3 ist halt übel gerundet. 4e-8 kann halt alles sein von 3.5e-8 bis 4.49e-8 - das macht schon was aus.

 

[habichtfreak]

 

[moneymaker4ever]

Guck mal hier: https://learn.microsoft.com/de-de/office/troubleshoot/excel/inaccurate-chart-trendline-formula

mit dem Woraround spuckt mir Excel bei deinen Werten folgende Gleichung aus:

 

[blalaber]

Dann hast du einen Rundungsfehler. Schau mal hier:
https://www.herber.de/forum/archiv/988to992/990842_Trendlinien_in_Excel_Polynom_3_Grades.html
Hier steht die Formel wie du jeden einzelnen Koeffizient berechnen kannst (exakt).
Damit sieht es dann nice aus:

Spalte yfit wie bei dir mit gerundeten Koeffizienten, Spalte yfitExakt mit den per Formel berechneten Koeffizienten:

Gerade beim Koeffizient für x^3 ist halt der Rundungsfehler sehr groß und das zieht sich bei der Potenz sehr stark durch.

 

[xxMuahdibxx]

Libre Office hat da jetzt keine Probleme mit

 

[blalaber]

Ein Problem hat MS Excel ja auch nicht, es ist nur ungünstig dargestellt. Wenn einem die Regressionsformel zu stressig ist kann man auch in die Eigenschaften der Trendlinien-Daten so anpassen, dass man genug Kommastellen hat:

 

[Don_2020]

Excel kann halt nicht richtig rechnen. Das war schon in den 90er-Jahren des letzten Jahrunderts bekann.

 

[the_ButcheR]

Excel rechnet richtig, das ist wir gesagt ein einfacher Rundungsfehler. Mehr Stellen für die Formel anzeigen lassen und du bekommst den Fehler auch kleiner.

 

[habichtfreak]

Danke für eure Hinweise. Die Einstellung zu mehr Kommastellen versteckt sich allerdings nicht mehr im Menüband, sondern hier:

Zumindest bei Office 2021. Die Beschreibungen die ich bei MS gefunden habe, sind sehr veraltet (2010er und älter).

 

[firespot]

Die von Excel errechneten Koeffizienten sind korrekt, aber du solltest bei der Darstellung auch eplizit die scientific e-Notation wählen um auch alle signifikanten Stellen angezeigt zu bekommen. Falls Excel 32-bit Fließkommazahlen nutzt (ich glaube das tut es) gibt es ca. 8 signifikaten Stellen, bei 64-bit Fließkommazahlen wären es ca. 16. Signifikant bezieht sich auf alles ab der ersten nicht-0 Ziffer.

Und wenn du das professionell rechnen willst, nutzt du gar nicht Excel, sondern z.B. R. Da sieht man dann im Normal-Q-Q-Plot auch, dass es gewisse Probleme bei den Ausläufern der Verteilung / Heteroskedaszidität im Sinne von zunehmender Varianz bei größerem Erwartungwert gibt. Solange du nur die least-squares Koeffzienten und R² nutzt ist das ok, aber bei weiterführenden Dingen wären die Verteilungsannahmen in Frage zu stellen und vermutlich auf ein anderes Modell umzustellen.

PS: Man nutzt im allgemeinen nicht R², sondern das adjusted R² welches einen penalty für jeden zusätzlich eingeführten Koeffizienten einführt (https://en.wikipedia.org/wiki/Coefficient_of_determination). Denn ansonsten kann man selbst bei n reinen Zufallszahl-Werten mit einem Polynomial des Grades n-1 einen perfekten fit mit R² = 1 erzielen.

R-Code:

x <- c(1, 87.5, 175, 262.5, 350, 437.5, 525, 612.5, 700, 787.5, 875, 962.5, 1050)
y <- c(1, 28.5, 54.5, 78, 100.5, 121.5, 141, 159, 177, 193.5, 208.5, 223.5, 238.5)
plot(y ~ x)
modelPoly3 <- lm(y ~ poly(x, 3, raw = TRUE))
summary(modelPoly3)
plot(modelPoly3)