Warum Excel 0,1 + 0,2 nicht exakt rechnet – und wann das zählt

Sie ziehen in einer Abrechnung zwei Werte voneinander ab, die beide 1.004,80 Euro anzeigen, und als Differenz erscheint statt 0 ein Wert wie 1,13687E-13. Oder ein WENN-Vergleich liefert FALSCH, obwohl beide Zellen identisch aussehen. Das ist kein Defekt Ihrer Datei und auch kein Excel-spezifischer Fehler. Es ist eine Folge davon, wie Computer Dezimalzahlen speichern.

Dieser Artikel erklärt den Mechanismus so weit, wie er für die Praxis nötig ist, zeigt, in welchen Situationen er tatsächlich Ergebnisse verfälscht, und welche Gegenmittel Excel dafür bietet.

Warum 0,1 im Computer nicht exakt 0,1 ist

Excel speichert Zahlen nach dem Standard IEEE 754 im Format mit doppelter Genauigkeit. Laut Microsoft besteht eine solche Zahl aus einem Vorzeichenbit, einem Exponenten mit 11 Bit und einer Mantisse mit 52 Bit (plus einem impliziten Bit). Gespeichert wird also eine Binärzahl, keine Dezimalzahl.

Das Problem: Viele Dezimalbrüche, die uns rund vorkommen, sind im Binärsystem periodisch. Microsoft vergleicht das mit 1/3, das im Dezimalsystem als 0,3333… nie endet. Für 0,1 gilt im Binärsystem dasselbe. Da die Mantisse begrenzt ist, wird die Zahl nach 53 signifikanten Bits abgeschnitten beziehungsweise gerundet. Was tatsächlich im Speicher steht, sieht so aus (berechnet in IEEE-754-Doppelgenauigkeit):

0,1 und 0,2 werden jeweils minimal zu groß gespeichert, 0,3 minimal zu klein. Die Summe aus 0,1 und 0,2 landet deshalb nicht auf derselben Binärzahl wie die Eingabe 0,3, sondern auf der nächsthöheren darstellbaren Zahl. Der Abstand beträgt etwa 5,55E-17, also 0,0000000000000000555.

Was Excel anzeigt – und was es speichert

In der Zelle sehen Sie davon meist nichts. Laut den Microsoft-Spezifikationen beträgt die Genauigkeit von Zahlen in Excel 15 Stellen; entsprechend zeigt Excel Werte mit höchstens 15 signifikanten Ziffern an. Die Abweichung bei 0,1 + 0,2 liegt jenseits dieser Grenze, die Zelle zeigt 0,3.

Sichtbar wird der Effekt, sobald das Ergebnis klein ist oder Sie viele Nachkommastellen einblenden. Microsoft nennt dafür das Beispiel =(43,1-43,2)+1: Formatiert man die Zelle wissenschaftlich mit 15 Dezimalstellen, steht dort nicht 0,9, sondern ein Wert knapp darunter. Grund ist, dass zuerst 43,1 − 43,2 berechnet wird; dabei entsteht ein −0,1 mit dem typischen Darstellungsfehler, der sich ins Endergebnis überträgt. In Doppelgenauigkeit ergibt die Rechnung 0,8999999999999986.

Excel versucht, den häufigsten sichtbaren Fall abzufangen. Nach Microsoft-Dokumentation gleicht Excel seit Version 97 Fehler aus, wenn eine Addition oder Subtraktion ein Ergebnis bei oder sehr nahe null liefert. Das dokumentierte Beispiel =1,333+1,225-1,333-1,225 ergibt daher 0, während Excel 95 noch −2,22044604925031E-16 anzeigte. Diese Korrektur greift aber nur in bestimmten Konstellationen. Darauf, dass ein Ergebnis „schon glattgezogen wird", sollten Sie sich nach unserer Einschätzung nicht verlassen.

Sonderfall: große und lange Zahlen

