Office: (Office 2007) Anzeige Formel einer exponentiellen Trendlinie

Hallo zusammen,
ich habe eine Frage zu den Formeln, die in Diagrammen ausgewiesen werden sowie zu einer korrekteren Berechnung über den Solver in Abhängigkeit zu den maximalen Änderungen.

Also der Reihe nach:
Wenn ich folgende Werten für die X-Reihe:
12 18 24 30 36 42 48 54 60 66 72
…diese Werte für die Y-Reihe zuordne:
860 582 442 359 303 264 234 211 193 178 165
…daraus ein Diagramm erstelle und eine exponentielle Trendlinie hinzufüge, so soll selbige die Formel
y = 918,95x^-0,7033
haben.
Auf Grund des Bestimmtheitsmaßes von 0,9961 bin ich davon ausgegangen, daß die Formel so gut ist, daß eigene Berechnungen zum annähernd ähnlichen Ergebnis kommen.
Pustekuchen.
Die Y-Werte sind wie folgt (für den Post gerundet):
160 120 98 84 74 66 60 56 52 48 45

meilenweit daneben!
Also habe ich die beiden Variabeln aus der Formel in extra Zellen gepackt, die absoluten Differenzen zwischen Soll und Ist-Werten summiert und dem Solver gesagt, er möge diese Differenz auf Null bringen, indem er die beiden Zellen mit den Variabeln ändert.
Das Ergebnis lag meilenweit daneben.
Dann habe ich unter Extras > Optionen > Berechnen
den Wert für maximale Änderungen auf "1" gestellt und beim nächsten Solverlauf war die absolte Differenz plötzlich nur noch minimal (die Formel wäre besser:
Y = 8560,16X^-0,93

Daraus ergeben sich für mich zwei Fragen:

1) Warum sind die angezeigten Formeln in den Diagrammen so grottenschlecht?
2) Wie hängt der Wert unter "Maximale Änderungen" mit dem Solver zusammen?

Da ich öfter aus externen Daten auf eine Formel rückschliessen muß, würde ich mich über eine Antwort sehr freuen!
Mein Ansatz hilft mir zwar nun schon immens weiter - aber ich denke, "da geht noch was".

Grüße
Struschie

:)

 

Also bei einer exponentiellen Trendlinie bekommst du keine Formel in der Form a*x^b. Die Formel sollte so aussehn: a*e^(x*b). Mal davon abgesehen, das die exponentielle Trendlinie nicht die richtige für dein Problem ist, kommt natürlich nur noch Mist raus wenn man die Konstanten in die falsche Formel einsetzt.

Wenn du eine potenzielle Trendlinie auswählst erhälst du eine Formel in der Form a*x^b und dann stimmen die Ergebnisse auch.
Mit einer Trendlinie erhälst du, neben dem Bestimmtheitsmaß auch noch einen optischen Eindruck genau die Formel die Ergebnisse berechnet. Hinschauen hilft also ;-)

 

Hi,

nein - richtig schreiben hilft:
Natürlich ist es eine potentielle Trendlinie - sonst hätte ich ja auch eine andere Formel bekommen (wie du richtig schreibst).
Anbei die Tabelle - mit der Trendlinie (paßt ähnlich gut, wie das R2 verspricht) und der errechneten, weißen Linie.

Danke für Feedback!

Grüße
struschie

 

Die Formel für die Trendlinie stimmt doch genau. Du darfst die Werte natürlich nicht mit den Beschriftungen berechnen. Wenn du die X-Werte des Diagramms nimmst (Werte von 1 bis 11) stimmt die berechnete Linie mit der Trendlinie überein.
Wenn du deine X-Werte nicht nur als Beschriftung der X-Achse nehmen willst solltest du kein Liniendiagramm sondern ein Punkt (x-y)-Diagramm verwenden.

 

Hallo Struschie,

Statt in B3 =$B$4*B1^-$C$4 schreib mal
=$B$4*(SPALTE()-1)^-$C$4 und kopier sie bis L3

Geht da ein Lichtlein an ???

 

Nun, Du hast mind. zwei Probleme.
Wenn man Mathematik mit Diagrammen betreibt ist ein xy-Diagramm zwingend notwendig! Zum anderen sind Potenzen extrem abhängig von der Genauigkeit der verwendeten Werte. Mann sollte auch negative Potenzen grundsätzlich Klammern, sonst vertut sich der Parser gerne.
Also bring mal die beiden Phänomene auf die Reihe, dann klappt das auch mit dem Nachbar...

 

Moinsen,

ihr seid so geil ;P
Vielen Dank für diesen Tip - das war so naheliegend, das tut ja schon weh.*mrcool

Bzgl. Genauigkeit der Potenzen:
Gerade hierfür würde ich gerne weiterhin auch auf meinen Solver-Vorlschlag zurück kommen - dann kommen nämlich - im gegensatz zu den paar Angaben im Diagramm - vernünftig lange Werte dabei heraus:

Kann mir jemand sagen, wie die Angabe unter "Maximale Genaugikeit" mit dem Solver korrespondiert?

Grüße
struschie

 

Hallo Struschie,

Was verstehst Du unter:

Excel kann intern mit 15 signifikanten Stellen rechnen. Beim Solver kann man höchstens die Anzahl Iterationen erhöhen und die Genauigkeit erhöhen und bessere Näherungswerte zu bekommen.

 

\@struschie:
Hm, und wer hindert Dich in Deiner Trendgleichung die Anzahl der Nachkommastellen zu erhöhen?

 

Hi all,

wie im ersten Post beschrieben, rechnete der Solver erst bei Anpassungen der "maximalen Genauigkeit" (Karteikarte s.o.) gut:
Je größer dieser Wert wurde, desto geringer die Abweichungen zwischen den errechneten & vorgegebenen Punkten.

Aber - :grummel: -ich kann das Problem nicht nachstellen...
Bei Versuchen habe ich bessere Ergebnisse gefunden, wenn ich die beiden Variabeln sehr abwegig/weit weg gesetzt habe - siehe Anlage.

Ansonsten:
Vielen Dank für die rege Beteiligung - der Hinweis auf den ScatterPlot hat mir sehr geholfen und nun bin ich auch wieder beruhigt, daß "mein Excel" funktioniert *tongue.gif*


Grüße
struschie