Thema: Bestimmtheitsmass gibt anderen Wert als im Diagramm aus

Liebes Forum,
ich habe folgendes Problem:
Ich erstelle mit einer Datenreihe ein Diagramm und will das Bestimmtheitsmaß der logarithmischen Trendlinie berechnen:

Das sind die Werte:
127.69
187.55
241.16
319.98
388.48
456.39
511.4
649.13
824.17
993.62
1137.73
1292.63
1471.54
1604.19
1730.31
Als R2 (Bestimmtheitsmaß) wird mir im Diagramm 0,9627 angezeigt. Dieses Ergebnis bekomme ich aber nicht mit der Formel, weder mit Bestimmtheitsmass, noch mit INDEX(RGP) oder INDEX (RKP). Könnt ihr mir helfen, wie ich auf das Ergebnis 0,9627 komme, ohne für jede Datenreihe ein Diagramm erstellen zu müssen?
Vielen Dank im Voraus!

nicht logarithmisch, sondern exponentiell natürlich :-)

Das Bestimmtheitsmaß R2 ist nur für lineare Zusammenhänge definiert.
Deshalb die Werte der Datenpunkte vorher logarithmieren (dann wird es eine Gerade, also linear) und dann erst R2 berechnen!
Die Basis beim Logarithmieren ist egal - hier im Beispiel: 10
https://www.herber.de/bbs/user/168527.xlsx

Siehe auch:
https://gramschs.github.io/book_matlab/part11.html

Das ist aber doch gerade das Thema.
Excel wirft in der Graphik ein R² aus, das eben nicht mit dem R² aus der linearen Regression log(Y) und X übereinstimmt.

Naja, Excel macht eben genau das Richtige, was man bei einem exponentiellen Zusammenhang machen muss!?
Er rechnet intern, wie ich beschrieben habe.

Letztendlich muss man bei jedem Zusammenhang (z. B. quadratisch) vor dem Berechnen des Bestimmtheitsmaß erst die Umkehrung anwenden - dann also Wurzel ziehen...

Man muss das nur wissen / verstehen...

Genau das ist der Punkt. Der Wert im Diagramm kommt bei der Formel Bestimmtheitsmass heraus, wenn man die Messwerte y und die perfekten Werte der exponentielle Kurve als x Werte benutzt, so wie im Diagramm. Die x Werte kann man mit Log und dem Schnittpunkt der y-Achse mit der Wachstumsrate berechnen.

Wenn man als x-Werte die Standardzahlen 1-15 benutzt, kommt der höhere Wert deiner Excel-Datei heraus.

Du zeigst doch in deinem Excel genau das Problem, nämlich dass dein berechnetes R² nicht mit dem in der Graphik übereinstimmt.
Keine Ahnung, was du mir erzählen willst.
=INDEX(RKP(D5:D19;B5:B19;WAHR;WAHR);3;1)
hätte dir auch nichts anders erzählt als was du da als Bestimmtheitsmaß gerechnet hast.
Irgendwie fehlt mir bei dir Bezug zum Thema. Hab ich was übersehen?

Ich verstehe den Unterschied zwischen Formelergebnis und Diagrammergebnis nicht.

In diesem Zusammenhang wollte ich wissen, wie Excel im Diagramm auf den r2 Wert kommt.

Das weiß ich nun, in dem man die X-Werte der Log-Kurve und nicht einfach nur "1,2,3,4,..." benutzt.

Leider weiß ich immer noch nicht, ob ich nun den Diagrammwert nehmen oder mit der Formel weiterrechnen soll.

Mein Vorgängerpost war eigentlich an Peter Trawinski gerichtet, aber gut.
Du sagst:
"In diesem Zusammenhang wollte ich wissen, wie Excel im Diagramm auf den r2 Wert kommt.
Das weiß ich nun, in dem man die X-Werte der Log-Kurve und nicht einfach nur "1,2,3,4,..." benutzt."

Du meinst, dass Excel in der Graphik für die Berechnung von R² nicht die eigentlichen X-Werte in der Formel für das Bestimmtheitsmaß benutzt, sondern stattdessen die Y-Werte, die sich anhand der gegebenen X-Werte aus der Regressionskurve ergeben würden.

Wie dem auch sei. Regressionskoeffizient hin oder her...
Du hast bei X = 15 einen Y-Wert von ca.1730 mit deiner Kurve aber einen Wert von ca. 2166, also eine Abweichung von ca. 436, das find ich ganz schön daneben.
Wenn das für dich gut genug ist, dann gut, sonst halt nicht.

