Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1452to1456
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Probleme mit Zielwertsuche und großen Zahlen

Probleme mit Zielwertsuche und großen Zahlen
01.11.2015 18:36:34
Sebastian
Hallo alle zusammen,
erstmal wollte ich euch für eure gute Arbeit danken. Auch wenn das hier mein erster Beitrag ist, haben mir euer Forum und die Beiträge hier als passiver Leser bisher an vielen anderen Stellen bereits weitergeholfen.
Nun zwingt mich ein Problem zu meinem ersten Beitrag, von dem ich keine Ahnung habe, ob es sich überhaupt vernünftig lösen lässt. Ich bin gerade dabei eine Zielwertsuche durchzuführen, bei dem ein Term jedoch bis zu mehrere hundert Zeichen lang werden kann (großer Binomialkoeffizient, ganze Zahl) und ein anderer Term sehr klein werden kann (Dezimalzahl mit vielen Nachkommastellen). Hierzu habe ich den relevanten Teil meines Arbeitsblattes mal hochgeladen: https://www.herber.de/bbs/user/101178.xlsx
Die Zellen in Zeile F enthalten die großen ganzzahligen Werte aus dem Binomialkoeffizienten, Spalte H die Formeln, deren Ergebnis mit in die Zielwertsuche einfließen. Die Summe dieser Formeln in H275 ist die Zielzelle, B13 die veränderbare Zelle. Ich möchte zum Beispiel wissen, welchen Wert B13 annehmen muss, damit H275 gleich 5 ist.
Nun zu meinem Problem: Ab Zelle H151 liefert die Formel nur noch den Fehler "#Wert!". Ich vermute mal, dass es daran liegt, dass die große Zahl ab hier länger als 256 Zeichen wird, hab aber keinen genauen Plan. Ich habe bereits ein Add-In gefunden, mit dem ich große Zahlen addieren, multiplizieren, usw. kann. Jedoch funktioniert dieses Add-In nur für GANZE Zahlen, also nicht für meine Terme mit Nachkommastellen.
Jetzt die Frage: Kennt jemand eine Lösung für mich? Ich habe bereits mehrere Rechner online gefunden, mit denen ich die Rechnung ausführen könnte und für den aktuellen Stand jeweils die Lösungen ermitteln könnte. Jedoch muss ich ja noch die Zielwertsuche ausführen, weshalb ich eben keine statische Lösung hier eintragen kann. Ist das Dilemma vielleicht mit VBA lösbar? Hier habe ich leider so gut wie keine Ahnung von.
Ich wäre jedem sehr dankbar, der mir behilflich sein kann!
(Sry für den langen Text...)

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Probleme mit Zielwertsuche und großen Zahlen
02.11.2015 08:59:46
EtoPHG
Hallo Sebastian,
Dir fehlt das Verständnis für die IEEE 754 Norm, bzw. für die Tatsache das Excel mit nur 15 signifikanten Stellen rechnen kann. Die untere und obere Grenze von Gleitkommazahlen liegt generell für
negative Werte zwischen -1,79769313486231e308 bis -4,94065645841247e-324
und für positive zwischen 4,94065645841247e-324 bis 1,79769313486232e308
mit deinen Zahl sprengst du diese Bereiche. Du musst die einfach Fragen, wie gross die Genauigkeit für deine Aufgabenstellung sein muss. Ich kann mir nicht vorstellen, dass du mit dem oben erwähnten Zahleraum nicht auskommst.
Gruess Hansueli

