Excel/Trendlinie/Poly so schlecht?

[H.Joachim S. (crazyhorse)]

Ein blöder Sensor sollte auf 0-100% skaliert werden. Da ich mal genug 
Rechenleistung habe (Raspberry) dachte ich mir: Polynom statt Tabelle 
erstellen.
Wertepaare in Excel eingetippt, Trendlinie Polynom 3.Grades, die Kurve 
passte auch ganz gut und ausreichend genau. Formel hergenommen da habe 
ich mich schon über die glatten a und b gewundert, aber gut - kann ja 
sein) und dann kam das böse Erwachen, stimmte hinten und vorne nicht.
Bisher war ich immer der Meinung, Excel kann (ausser Diagrammen, das ist 
nicht toll) so ziemlich alles numerische gut. Inzwischen habe ich 
gelesen, dass die Trendlinie ziemlich miese Ergebnisse liefert - warum 
auch immer. Wieso legt der ne passende Kurve da hin, liefert aber ne 
ziemlich falsche Gleichung ab?

Selbst ermittelte Koeffizienten:
a: -3,31882660411389E-07
b: 0,000546655625451365
c: -0,394156344836167
d: 167,698104632209

[Yalu X. (yalu) (Moderator)]

H.Joachim S. schrieb:
> Bisher war ich immer der Meinung, Excel kann (ausser Diagrammen, das ist
> nicht toll) so ziemlich alles numerische gut.

Ist es nicht eher umgekehrt? Der typische Excel-User will keine
Mathematik, sondern bunte Tortendiagramme. Demzufolge hat MS die
gesamte Entwickler-Power auf diesen Diagrammtyp fokussiert. Dinge
wie Approximationen versteht ein BWLer sowieso nicht, deswegen
fällt es niemandem auf, wenn hier die Ergebnisse ungenau sind ;-)

Hier gibt es eine Erklärung und auch eine Lösung für das Problem:

  https://support.microsoft.com/en-us/help/211967/chart-trendline-formula-is-inaccurate-in-excel

[Bernd K. (prof7bit)]

sowas würd ich sowieso von Anfang an mit python/numpy/matplotlib machen, 
ist doch auch viel besser zu handhaben und nachzuvollziehen als das 
elende rumgeklicke in Excel.

[H.Joachim S. (crazyhorse)]

Danke, das klingt gut, werde ich gleich mal ausprobieren. Ich habe zwar 
jetzt eine ausreichende Lösung, aber vielleicht kommt ja was ähnliches 
mal wieder.
Die Vorgehnsweise von Microsoft sollte eigentlich andersherum sein. 
Erstmal max. Genauigkeit verwenden und wem das zuviel ist kann dann 
ungenauer einstellen.
Mit ner Tabelle wäre ich schon lange fertig gewesen :-)

Witzigerweise liefert die lineare Trendfunktion (y=f(x)=-0,1277x+129,27) 
an jedem einzelnen Wertepaarstelle einen deutlich kleineren Fehler als 
das Excel-Polynom-Konstrukt.

Zu Tortendiagrammen kann ich nichts sagen, die habe ich noch nie für 
irgendwas gebraucht :-)

[Peter M. (r2d3)]

Hallo H.Joachim Seifert,

wie bist Du schlussendlich an die besseren Koeffizienten gekommen?

Ich hätte die Koeffizientensuche als Optimierungsproblem im Solver 
formuliert.

[Lukas T. (tapy)]

Magst du gerade noch einmal schnell darlegen, warum du meckerst, wenn 
Excel die Darstellung (IM BILD) auf etwa vier Stellen rundet und damit 
berechnete Ergebnisse verglichen mit ~16 Stellen (die Excel bei der 
Berechnung der DARGESTELLTEN Punkte natürlich auch verwendet) Mist sind?

Anwenderfehler sind keine Programmfehler.
Auch wenn ich Excel nicht mag: Diesmals ist eher der TE doof.

[H.Joachim S. (crazyhorse)]

@Peter M.
4 Wertepaare -> 4 Gleichungen -> Matritzenrechnung -> fertig
Wertepaar 1:
0=a*958^3 + b* 958^2 + c*958 + d
.
.
100=a*240^3 + b*240^2 +c*240 +d

Lukas: Mist ist die von Excel ausgeworfene Gleichung (im Diagramm 
ersichtlich). Mir scheint eher, dass du doof bist als ich, wenn wir 
schon auf dieses Niveau herabsinken.

[Peter M. (r2d3)]

H.Joachim S. schrieb:
> @Peter M.
> 4 Wertepaare -> 4 Gleichungen -> Matritzenrechnung -> fertig
> Wertepaar 1:
> 0=a*958^3 + b* 958^2 + c*958 + d
> .
> .
> 100=a*240^3 + b*240^2 +c*240 +d