Die Werte ergeben sich aus der Steigung, bzw. dem jährlichen Wachstumswert. D.h. nichts anderes, als dass der letzte Wert nicht mehr so gestiegen ist, wie er sollte. Ich will die Werte finden, die möglichst nahe an der exponentiellen Regressionskurve sind. Das sind nur Beispieldaten und im Endeffekt geht es hier um Umsätze von Firmen, davon habe ich eine Liste mit vielen... und ich würde gerne die Firmen finden, deren Umsatz am ehesten so steigen, wie es die exponentielle Kurve vorsieht. 0,96x und 0,97x sind ja beides super Werte, aber es ist eben doch ein Unterschied. Und nicht zuletzt will ich einfach verstehen, warum Excel im Diagramm die Werte der exponentiellen Kurve nimmt und in der Formel werden einfach nur lineare Datenreihen (Jahreszahlen, 1,2,3,4...) von euch benutzt. Das leuchtet mir leider trotz eurer Antworten nicht ein.

Sorry dafür, bin weder Excel- noch Matheprofi....und trotzdem ein dickes Danke für eure Zeit.

Ich verstehe deine letzten Antworten nicht richtig.
1. Der Wert im Diagram ist der Richtige - den "solltest du nehmen"!
2. Der Wert errechnet sich aus den Logarithmen deiner Werte - schau doch mal in die Formel im Excelblatt!
3. Mit den Formeln dort brauchst du kein Diagram erzeugen.

Alles Andere wäre falsch!

Warum Excel da so rechnet, kann ich dir nicht sagen. Meines Wissens ist aber R^2 anders definiert, funktioniert praktisch nur für lineare Regression.
Die Regression einer Exponentialfunktion wird praktisch auf eine lineare Regression zurückgeführt, aus der dann auch ein R^2 ermittelt werden kann.
Wenn du in deiner Graphik die y-Achse logarithmisch machst, wird die Trendline als Gerade erscheinen. Die scheinbar gute Annäherung der logarithmierten Werte, kann dann, wie man in deinem Beispiel gut sieht, durchaus weit daneben liegen.
Die Aussagekraft des Bestimmtheitsmasses ist bei nicht linearen Funktionen damit zweifelhaft.
Interessant wäre jetzt, inwieweit die Methode, die Excel da bei deiner Graphik verwendet hat, auch bei linearer Interpolation funktionieren würde.

Diagramm:0.9627, Berechnung 0,9766. Auch in deiner Excel! Deswegen der ganze Thread. Sorry, ich verstehe es nicht!

Diagram: 0,9766, Berechnung in H22: 0,9766 ???

Also in meinem Excel 2016 sieht das in der Grafik anders aus, beim TO mit 2022 wohl auch...

OK, sorry, war mir nicht aufgefallen!

Wenn du jetzt in deinem Excel 2016 meine Excelmappe öffnest, steht dann da im Diagram für R2 ein andferer Wert als bei mir - also NICHT 0,9766?
In meinem Excel 2019 sind das die Einstellungen für die Trendlinie:



- Wenn sich das verändert, macht eine der Excel-Versionen einen Fehler.
- Wenn es sich nicht verändert, habt Ihr irgendwas falsch eingestellt!

Ich mach DEIN Workbook auf und habe einen anderen Wert und nicht nur ich. An welcher Einstellung soll das denn liegen?

Tatsächlich. Schau dir deine Datei in meinem Excel an:

Das ist ja der Hammer!!

Aber da die Formel in H22 noch stimmt, muss was mit der Berechnung von R2 im Diagram falsch sein bei Euren Excel-Versionen!
Das ist ja kaum zu glauben!!

Aber du kannst dich darauf verlassen, dass das Formelergebnis in H22 stimmt. Du willst ja eh ohne Diagram arbeiten - also nimm einfach die Formel und dein Problem sollte gelöst sein.

ich hab jetzt mal hiermit
https://www.microsoft.com/de-de/microsoft-365/free-office-online-for-the-web
durchgespielt.
Das haut in der Graphik 0,9627 auch raus... also ich versteh es nicht mehr
Man liest zwar überall dass es da Korrekturen gab und insbesondere bei vorgegebenem Schnittpunkt Probleme auftreten, aber den Fall haben wir ja nicht.
Bei 365 sollte das aber... bhhhh
Ob da auch Unterschiede raus kommen, wenn das jemand mit nem anderen Rechner macht ?

Die Excel-Funktion "Bestimmtheitsmaß" bezieht sich in aller Regel auf lineare Zusammenhänge und ist bei nicht linearen Zusammenhängen nicht unbedingt aussagekräftig.
In meiner und der Excel-Version des TO wird für die exponentielle Trendkurve ein R^2 geliefert, das über die Funktion Bestimmtheitsmaß nur errechnet werden kann, wenn man statt der X-Werte die zugehörigen Y-Werte der von der Regression gelieferten Funktion einsetzt, was ziemlich umständlich ist.
Das dürfte aber unter dem Strich in diesem Fall eine bessere Aussage über die Kurvenanpassung liefern.
Ich habe ein wenig gespielt und zu den Meßwerten die Gerade Y = X * 16 + 470 addiert und das Ergebnis dann einer Regression unterzogen.
Hier liefert die Graphik dann ein R² von 0,9928, die RKP liefert ein Bestimmtheitsmaß von 0,9961
Bei der anhand der Regression ermittelten Funktion, muss dann natürlich die zuvor addierte Gerade wieder subtrahiert werden.
So erhalte ich die Funktion Y=561,2332*e^(0,10188* X ) - (16 * X + 470) und für die Meßwerte (mit X = 1,2,3... 15):
127.69,187.55,241.16,319.98,388.48,456.39,511.40,649.13,824.17,993.62,1137.73,1292.63,1471.54.,1604.19,1730.31
die Funktionswerte:
135.43,186.08,243.88,309.60,384.08,468.26,563.19,670.02,790.02,924.61,1075.35,1243.98,1432.40,1642.76,1877.39
und bin damit bei einer maximalen Abweichung von etwa 147 (X=15) und Abweichungen insgesamt bis ca. 10%.
ohne den "Trick" waren wir mit Y=140,9186 *e^(0,18217* X) bei einer maximalen Abweichung von ca. 436 (X=15).
169.08,202.86,243.40,292.03,350.39,420.40,504.40,605.19,726.12,871.21,1045.30,1254.17,1504.78,1805.46,2166.22
Aber Hallo ?! Da gibt es Abweichungen von über 30%.
Wenn ich dann das Bestimmtheitsmaß von 0,9766 sehe, wie es zum Beispiel auch die Funktion RKP liefert..
da sehe ich die Angabe der Graphik (bei mir und dem TO) mit 0,9627 eher als gerechtfertigt an.
Es scheint aber leider ja so zu sein, dass Excel hinsichtlich der Ausgabe von R² für die Trendkurve nicht einheitlich arbeitet.
Den Wert der Graphik findet man dann auch z.B. in der Funktion RKP nicht wieder und so wird das Ganze für den Anwender zum Verwirrspiel.

Ja, mir kam der Diagramm-Wert auch realistischer vor. Auf der anderen Seite sind die Werte bei einer anderen Excel-Version (?) ja offensichtlich gleich, wodurch eigentlich nur der Formelwert stimmen kann.

Ich muss dir jedenfalls nochmals gratulieren, dass du herausgefunden hast, was da wahrscheinlich gerechnet wird und mir scheint das durchaus sinnvoll, aber ich bin auch kein Mathematiker, insofern .. naja..
Ich hatte übrigens deine Werte auch mal bei Wolfram-Alfa rein geworfen.
https://www.wolframalpha.com/input?i=regression+exponential
Die führen das dort scheinbar nicht auf eine lineare Regression zurück und liefern ein anderes ( besseres? ) Ergebnis als Excel.
Wenn ich mir da dann den Regressionskoeffizienten anschaue, dann frag ich mich aber auch wieder, wie die dahin kommen.
Es scheint, dass es bei nicht linearen Regressionen eher nicht so einheitlich definiert ist, wie da R² zu berechnen ist....

Es ist natürlich irgendwo inkonsistent wenn die eine Excel-Version so rechnet, die andere wieder anders und dazu die Werte aus Graphik weder nachvollziehbar sind, noch durch eine Funktion unterstützt werden, aber...
Ich weiß nicht, ob ich das schon gesagt hatte, die Methode für das Bestimmtheitsmaß die Funktionswerte statt der X-Werte heran zu ziehen geht bei Linearer Regression auch und liefert da aber genau das Gleiche R^2 wie mit den X-Werten. Irgendwie logisch.

Da Excel bei RKP im Grunde die Meßwerte (Y-Werte) logarithmiert, um damit dann eine lineare Regression zu machen, erscheint das entsprechende Bestimmtheitsmaß recht gut. Würde man jetzt die resultierenden Funktionswerte gegen die Meßwerte auftragen, dann würde die Verfälschung durch das Logarithmieren wegfallen.
Man bekäme etwa eine Gerade mit der Steigung 1, aber hätte da dann halt auch die unverfälschten Abweichungen und das entsprechende R² der Ausgleichsgeraden hielte ich hier jedenfalls für angemessener.
Ich schätze Microsoft wird RKP aber eher nicht dahingehend ändern.
Man könnte sich aber mit VBA etwas zusammen basteln.

Was RKP an Bestimmtheitsmaß liefert, bezieht sich halt auf die lineare Regression. Dafür wäre das auch richtig, aber es geht ja nicht um eine lineare Funktion von daher, kann man sich schon darüber streiten, ob das richtig ist. Immerhin variiert das wohl nicht zwischen Excel-Versionen, ob es damit richtiger wird, sei dahin gestellt.

