none
Excel 2016: Binäres Rechensystem bei Ganzzahl-Funktion RRS feed

  • Allgemeine Diskussion

  • Guten Morgen zusammen!

    Ich wollte mal eine etwas ärgerliche Erfahrung von heute Morgen mit euch teilen. :) Es geht um die GANZZAHL Funktion in Kombination mit einer WENN Funktion:

    =(Wenn(B2-Ganzzahl(B2)<0,99;Ganzzahl(B2)+0,99;B2+1))

    Das klappt in meinem Beispiel bei den Werten:

    8,98

    8,99

    9,98

    9,99

    ...

    15,98

    15,99

    16,98

    Ab dem Wert 16,99 (=B19) wird für die Formel

    =B19-Ganzzahl(B19)

    der Wert 0,989999999999998 anstatt dem richtigen Ergebnis 0,99 ausgegeben. Entsprechend funktioniert auch die Formel

    =(Wenn(B19-Ganzzahl(B19)<0,99;Ganzzahl(B19)+0,99;B19+1))

    nicht. Das erwartete Ergebnis wäre bei einem Wert von B19 = 16,99 in dem Fall 17,99. Ausgegeben wird allerdings der falsche Wert 16,99.

    Ich nehme mal an, dass es sich um eine klassische Folge des binären Rechensystems handelt. Gleitkommazahlen können schließlich nur annähernd präzise berechnet werden.

    Einen eleganten Workaround muss ich mir noch überlegen, aber es wird vermutlich darauf rauslaufen, in mehreren Spalten zu arbeiten und die Berechnung auf "Genauigkeit wie angezeigt" umzustellen. So richtig zufriedenstellend ist das aber nicht.

    Wie geht ihr mit solchen Situationen um? Hat eventuell jemand einen schöneren Workaround parat?

    Viele Grüße

    Fabian


    • Bearbeitet FabianTHAT Mittwoch, 3. Januar 2018 09:35
    Mittwoch, 3. Januar 2018 09:32

Alle Antworten

  • Hmmm. Hier funktioniert's:

    Vielleicht sind Deine Ausgangsdaten nicht "zwei Stellen-rein"?


    Evgenij Smirnov

    I work @ msg services ag, Berlin -> http://www.msg-services.de
    I blog (in German) @ http://it-pro-berlin.de
    my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
    Exchange User Group, Berlin -> http://exusg.de
    Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
    Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

    Mittwoch, 3. Januar 2018 10:31
  • Hi,

    Danke erstmal für dein Interesse am Thema. :) Leider sind es wirklich exakt zwei Nachkommastellen.

    Interessanterweise wird die "Unschärfe" im Ergebnis erst angezeigt, wenn ich genug Nachkommastellen einblende. Ab 15 Nachkommastellen wird das Ergebnis dann tatsächlich so dargestellt wie es ist: falsch.

    Das ist ja an sich schon irritierend, weil man den Fehler nicht sofort sehen kann. Viel spannender ist jedoch, dass der Fehler sich durch "Optionen" > "Erweitert" > "Genauigkeit wie angezeigt festlegen" umgehen lässt.

    Das heißt: Wenn ich unpräzisere Ergebnisse akzeptiere, bekomme ich präzisere Ergebnisse. :D Interessant ist das Thema in jedem Fall. Auch wenn ich noch keinen Plan hab, wie ich den Fehler eleganter umschiffen kann. >_<

    Ich hoffe, dass Microsoft bald mit der Prüfung meines Kontos durch ist, dann kann ich Screenshots nachliefern.

    Viele Grüße

    Fabian

    Edit: Ein Wort im vorletzten Absatz vergessen.
    • Bearbeitet FabianTHAT Mittwoch, 3. Januar 2018 10:52
    Mittwoch, 3. Januar 2018 10:50
  • Das ist ein generelles Problem nicht nur bei Excel sondern in allen Programmiersprachen bei der Berechnung mit sog. Double-Werten.
    Double's werden in der internen Darstellung mit (0,0 - 0,5) * 2^n gespeichert, also eigentlich als Binärwert.
    Bei den Vorkommastellen ist das noch ok, bei den Nachkommastellen wird hier ein Näherungswert aus 1/2^x berechnet, der dann natürlich mit dem Dezimalwert nicht identisch sein muss.

    Nachzulesen hier:
    https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

    Helfen kann u.U. die Funkltion "Runden(Wert;2)", was bei kaufmännischen Berechnungen auch durchaus in Zwischenergebnissen benötigt wird.

    Mittwoch, 3. Januar 2018 11:12
  • Moin,

    ja, stimmt. Allerdings liegt der Fehler nicht in der GANZZAHL()-Funktion, sondern in der Subtraktion. Guck mal:

    Der Fall ist auch hinreichend bekannt und hat weitere interessante Ausprägungen: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel#Subtraction_of_Subtraction_Results


    Evgenij Smirnov

    I work @ msg services ag, Berlin -> http://www.msg-services.de
    I blog (in German) @ http://it-pro-berlin.de
    my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
    Exchange User Group, Berlin -> http://exusg.de
    Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
    Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

    Mittwoch, 3. Januar 2018 11:17
  • Hallo nochmal!

    Danke für die Quellen Herrschaften, war eine interessante Lektüre für die Mittagspause. :) Ich habe mein Problem nun folgendermaßen gelöst:

    =(WENN(RUNDEN(B2-GANZZAHL(B2);2)<0,99;GANZZAHL(B2)+0,99;B2+1))

    Danke für den Hinweis auf die Runden-Funktion, bfuerchau - so einfach und elegant kann es sein! Hätte ich ehrlich gesagt gar nicht dran gedacht.

    Funktioniert nun tadellos die Formel. :)

    Viele Grüße und danke an euch beide!

    Fabian

    Mittwoch, 3. Januar 2018 13:26
  • > Viel spannender ist jedoch, dass der Fehler sich durch "Optionen" > "Erweitert" > "Genauigkeit wie angezeigt festlegen" umgehen lässt.

    Kommt dann immer noch die legendär bescheiden übersetzte Frage "Die Daten verlieren damit endgültig an Genauigkeit....."?

    > Das heißt: Wenn ich unpräzisere Ergebnisse akzeptiere, bekomme ich präzisere Ergebnisse.

    Aber eben nur für Dich als Menschen. Eigentlich waren sie vorher eher "überpräzise" und durch die Einstellung hast Du sie auf die gewünschte, weil sichtbare, Präzision wieder runtergestuft.

    Und ganz nebenbei hast Du damit erfahren, wofür diese lustige Option ist. :)

    Gruesse aus Berlin schickt Robert - MVP Office Servers and Services (Exchange Server)

    Mittwoch, 3. Januar 2018 14:21
  • Diese Einstellung gilt jedoch nur für das Endergebnis in der Zelle nicht für berechnete Zwischenergebnisse. Hier muss u.U. eben die Runden-Funktion verwendet werden (ins besonders wenn ich mit Prozenten rechne).

    Ich hatte da mal einen interessanten Fall in einer anderen Anwendung:
    Der Hersteller der Software hat den Vorkomma- und Nachkommawert getrennt und nur die Nachkommata gerundet. Dies führte zu einer Abrundung obwohl auf den Gesamtwert eine Aufrundung hätte stattfinden müssen.

    Wenn man nun bedenkt, dass bei den Ziffern 1-9 nur 4 Mal abgerundet aber 5 Mal aufgerundet wird (0 wird ja nicht gerundet) ergibt sich da durchaus eine große Differenz bei vielen kleinen Berechnungen.
    Mittwoch, 3. Januar 2018 14:40