Ah ja!

Wenn Du das in Excel via Solver formulierst, kannst Du anstelle Deiner 
vier Stützstellen gleich alle 20 verwenden:

Du minimierst einen Fehlerterm. Der ergibt sich aus der Summe aller 
Einzelfehlerterme.
Ein Einzelfehlerterm ist dann z.B. die quadrierte Differenz zwischen den 
gegebenen Funktionswerten und den interpolierten Werten.

Die Zellen, an denen der Solver rumspielen darf, sind dann die, in denen 
die Koeffizienten a,b,c und d stehen.

[Bernhard D. (pc1401)]

H.Joachim S. schrieb:

> Bisher war ich immer der Meinung, Excel kann (ausser Diagrammen, das ist
> nicht toll) so ziemlich alles numerische gut. Inzwischen habe ich
> gelesen, dass die Trendlinie ziemlich miese Ergebnisse liefert - warum
> auch immer. Wieso legt der ne passende Kurve da hin, liefert aber ne
> ziemlich falsche Gleichung ab?

Die Darstellung der Funktion im Plot ist nicht falsch, sondern großzügig 
gerundet. Ist ärgerlich, aber wohl nicht zu ändern.

> Selbst ermittelte Koeffizienten:
> a: -3,31882660411389E-07
> b: 0,000546655625451365
> c: -0,394156344836167
> d: 167,698104632209

Die Koeffizienten nimmt man auch nicht aus der Grafik, sondern benutzt 
die Rechenfunktionen von Excel (IMHO RGP(), TREND() ).
Versuche es, Du wirst Dich wundern...

[H.Joachim S. (crazyhorse)]

Mit der Darstellung war ich völlig zufrieden, auch mit den Fehlern, die 
sich aus nur 3.Grades ergeben. Ärgerlich war, dass die angezeigte 
Funktionsgleichung nur wenig mit der dargestellten (ausreichend 
passenden) zu tun hat.
edit:
Ja, geht alles. Ich bin halt drüber gestolpert, dass das, was da steht 
schlicht falsch ist (bzw. so grosszügig gerundet, dass es keinen Sinn 
mehr macht. Warum bei c und d 4 Dezimalstellen, aber bei a und b nur 
eine?)

[Wolfgang (Gast)]

H.Joachim S. schrieb:
> Inzwischen habe ich gelesen, dass die Trendlinie ziemlich miese
> Ergebnisse liefert - warum auch immer.

Welche Koeffizienten hast du denn verwendet, um die Zahlen in der Spalte 
"Excel Trendformel" zu berechnen? Doch nicht etwa die grob gerundeten 
Werte aus der Formel, die in der Graphik eingeblendet ist, oder?

Falls letzteres der Fall ist, liegen die miesen Ergebnisse nicht an der 
Trendlinieberechnung, sondern an der kräftigen Rundung der Koeffizienten 
durch die gewählte Zahlenformatierung bei der Anzeige der 
Trendlinienformel.

Lass dir dort mal ein paar mehr Nachkommastellen anzeigen oder rechne 
die Koeffizienten über die entsprechende Funktion aus.  ;-)

[H.Joachim S. (crazyhorse)]

Wolfgang schrieb:
> Doch nicht etwa die grob gerundeten
> Werte aus der Formel, die in der Graphik eingeblendet ist, oder?

Doch, erst mal ja.
Ist ja heutzutage kein Problem mehr, mehr  Stellen anzuzeigen. Ist ja 
erst mal nicht ersichtlich dass die massiv (bis zur 
Unkenntlichkeit/Unverwendbarkeit) gerundet sind. Dann schreib ich doch 
lieber gar nichts hin als so einen Mist. Noch dazu, da das nicht 
durchgehend so ist - c und d lassen vermuten, dass eine höhere 
Genauigkeit verwendet wird. Mit durchgehend 4 Deziamlstellen wäre das 
brauchbar gewesen. So aber nicht.

[Wolfgang (Gast)]

H.Joachim S. schrieb:
> Ist ja heutzutage kein Problem mehr, mehr  Stellen anzuzeigen.

Der bessere Weg ist - wie vorgeschlagen -, die Parameter der Kurve 
direkt berechnen zu lassen und nicht aus in der Graphik eingeblendeten 
Formel abzuschreiben.

> Ist ja erst mal nicht ersichtlich dass die massiv (bis zur
> Unkenntlichkeit/Unverwendbarkeit) gerundet sind.

Eine einzige gültige Ziffer sollte einem aber sofort verdächtig 
vorkommen und der erste Schritt ist dann doch wohl, sich das 
Zahlenformat für die Formeldarstellung anzugucken.

[H.Joachim S. (crazyhorse)]