Ich hab jetzt mal quasi eine modifizierte RKP Funktion gebastelt, wo letztlich nur das Bestimmtheitsmaß, das RKP liefern würde,
durch das Bestimmtheitsmaß ersetzt wird, von dem wir annehmen, dass es das ist, was die Graphik der exponentiellen Trendlinie (bei manchen) liefert.
Das ist dann halt eher hypothetisch:

Public Function RKP_mod(Y_Werte As Range, X_Werte As Range, Konstante As Boolean, stats As Boolean) As Variant

    

    Dim result As Variant

    result = WorksheetFunction.LogEst(Y_Werte, X_Werte, Konstante, stats)

    

    If stats Then

        'Ersetzt das von RKP gelieferte Bestimmtheitsmaß durch das Bestimmtheitsmaß,

        'das sich aus den Y-Werten und den Funktionswerten ergeben würde.

                

        Dim i As Long, j As Long, n As Long, m As Long

        Dim dblF As Double

        Dim dblX As Double, dblXQ As Double, dblXY As Double, dblY As Double, dblYQ As Double

        

        n = Y_Werte.Rows.Count

        m = X_Werte.Columns.Count

        

        For i = 1 To n

            dblF = result(1, m + 1)

            For j = 1 To m

                dblF = dblF * result(1, m + 1 - j) ^ X_Werte(i, j)

            Next

            dblX = dblX + dblF / n

            dblXQ = dblXQ + dblF ^ 2 / n

            dblXY = dblXY + dblF * Y_Werte(i) / n

            dblY = dblY + Y_Werte(i) / n

            dblYQ = dblYQ + Y_Werte(i) ^ 2 / n

        Next

        result(3, 1) = (dblXY - dblX * dblY) ^ 2 / (dblXQ - dblX ^ 2) / (dblYQ - dblY ^ 2)

    End If

    

    RKP_mod = result



End Function

Ich habe die Alternative auch so relativ einfach in Excel hinbekommen.
Die Y-Werte sind die Messwerte.
Den ersten X- Wert kann man (bei 11 Messwerten) mit
=10^INDEX(RGP("LOG von den Messwerten";);1;2)
berechnen
die anderen X-Werte mit dem Zinseszins und der Wachstumsrate
="Erster X-Wert"*(1+10^INDEX(RGP("LOG von den MEsswerten";);1;1)-1)^1 bzw. ^2, ^3 etc.

Das Diagrammergebnis wäre dann mit
=BESTIMMTHEITSMASS("Messwerte";"berechnete X-Werte") zu bestimmen.

Ich habe an anderen PCs getestet, in der Arbeit z.B., da stimmen Diagrammwerte und Formelwerte auch teilweise überein.

1. Deine "Lösung" scheint mir a) Recht aufwändig b) auf den ersten Blick bestenfalls bedingt richtig. Wenn du Makros verwenden kannst, dann könntest du meinen Code in ein allgemeines Modul kopieren und anschließend die Funktion RKP_mod genau so verwenden wie RKP. Für das Bestimmtheitmass also =Index(RKP_mod(X_Werte;Y_Werte;Wahr;Wahr);3;1)
2. Üblicherweise wird in Firmen die Software überall auf gleichem Stand gehalten. Es gibt wohl mal einen Update Zeitraum, in dem dann unterschiedliche Versionen existieren. Von daher wundert es mich, dass du da unterschiedliche Resultate gefunden hast. Waren das denn gleiche Excel-Versionen? Nicht dass es vielleicht an etwas anderem liegt, vielleicht ist das ja auch z.B. prozessorspezifisch, was ich mir aber nicht wirklich vorstellen mag.

Was bitte sind die "perfekten Werte der exponentielle Kurve" ?

Die y-Werte, die bei x1/2/3... genau auf der exponentiellen Kurve liegen. Siehe Excel Datei. Also die Werte, die r2 1,0 ergeben sollten.

Die meine ich
y(Exp-Funktion)
169.0768
202.8615
243.3970
292.0323
350.3858
420.3995
504.4032
605.1924
726.1211
871.2137
1045.2985
1254.1687
1504.7751
1805.4574
2166.2216

und um uns diese deine Erkenntnis mitzuteilen, hast du den Thread hier aufgemacht ?

Nein. Ich wollte wissen, ob der Diagrammwert oder der Formelwert der richtige ist. Dass der Diagrammwert mit den berechneten x-Werten berechnet werden kann, habe ich gerade erst in der Excel Datei gesehen.

Irgendwo schon interessant, aber im Grunde widerspricht das, was da in der Graphik steht der Definition von R², etwa nicht?

Keine Ahnung was Excel da macht,
bei mir kommt mit RGP und RKP ein Bestimmtheismass r^2 von 0,9766 raus.

Bei linearer Trendline käme dann eine größeres R^2 heraus, als bei exponentieller,
Wobei das R^2 bei linearer Trendline nachvollziehbar ist....