Die 15-Stellen-Grenze wirkt auch umgekehrt. Microsoft beschreibt, dass bei Zahlen mit 16 oder mehr Ziffern alle Stellen nach der 15. auf null gesetzt werden. Eine 16-stellige Karten- oder Artikelnummer wie 1234567890123456 wird dadurch zu 1234567890123450. Solche Werte sind keine Rechengrößen, sondern Kennungen, und gehören als Text in die Zelle: Formatieren Sie die Spalte vor der Eingabe als Text oder setzen Sie ein Apostroph voran. Beim Import aus CSV-Dateien legen Sie den Datentyp der Spalte entsprechend fest, siehe CSV-Dateien importieren ohne Zahlensalat.

Ähnlich verhält es sich bei sehr unterschiedlichen Größenordnungen. Laut Microsoft-Beispiel ergibt =1,2E+200+1E+100 wieder exakt 1,2E+200, weil der zweite Summand weit hinter der 15. Stelle liegt. Und =0,000123456789012345+1 liefert 1,00012345678901 – die letzten Ziffern des kleinen Werts gehen verloren.

Wann der Fehler harmlos ist und wann nicht

Ein relativer Fehler in der Größenordnung von 1E-16 (einem Zehnbilliardstel) ist für fast jede kaufmännische oder statistische Auswertung bedeutungslos. Ob ein Umsatz 1.004,80 oder 1.004,8000000000001 Euro beträgt, ändert keine Entscheidung. Kritisch wird es dort, wo Excel nicht mit dem Wert weiterrechnet, sondern eine Ja-oder-Nein-Entscheidung trifft.

Beispielrechnung: Kassenabgleich mit Restbetrag

Beispieldatei: Kassenbuch eines fiktiven Kleingartenvereins. Spalte B enthält Ein- und Auszahlungen, am Ende soll der Bestand gegen die Bargeldzählung abgeglichen werden.

Der rechnerische Bestand beträgt 1.004,80 Euro, die Zählung ergibt ebenfalls 1.004,80 Euro (in Zelle E2). Addiert man die sechs Beträge in IEEE-754-Doppelgenauigkeit von oben nach unten, entsteht jedoch 1.004,8000000000001 – der gespeicherte Wert liegt etwa 1,14E-13 über dem Wert, den die Eingabe 1.004,80 erzeugt. Beide Zellen zeigen 1.004,80 an. Eine Prüfformel, die auf exakte Gleichheit setzt, kann trotzdem Alarm schlagen:

=WENN(SUMME(B2:B7)=E2;"Kasse stimmt";"Differenz prüfen")

Ob Excel in genau diesem Fall „Kasse stimmt" oder „Differenz prüfen" ausgibt, hängt von der Reihenfolge der Berechnung und davon ab, ob die oben beschriebene Korrektur nahe null greift. Gerade diese Unvorhersehbarkeit ist das Problem: Eine Prüfformel, die mal funktioniert und mal nicht, ist schlechter als keine. Die Lösung besteht darin, den Vergleich auf die Genauigkeit zu bringen, die fachlich gemeint ist – hier Cent:

=WENN(RUNDEN(SUMME(B2:B7);2)=RUNDEN(E2;2);"Kasse stimmt";"Differenz prüfen")

Gegenmittel in der Praxis

RUNDEN gezielt einsetzen

RUNDEN (englisch ROUND) mit der Syntax RUNDEN(Zahl;Anzahl_Stellen) ist das wichtigste Werkzeug. Microsoft empfiehlt die Funktion ausdrücklich, um Genauigkeitsfehler auszugleichen. Runden Sie dort, wo ein Wert fachlich eine feste Genauigkeit hat: Geldbeträge nach jeder Preis- oder Steuerberechnung auf 2 Stellen, Mengen auf die sinnvolle Einheit.