Anzeige
AW: Probleme mit Zielwertsuche und großen Zahlen
02.11.2015 12:49:13
Sebastian
Hallo Hansueli,
du hast Recht. Ich kannte die Norm tatsächlich nicht. Ich hatte ja nur vermutet, dass es an der Länge der Zahlen liegt. Gleichzeitig verstehe ich dann aber nicht, warum in meinem Beispiel schon die Formel in Zelle H151 kein Ergebnis mehr liefert. Der passende Binomialkoeffizient in Zelle F151 beträgt als Zahl ausgedrückt 'nur' 1,8839170593321E+255. Sollte also in dem von dir genannten Rahmen liegen.
Weiterhin: Ich habe den Bonimialkoeffizienten in Excel errechnen können und da waren noch deutlich größere Zahlen im Spiel (3091 über 270), wofür zum Beispiel die Fakultät von 3091 benötigt wird. Ich hatte hierzu ein Marko gefunden, welches die Ergebnisse für mich ermitteln konnte (letzter Post von hier: https://www.herber.de/forum/archiv/632to636/t634152.htm ). Daher hatte ich gehofft, dass es doch irgendwie möglich ist, mit den größeren Zahlen zu rechnen.
Und leider komme ich mit dem oben genannten Zahlenraum nicht aus. Ich muss in meinem Modell (sitze an meiner Masterarbeit) einige Wahrscheinlichkeiten berechnen und da kommen solche 'Ziehungen' von 270 Werten aus 3091 Möglichkeiten drin vor, was eben solche Werte produziert.
Grüße
Sebastian

Anzeige
AW: Probleme mit Zielwertsuche und großen Zahlen
02.11.2015 14:42:30
EtoPHG
Hallo Sebastian,
Alle Ansätze über den Zahlenraum von Excel hinauszugehen, beruhen auf Algorithmen, die mit Text (Ziffernblöcken) rechnen.
Grundsätzlich ist dein Problem in den Zwischen-Resultaten der Rechnung zu suchen. Denn sobald eines davon den Zahlenraum sprengt, ist Ende der Fahnenstange. Die Frage ist woher du deine grossen Zahlen hast, die übrigens als nur als Text vorliegen und warum sie du sie nicht auf die Exponential-Schreibweise kürzt. Bei F151 stösst du nicht wegen dem Zahlenraum an die Grenze, sondern, wegen der Länge des Textzahl (256 Stellen).
Gruess Hansueli

Anzeige
So in etwa
02.11.2015 20:37:22
Michael
Hi zusammen,
Hansuelis Bemerkung hat mich inspiriert, die Formel etwas zu zerpflücken.
Ich habe die "langen Zahlen" in Teilstrings bzw. Teilzahlen umgewandelt, damit ging es erst ein Stück weiter, und weiter unten, ab Zeile 193, habe ich nochmal die Zahl aufgeteilt und anders verformelt, so daß das Ding bis 400 rechnet und (wahrscheinlich wunschgemäß) so was Ähnliches wie 1 rauskommt.
Datei: https://www.herber.de/bbs/user/101192.xlsx
Schöne Grüße,
Michael

AW: So in etwa
02.11.2015 22:20:54
Sebastian
Hallo!
Danke euch beiden schon mal! Deine Lösung, Michael sieht schon mal richtig prima aus. Ich hätte gar nicht gedacht, dass es auch ohne Makro geht. Ich werde mir das morgen noch mal genauer anschauen und melde mich noch mal!
Grüße
Sebastian

Anzeige
So in etwa - Nachtrag
03.11.2015 15:45:59
Michael
Hi zusammen,
mir sind im Nachhinein noch ein paar Sachen durch den Kopf gegangen. Je nach dem, was Du für einen Background hast (in was für einem Fach machst Du den Master?) ist das eine oder andere für Dich nachvollziehbar:
a) Das Rechnen mit einer festen Anzahl von Nachkomma- bzw. signifikanten Stellen ist *immer* fehlerbehaftet. Damit schlagen sich Banker herum ...
https://support.microsoft.com/de-de/kb/196652
... und auch Physiker beschäftigen sich damit, Fehler bei Berechnungen zu beziffern.
D.h., Rundungsfehler sind nichts Außergewöhnliches, man muß sie "nur" der Vollständigkeit halber erwähnen.
b) Rechnen mit großen Zahlen geht schon:
http://www.excel-ticker.de/in-excel-mit-sehr-grossen-zahlen-rechnen-teil-1-addition/
c) Die Rechenoperationen lassen sich "logarithmisch" vereinfachen: dadurch werden Potenzen zu Multiplikationen, die mit b) ausführbar wären.
Zum Einstieg etwa: https://lp.uni-goettingen.de/get/text/4923
... wobei ich zugeben muß, daß ich die Anwendung auf Deine Aufgabe nicht ohne Nachdenken parat habe.
Das nur als Anregungen.
Happy Exceling,
Michael

Anzeige
AW: So in etwa - Nachtrag
03.11.2015 21:42:55
Sebastian
Hallo Michael,
danke noch für deine Hinweise. Den Link unter b) hatte ich selber schon gefunden, jedoch funktioniert das Add-In nur für ganze Zahlen. Das ist das Add-In, was ich schon in meiner Eingangsfrage erwähnt habe. Ich habe ja aber leider einige Dezimalzahlen mit Kommas mit in meiner Rechnung. Über Sinn und Unsinn meiner Genauigkeit lässt sich aber sicherlich diskutieren. Brauche ich wirklich die Nachkommstellen oder geht es auch ohne?
Jedenfalls: Vielen Dank noch mal für deine Lösung! Die funktioniert großartig! Tatsächlich habe ich dadurch auch noch mal einen Fehler in meiner Berechnung bemerkt. Die Werte, die ich durch deine Lösung errechnet habe, waren einfach unplausibel.
Noch kurz zur Info: Die Masterarbeit ist im Fach Finance & Accounting. Thema ist grob gesagt IFRS 9 (Ansatz und Bewertung von Finanzinstrumenten in zum großen Teil Bankbilanzen). Banker ist also gar nicht schlecht gewesen als Annahme... ^^
Ich setz mich dann mal wieder an die Arbeit! Vielen Dank für eure Hilfe!
Grüße
Sebastian

Anzeige
kleine Vervollständigung
04.11.2015 01:43:01
Michael
Hallo Sebastian,
freut mich, wenn Dir die Anregungen weitergeholfen haben.
Der Vollständigkeit halber und weil es mich interessiert hat, habe ich Deine Formel aus H5 mal zerpflückt. Das *wäre* insbesondere dann interessant, wenn man mit ganzen Zahlen (mit allen verfügbaren, signifikanten Stellen) rechnen wollte.
Es geht darum, einfach die 10er-Potenzen der Werte "getrennt" zu behandeln, die lassen sich dann im Nachhinein einfach zusammengefaßt berechnen.
Quick 'n' dirty-Skizze anbei (das 10^0 steht natürlich für 1, wobei sich die 0 zeilenweise runter dann schnell bis knapp 400 ändert):
Userbild
Also, viel Erfolg!
Schöne Grüße,
Michael
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige