Rundungsfehler bei VBA aufgrund von Formeln möglich?

standi · 19. Februar 2018

Hallo zusammen,

ich habe in einer Zelle eine Zahl drinstehen: sheet.Cells(44, 8).Value

In den Zellen sheet.Cells(44, 9).Value und sheet.Cells(44, 10).Value) wird per Formel der Netto-Betrag (9) und die Mwst (10) berechnet.

Beispiel:
-5,88 Spalte 5
-4,94 (Enthält die Formel =RUNDEN((H44/1,19); 2) für Netto
-0,94 (Enthält die Formel =RUNDEN((H44-I44);2)) für MwSt

In VBA mache ich nun folgendes:

Code:

If (sheet.Cells(44, 8).Value = (sheet.Cells(44, 9).Value + sheet.Cells(44, 10).Value)) Then
          resultText = "iO"
Else

Manchmal kommt er in die If-Schleife und manchmal nicht, obwohl -4,94+(-0,94) = -5,88 entspricht. Gibt es hier evtl. Rundungen? Per Debugging sehe ich aber keine Rundung, siehe Anhang

halwe · 19. Februar 2018

Das Problem dürfte dann entstehen, wenn H44 selbst kein glatter Wert ist.

CHaos.Gentle · 19. Februar 2018

Moin,

ich versuche mal meinen sonst so überschwenglichen Sarkasmus ein wenig im Zaun zu halten...

Also, in H44 steht eine Zahl, diese multiplizierst, rundest und speicherst du.
Dann subtrahierst von H44 diesen gerundeten Wert und rundest ihn nochmal. (Was Unsinn ist, da ja eine Zahl mit maximal zwei Dezimalstellen, subtrahiert von einer anderen Zahl mit maximal zwei Dezimalstellen, niemals eine Zahl mit mehr als zwei Dezimalstellen werden kann)

Und am Ende addierst du beide gerundeten Werte wunderst dich über Rundungsfehler - ernsthaft jetzt?

Abgesehen davon, dass es für mich überhaupt keinen Sinn ergibt, diesen "iO"-Text auszugeben, was sollte denn an der Berechnung nicht iO sein? Hast du Angst vor einem CPU-Bug. Ach, ich wollte ja ohne Sarkasmus.

Entweder, du arbeitest mit gerundeten Werten, dann darfst du eben am Ende kein "=" benutzen oder du arbeitest mit exakten Werten, dann kannst du dein "=" nutzen.

Kurz gesagt, es gibt unendlich viele Zahlen, bei denen es völlig richtig ist, dass deine if-Anweisung ins else läuft.

Andreas_ · 19. Februar 2018

Es ist kein Rundungsfehler. Es ist nur das übliche Problem beim Umgang mit Gleitkommazahlen. Auch für VBA gilt, dass man diese nicht direkt vergleichen soll ...

Deswegen sollte folgendes funktionieren:

Code:

If (Round(sheet.Cells(44, 8).Value,2) = Round(sheet.Cells(44, 9).Value + sheet.Cells(44, 10).Value,2)) Then
          resultText = "iO"
Else

standi · 19. Februar 2018

CHaos.Gentle schrieb:
Also, in H44 steht eine Zahl, diese multiplizierst, rundest und speicherst du.

Sollte ja soweit OK sein

CHaos.Gentle schrieb:
Dann subtrahierst von H44 diesen gerundeten Wert und rundest ihn nochmal. (Was Unsinn ist, da ja eine Zahl mit maximal zwei Dezimalstellen, subtrahiert von einer anderen Zahl mit maximal zwei Dezimalstellen, niemals eine Zahl mit mehr als zwei Dezimalstellen werden kann)

Sollte ja nicht stören

CHaos.Gentle schrieb:
Und am Ende addierst du beide gerundeten Werte wunderst dich über Rundungsfehler - ernsthaft jetzt?

Es sollten ja trotzdem keine Rundungsfehler herauskommen, da ja der erste errechnete Wert gerundet ist und der zweite Wert subtrahiert. Die

CHaos.Gentle schrieb:
Abgesehen davon, dass es für mich überhaupt keinen Sinn ergibt, diesen "iO"-Text auszugeben, was sollte denn an der Berechnung nicht iO sein? Hast du Angst vor einem CPU-Bug. Ach, ich wollte ja ohne Sarkasmus.

Es sind nicht alle Zeilen mit Formeln hinterlegt, die ich ihn VBA auswerte. Ebenso könnte ja die Formel an manchen Stellen falsch kopiert worden sein. Daher prüft es das VBA-Skript einfach nochmal gegen, ob die Summe passt.

CHaos.Gentle schrieb:
Entweder, du arbeitest mit gerundeten Werten, dann darfst du eben am Ende kein "=" benutzen oder du arbeitest mit exakten Werten, dann kannst du dein "=" nutzen.

Mich hat es gewundert, da ich ja beim Debuggen eine gerundete Zahl erhalte, in diesem Fall "-4,94", weswegen ich mich schon frage, warum man das nicht mit "=" vergleichen kann. Für das VBA-Skript ist es ja eine Zahl mit zwei Nachkommestellen, die er aus der Zelle abgreift. Oder greift es die Formel und das Ergebnis daraus ab? Dann würde es mir aber ja nicht die "-4,94" anzeigen.

@Andreas
Danke. Oben wird auf 2 Nochkommestellen gerundet. Soweit funktioniert es nun. Technisch stelle ich mir aber trotzdem die Frage, warum beim Vergleich zwei zwei dezimalstellen in VBA zusätzlich gerundet werden muss.

Andreas_ · 19. Februar 2018

standi schrieb:
Für das VBA-Skript ist es ja eine Zahl mit zwei Nachkommestellen, die er aus der Zelle abgreift.

Ist es nicht. Das ist eine Gleitkommazahl. Das angezeigte -4.94 kann genauso gut -4.939999999999999997 sein ...

Man könnte mit "Precision as displayed" erzwingen, dass angezeigter Wert und tatsächlicher Wert übereinstimmen. Aber von der Verwendung rät Microsoft ab.

Auf der verlinkten Seite ist ein schönes Beispiel:

Code:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Normalerweise würde man annehmen, dass 1 am Ende ausgegeben würde. Statt dessen ist das Ergebnis 0.999999999999996

standi · 19. Februar 2018

Danke. Hätte gedacht, dass in meinem screenshoot dann eine Zahl hätte erscheinen müssen, mit mehr Nachkommastellen. Danke aber soweit, nun passt das Skript

cumulonimbus8 · 19. Februar 2018

Noch mal ein Tröpfchen Sarkasmus.

Du begehst den selben Fehler wie so viele. Zwischen Rechnen und Darstellen besteht ein himmelweiter Unterschied!

Rohwerte kommen ›in den Keller‹ und werden mit maximaler Rechengenauigkeit - nun, berechnet eben.
Ergebnisse stellt man in formatierten Zellen dar - es ist reine Anzeige, Darstellung. Wenn dort Rundungsfehler auftreten ist das normal.

CN8

Andreas_ · 19. Februar 2018

Noch einmal - es ist kein Rundungsfehler. Das Problem liegt allein im verwendeten Datentyp. Vielleicht sollte sich der eine oder andere mal damit beschäftigen. Der Wikipedia-Artikel zu Gleitkommazahlen erklärt, warum der Vergleich von Gleitkommazahlen nicht zuverlässig funktionieren kann oder auch warum Dezimalbrüche regelmäßig nur näherungsweise abgebildet werden können.

halwe · 20. Februar 2018

Andreas_ schrieb:
Noch einmal - es ist kein Rundungsfehler. Das Problem liegt allein im verwendeten Datentyp. Vielleicht sollte sich der eine oder andere mal damit beschäftigen. Der Wikipedia-Artikel zu Gleitkommazahlen erklärt, warum der Vergleich von Gleitkommazahlen nicht zuverlässig funktionieren kann oder auch warum Dezimalbrüche regelmäßig nur näherungsweise abgebildet werden können.

Richtig, Gleitkommaberechnung hat ihre Grenzen, und die treten dort auf, wo die in der Berechnung hinterlegte "interne" Rundung auf Grund vieler Einzeloperationen nicht mehr greift.

Aber was das obige Beispiel abgeht, hatte ich eigentlich bereits im zweiten Beitrag alles gesagt:

halwe schrieb:
Das Problem dürfte dann entstehen, wenn H44 selbst kein glatter Wert ist.

Genauso ist es und wenn man die Formel:
=H44-I44-J44 irgendwo einfügt, sieht man das auch...

Andreas_ · 20. Februar 2018

Anscheinend hast Du die Problematik nicht verstanden. Das hat mit "Rundung" nichts zu tun. Alle Dezimalbrüche, deren gekürzter Nenner keine Zweierpotenz ist, können als Gleitkommazahl nur näherungsweise dargestellt werden. Ich weiß also, dass 0,1 als Gleitkommazahl immer ungleich 0,1 ist.

Selbst wenn H44 ein "glatter" Wert sein sollte, wenn das Ergebnis von H44/1,19 nicht auch ein "glatter" Wert ist, habe ich das Problem mit den Gleitkommazahlen trotzdem, da in I44 und J44 trotz Rundung Gleitkommazahlen stehen.

Es ist also völliger Unsinn wenn man

halwe schrieb:
Das Problem dürfte dann entstehen, wenn H44 selbst kein glatter Wert ist.

schreibt. Wie ich schon anmerkte - einfach mal mit der Problematik beschäftigen ...

halwe · 20. Februar 2018

@Andreas: Also ich beziehe mich auf das Tabellenblatt und die hinterlegte Überprüfung. Und die gibt "iO" solange der Anfangsbetrag eine glatte Cent-Zahl ist, weil dann die Rundung der nachfolgenden Zahlen keine Differenzen im <1ct Bereich aufwirft.

Das Thema, welches du ansprichst ist durchaus interessant. Aber es ist hinreichend bekannt, so dass die Berechnungsalgorithmen in Excel (und auch bei jedem besseren Taschenrechner) das berücksichtigen, indem sie eine feingliedrige Binäroperation auf eine grobgliedrigere Dezimalanzeige umrechnen. Deshalb ist eben 1/3 in Excel 0,33333... usw. aber wenn du das wieder mit 3 multiplizierst, kommt 1 raus und nicht 0,9999... Auch nicht mit der geringsten Abweichung, obwohl 1/3 eindeutig nicht binär exakt ist. Die Algorithmen wissen eben, dass 0,999999999 eher 1 ist, solange sich die Differenz zu 1 im Binärrundungsbereich hält.

Andreas_ · 20. Februar 2018

Wenn es mit einer endlichen Anzahl von Versuchen klappt, dann bedeutet das nicht, dass es immer klappt ...

Excel arbeitet bei Gleitkommazahlen mit einer Genauigkeit von 15 Stellen, wenn die letzten Stellen eine Rolle spielen, dann merkt man die Abweichungen deutlicher. Ein Beispiel:
1.3240 – 1.3190 = 0.0049999999999999

Hier noch ein Artikel aus einem Microsoft-Blog, der das Problem im Zusammenhang mit Excel detailliert erklärt.
Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”

Suche

Rundungsfehler bei VBA aufgrund von Formeln möglich?

standi

Lt. Junior Grade

halwe

Lt. Commander

CHaos.Gentle

Lt. Commander

Andreas_

Commander

standi

Lt. Junior Grade

Andreas_

Commander

standi

Lt. Junior Grade

cumulonimbus8

Fleet Admiral

Andreas_

Commander

halwe

Lt. Commander

Andreas_

Commander

halwe

Lt. Commander

Andreas_

Commander