Wichtig ist der Unterschied zwischen Formatierung und Rundung. Das Zahlenformat „2 Dezimalstellen" ändert nur die Anzeige, gerechnet wird mit dem vollen Wert. Erst RUNDEN ändert den Wert selbst. Interessant ist dabei ein Detail aus der Microsoft-Dokumentation: =RUNDEN(2,15;1) ergibt 2,2, obwohl 2,15 binär als 2,149999999999999911… gespeichert wird. Excel rundet also so, wie man es bei der dezimalen Eingabe erwartet. Nicht jede Programmiersprache verhält sich so; Pythons eingebaute Funktion round(2.15, 1) etwa liefert 2.1.

Runden Sie nicht vorsorglich jede Zwischenrechnung. Wer bei Prozentwerten oder Durchschnitten früh rundet, erzeugt größere Fehler, als die Gleitkommadarstellung je verursachen würde. Sinnvoll ist das Runden an fachlich definierten Stellen: bei Beträgen, die so auf einer Rechnung stehen, und unmittelbar vor Vergleichen.

Vergleichen mit Toleranz

Wenn sich keine feste Stellenzahl anbietet, etwa bei Messwerten, vergleichen Sie mit einer Toleranz. Die Funktion ABS liefert den Betrag der Differenz:

=WENN(ABS(A2-B2)<0,000001;"gleich";"verschieden")

Die Toleranz sollte zur Größenordnung der Daten passen. Für Euro-Beträge ist 0,005 (ein halber Cent) eine naheliegende Grenze, für Werte im Millionenbereich darf sie größer sein. Für die bedingte Formatierung gilt dasselbe: Verwenden Sie statt „Zellwert gleich 0" eine Formelregel wie =ABS($D2)<0,005.

Ganzzahlig rechnen, wo es möglich ist

Ganze Zahlen bis zu einer Größe von 15 Stellen werden exakt gespeichert. Wer Beträge in Cent statt in Euro erfasst (8990 statt 89,90), umgeht das Problem bei Additionen und Subtraktionen vollständig. Für Vereinskassen oder Warenwirtschaftsexporte, die ohnehin in Cent liefern, ist das eine robuste Lösung. Für die Anzeige teilen Sie erst am Ende durch 100.

Suchen nach berechneten Werten

Wenn ein Suchwert per Formel entsteht, etwa =B2*1,1 als Preisstufe, runden Sie ihn vor der Suche auf die Stellen, mit denen die Nachschlagetabelle gepflegt ist: =XVERWEIS(RUNDEN(B2*1,1;2);Preise[Stufe];Preise[Rabatt]). Mit SVERWEIS funktioniert das Runden des Suchwerts genauso. Unterschiede zwischen beiden Funktionen erklärt der Artikel XVERWEIS statt SVERWEIS.

Die Option „Genauigkeit wie angezeigt festlegen"

Excel bietet eine Arbeitsmappenoption, die alle Werte auf die angezeigte Genauigkeit kürzt. Sie finden sie unter Datei > Optionen > Erweitert im Abschnitt „Beim Berechnen dieser Arbeitsmappe". Ist bei einer Zelle das Format mit zwei Dezimalstellen gewählt, speichert Excel danach auch nur noch zwei Dezimalstellen.

Checkliste für die nächste Datei

• Vergleiche auf Gleichheit mit berechneten Werten mit RUNDEN oder einer Toleranz absichern.
• Geldbeträge nach Multiplikationen und Divisionen auf 2 Stellen runden, nicht nur so formatieren.
• Saldo- und Abgleichprüfungen auf die fachlich gemeinte Genauigkeit runden (bei Euro: Cent).
• Kennungen mit mehr als 15 Ziffern als Text erfassen und importieren.
• Bei unerwarteten Werten wie 1,13687E-13 nicht die Daten suchen, sondern die Formel runden.
• „Genauigkeit wie angezeigt festlegen" nur mit Sicherungskopie und bewusst einsetzen.

Wie sich Rundung auf Prozentangaben auswirkt, zeigt der Beitrag Prozent, Prozentpunkte, Veränderungsraten. Wenn Eingaben schon beim Erfassen auf zwei Dezimalstellen begrenzt werden sollen, hilft die Datenüberprüfung.