Alles richtig, und es hat mich ja auch direkt stutzig gemacht, dass a 
und b nur so einfach dargestellt werden.
Die eigentliche Frage: warum suggeriert man mit c und d, dass eine 
höhere Genauigkeit vorhanden wäre? Warum rundet man a und b anders als c 
und d? Entweder durchgehend massiv gerundet (dann wäre es wirklich 
direkt ersichtlich) oder durchgehend mit gleicher Genauigkeit. Dieser 
Mischmasch macht keinerlei Sinn.

[Arno (Gast)]

Weil vermutlich irgendjemand das Zahlenformat mal so festgelegt hat, 
dass pro Koeffizient maximal fünf Zeichen oder vier signifikante Stellen 
angezeigt werden sollen. 3e-07 sind fünf Zeichen. 0,0005 sind fünf 
Zeichen (plus Komma). 0,3589 sind fünf Zeichen (plus Komma). 164,1 sind 
vier signifikante Stellen. Soll ja nicht genau sein, sondern nur einen 
groben Eindruck über die Art der Kurve geben. Wenn die Koeffizienten 
sehr unterschiedlich groß sind, kann man auch überlegen, ob die sehr 
kleinen Koeffizienten überhaupt sinnvoll/nötig sind oder ob eine 
quadratische/lineare Näherung nicht ausreicht, das zeigt die Formel 
noch.

Keine Ahnung, ob und wo die Anzeige konfigurierbar ist.

MfG, Arno

[Wolfgang (Gast)]

H.Joachim S. schrieb:
> Die eigentliche Frage: warum suggeriert man mit c und d, dass eine
> höhere Genauigkeit vorhanden wäre? Warum rundet man a und b anders als c
> und d?

Da stecken wohl etwa 30 Jahre Erfahrung mit Tabellenkalkulationssoftware 
von einer großen Softwarefirma hinter. :-(

[Wolfgang (Gast)]

Arno schrieb:
> Weil vermutlich irgendjemand das Zahlenformat mal so festgelegt hat,
> dass pro Koeffizient maximal fünf Zeichen oder vier signifikante Stellen
> angezeigt werden sollen.

Warum auch immer hier gerade 5 Nachkommastellen als Default eingestellt 
sind, ist es in solchen Fällen doch die erste Tat, das Zahlenformat für 
die Darstellung in der Formel zu ändern,

[xyz (Gast)]

Rechtsklick in die angezeigte Formel, dann Formatierung?

[Yalu X. (yalu) (Moderator)]

Bernd K. schrieb:
> sowas würd ich sowieso von Anfang an mit python/numpy/matplotlib machen

import numpy as np

xs = np.array([ 958, 941, 921, 897, 870, 839, 804,
                765, 724, 680, 635, 589, 542, 497,
                453, 411, 371, 334, 300, 269, 240 ])

ys = np.linspace(0, 100, 21)

cs = np.polyfit(xs, ys, 3)

print(cs)

Ergebnis:

[-2.68906282e-07  4.57026132e-04 -3.61151868e-01  1.64458578e+02]

H.Joachim S. schrieb:
> 4 Wertepaare -> 4 Gleichungen -> Matritzenrechnung -> fertig

Im Anhang gibt's den Fehlerplot für beide Verfahren.

[H.Joachim S. (crazyhorse)]

Super, Danke.
Excel kann es auch ganz gut, mit fast identischem Fehlerverlauf.

xyz schrieb:
> Rechtsklick in die angezeigte Formel, dann Formatierung?

so gehts.

[Meister Röhricht (Gast)]

Der echte Elektroniker hätte seinen Sharp PC-1403 aus der Schublade
geholt und damit die Lösungen des minimierenden Gleichungssystems
bestimmt. Aber doch nicht mit so einem Mist wie Exel.

Ihr seid alle unfähig!

[Nebeltroll (Gast)]

Weshalb sollte man irgendwelche Werte in einem Sharp eintippen, wenn man 
alles auf dem PC hin und her copy pasten kann.

[Bernd K. (prof7bit)]

Nebeltroll schrieb:
> Weshalb sollte man irgendwelche Werte in einem Sharp eintippen,
> wenn man
> alles auf dem PC hin und her copy pasten kann.

Warum soll man umständlich Copy-Pasten wenn man es von einem Script 
direkt aus einer Datei einlesen lassen kann?

[eProfi (Gast)]

Hatten wir 2011 schon mal:
Beitrag "PT100: Mathematisches Problem"
Suchbegriffe waren "excel trendlinie formel" mit 5 Treffern.

[Meister Röhricht (Gast)]

Ja,

> 5 Treffer

und versenkt!

[Yalu X. (yalu) (Moderator)]

H.Joachim S. schrieb:
> Excel kann es auch ganz gut, mit fast identischem Fehlerverlauf.

Auch wenn sowohl die Numpy- als auch die Excel-Ergebnisse für den
vorliegenden Anwendungsfall mehr als ausreichend genau sind, hat es mich
jetzt doch interessiert, welches der beiden Tools näher am theoretisch
richtigen Ergebnis liegt.

Dazu habe ich die Polynomkoeffizienten mit einem weiteren Tool, nämlich
wxMaxima nach der Methode der kleinsten Quadrate exakt berechnen lassen.

Ergebnisse:

a =          -8536676654970643591975 /   31745917575274522495028685848
b =      522313700937540573913048415 / 1142853032709882809821032690528
c =  -137581169182481693004239233135 /  380951010903294269940344230176
d = 46987996036471179121783722044275 /  285713258177470702455258172632

Die drei Tools im Genauigkeitsvergleich:

a:
wxMAxima: -2.689062817204402e-07    auf 16 Stellen gerundet
Numpy:    -2.689062817204375e-07    Genauigkeit: 14 Stellen
Excel:    -2.66354          e-07    Genauigkeit:  2 Stellen

b:
wxMAxima: +4.570261319594640e-04    auf 16 Stellen gerundet
Numpy:    +4.570261319594604e-04    Genauigkeit: 14 Stellen
Excel:    +4.52752151       e-04    Genauigkeit:  2 Stellen

c:
wxMAxima: -3.611518679429549e-01    auf 16 Stellen gerundet
Numpy:    -3.611518679429534e-01    Genauigkeit: 14 Stellen
Excel:    -3.58943597910    e-01    Genauigkeit:  2 Stellen

d:
wxMAxima: +1.644585775829997e+02    auf 16 Stellen gerundet
Numpy:    +1.644585775829994e+02    Genauigkeit: 15 Stellen
Excel:    +1.64104759549167 e+02    Genauigkeit:  3 Stellen

Wie man sieht, liegt die Genauigkeit der Koeffizienten bei Numpy fast
schon im Bereich der Auflösung von Double-FP-Zahlen (ca. 16 Stellen).
Im Vergleich dazu muten die Ergebnisse von Excel eher wie eine grobe
Schätzung an.

Betrachtet man allerdings den mittleren quadratischen Fehler der
resultierenden Approximation, ist der Unterschied nur marginal:

wxMAxima: 0.55536
Numpy:    0.55536
Excel:    0.55619

Trotzdem stellt sich natürlich die Frage, warum MS hier mal wieder das
Rad neu erfindet und nicht einfach einen der öffentlich zugänglichen und
nachweislich besseren Algorithmen verwendet.

Generell würde ich bei solchen numerischen Anwendungen Numpy/Scipy oder
Matlab weit mehr als Excel vertrauen. Numpy und Matlab basieren beide
auf der LAPACK-Bibliothek, die den Industriestandard in diesem Bereich
darstellt und nach einem Vierteljahrhundert¹ Entwicklungszeit und
intensiver Nutzung (auch außerhalb von Numpy und Matlab) als absolut
ausgereift betrachtet werden kann.

—————————
¹) Rechnet man die Vorgänger LINPACK und EISPACK mit ein, sind es noch
   einmal zwei Jahrzehnte mehr.

[H.Joachim S. (crazyhorse)]

Vielleicht hat sich ja inzwischen da auch was geändert - ich verwende 
Excel 2010.

Aber wie schon gesagt - für mich mehr als ausreichend gut.

[Tom (Gast)]

Der Vollständigkeit halber noch Gnuplot (GP) und LibreOffice Calc (LO, 
hier mit übertrieben vielen Stellen formatiert, um nichts zu übersehen) 
im Vergleich zu den richtigen Werten aus wxMaxima (wx)

a:
wx -2.689062817204402e-07
LO -2.6890628172044000000E-07
GP -2.68906281719567e-07

b: 
wx 4.570261319594640e-04
LO 4.5702613195946400000E-04
GP 4.57026131957835E-04

c:
wx -3.611518679429549e-01
LO -3.6115186794295400000E-01
GP -3.361151867942018E-01

d:
wx 1.644585775829997e+02 
LO 1.6445857758299900000E+02
GP 1.64458577582838e+02

Yalu X. schrieb:
> warum MS hier mal wieder das
> Rad neu erfindet und nicht einfach einen der öffentlich zugänglichen und
> nachweislich besseren Algorithmen verwendet.
Weil sie das immer so machen. Wenn es für irgendein Problem eine 
Standardlösung gibt, die der Rest der Welt seit Jahrzehnten verwendet 
und die kompatibel, problemlos und korrekt funktioniert, verwendet MS 
irgendwas halbgares selbstgebasteltes, das dank ewiger 
Rückwärtskompatibilität nie repariert wird.