auf Excel-Formeln sollte man sich nicht 'blind' verlassen / kennt jemand noch weitere Fallen ?

[#1]

Hallo Excel-Freaks,

es liegt vermutlich in der DNA von Steuer-Fachleuten, dass sie gerne mit Zahlen 'spielen'.

.. und Excel ist hervorragend für Zahlenspielereien geeignet.

Im Lauf der Jahre sind mir immer wieder mal Fehler in Excel-Formeln begegnet (z.B. beim Barwert u.a. finanzmathematischen Funktionen).

Heute bin ich über einen Effekt gestolpert, der sich in entsprechenden Auswertungen auch auswirken könnte, ob negativ oder positiv, liegt in der Perspektive des 'Betrachters' 😀. 

Jedenfalls führt dieser Effekt zu einem falschen Ergebnis.

Die Formel für die Dauer (Zelle C2) lasse ich hier lieber weg 😀

[#2]

Ja, die Excel Formeln, immer ein Quell der Freude.

In diesem Fall macht die Formel, was sie soll. Die Funktion soll den Wert auf den nächsten Schritt genau um 1 aufrunden. Bei 9,0 ist der nächste Schritt 10,0. D. h., die Funktion rundet alles ab 9,0 auf 10,0 auf. Besser wäre die Funktion "Aufrunden" und die Zeiten in Industrieminuten umzurechnen.

[#3]

... dieser Effekt tritt nur relativ selten auf und nur bei vollen Minuten.

Wenn man eine andere Uhrzeit einsetzt, gibt es keine Probleme.

Es liegt wohl daran, dass hier der eigentliche Zahlenwert an der 7. 8. 9. ... Stelle hinter dem Komma minimal größer als 9:00 Minuten ist und daher auf 10:00 Minuten aufgerundet wird.

9 Minuten entsprechen (bei dieser Differenz) der Zahl 0,00625000000582077

Bei der Zahl 0,00625000000000000 würde die Excel-Formel nicht auf 10:00 Minuten aufrunden.

Intern rechnet Excel immer mit Zahlenwerten.

Das Format "Datum" und "Uhrzeit" ist nur eine andere Darstellung einer Dezimalzahl.

... aber wem erzähle ich das ?

Das ist natürlich jedem schon bekannt.

[#4]

Nachtrag:

... wenn ich die berechnete Differenz von 9:00 Minuten (aus der Tabelle) mit den Faktoren 24 und 60 multipliziere, erhalte ich als Differenz den Wert von 9,0000000083819 Minuten anstatt exakt 9 Minuten.

Insofern haben Sie zwar nocheinmal Recht @einmalnoch , dass die Formel das tut, was sie soll, nämlich aufzurunden, aber bei einem exakten Wert von 9 Minuten würde sie nicht auf 10 Minuten aufrunden !

Also müsste man vorher den 'Rattenschwanz' abschneiden, dann ist alles gut.

Die Umrechnung in Industrieminuten und zurück würde das Ganze nur noch verkomplizieren.

... aber wer rechnet schon mit solchen Schwierigkeiten, wenn man Excel-Formeln anwendet.

Bei finanzmathematischen Berechnungen habe ich mir vor Jahren schon angewöhnt, Excel-Formeln erst durch eigene Formeln oder durch händische Berechnung zu verproben, bevor ich sie 'auf breiter Front' einsetze.

[#5]

Weitere Falle: Man kann einstellen, dass Formeln erst beim schließen des Blatts / manuell aktualisiert werden 😂. Ändert man also was in der Tabelle, rechnet Excel einfach nicht und 1+1 = 3 👍. Sinn und Zweck erschließt sich mir nicht aber gehen tut's.

Excel: Automatische Berechnung funktioniert nicht

[#6]

Hallo,

Manuelle Berechnung...Ändert man also was in der Tabelle, rechnet Excel einfach nicht...Sinn und Zweck erschließt sich mir nicht

und genau das ist der Grund - Excel soll bei der Eingabe nicht rechnen.

Ich habe schon Tabellenblätter gesehen die waren gespickt mit Matrixformeln und volatilen Funktionen und als Krönung noch über die gesamte Spalte gezogen.

Da hattest du dann nur die Möglichkeit die Berechnung auf manuell zu stellen da XL ansonsten nach jeder Eingabe angefangen hat die Formeln neu zu berechnen und du bei der Eingabe entsprechende Kaffeepausen einlegen konntest.

[#7]

teile einen Euro Gewinn, Kapital, oder sonst etwas auf drei Gesellschafter auf...
-> 3 * 33 Cent
=> Summe Excel 1,00 EUR

Differenz 1 Cent, der dir überall um die Ohren fliegt 

[#8]

---

@vogtsburger  schrieb:

Nachtrag:

... wenn ich die berechnete Differenz von 9:00 Minuten (aus der Tabelle) mit den Faktoren 24 und 60 multipliziere, erhalte ich als Differenz den Wert von 9,0000000083819 Minuten anstatt exakt 9 Minuten.

Insofern haben Sie zwar nocheinmal Recht @einmalnoch , dass die Formel das tut, was sie soll, nämlich aufzurunden, aber bei einem exakten Wert von 9 Minuten würde sie nicht auf 10 Minuten aufrunden !

Also müsste man vorher den 'Rattenschwanz' abschneiden, dann ist alles gut.

Die Umrechnung in Industrieminuten und zurück würde das Ganze nur noch verkomplizieren.

... aber wer rechnet schon mit solchen Schwierigkeiten, wenn man Excel-Formeln anwendet.

Bei finanzmathematischen Berechnungen habe ich mir vor Jahren schon angewöhnt, Excel-Formeln erst durch eigene Formeln oder durch händische Berechnung zu verproben, bevor ich sie 'auf breiter Front' einsetze.

---

Das ist kein spezielles Excel-Problem, sondern ein generelles Umrechnungsproblem. Da immer ausgehend von einem Tag (=1) die Zeiten hiervon abgeleitetet werden, kann/muss es immer zu Abweichungen kommen. Das muss man dann selbst in der Formel berücksichtigen, in der Weise, dass an den richtigen Stellen gerundet bzw. mit ganzen Zahlen gerechnet werden. Die Minute wird eben halt wie folgt defniert:

(Quelle Wikipedia)

Und das Ergebnis kann also niemals ganze Zahlen sein.

Also Obacht bei Zeitberechnungen.

Gruß Achilleus

[#9]

---

@Gelöschter Nutzer  schrieb:

.

...

Das ist kein spezielles Excel-Problem, sondern ein generelles Umrechnungsproblem. Da immer ausgehend von einem Tag (=1) die Zeiten hiervon abgeleitetet werden, kann/muss es immer zu Abweichungen kommen. Das muss man dann selbst in der Formel berücksichtigen, in der Weise, dass an den richtigen Stellen gerundet bzw. mit ganzen Zahlen gerechnet werden. Die Minute wird eben halt wie folgt defniert:

---

.... für mich ist das schon ein spezielles Excel-Problem, oder sagen wir besser: ein Problem von Tabellenkalkulationsprogrammen

Das Umrechnen von Datum und Uhrzeit in eine 'krumme' Dezimalzahl ist kein Naturgesetz. Man könnte ja auch solche Werte, wie in meinem o.g. Beispiel, exakt berechnen, indem man die Sekunden, Minuten, Stunden und Tage exakt so addiert und subtrahiert, wie sie angezeigt werden. 

Warum entspricht eine angezeigte Differenz von 09:00 Minuten nicht exakt dem Wert von 9 Minuten.

Wenn man Excel kennt, kann man natürlich alles wieder mit Hilfe von Excel-Formeln "heilen", aber so trivial ist vieles nicht, was mit Excel gern 'zusammengeschustert' wird, insbesondere die Rundungsprobleme.

[#10]

Hallo Hr. @vogtsburger,

da muss ich widersprechen.

In allen computerbasierten Berechnungsprogrammen werden diese Fehler auftreten können, auch in anderen Kalkulationsprogrammen oder in Datenbankprogrammen wie z. B. in Access, werden Sie die gleichen Probleme haben. Ich spreche da aus einer leidvollen Erfahrungen.

Zeiten werden halt intern in Dezimal berechnet und das muss eben berücksichtigt werden.

Gruß Achilleus

[#11]

Hallo,

die Problematik ist nicht nur auf Zeitberechnungen beschränkt, das ist ein allgemeines Problem bei der Gleitkommaberechnung.

Geben Sie z.B. in Excel oder auch in Libre Office folgende Werte ein:

A1: 5

A2: 4,9

A3: =A1-A2

A4: =A3=0,1

Das Ergebnis in A4 ist in beiden Programmen der Wert "FALSCH" und wenn Sie die Zellenformatierung auf 16  Dezimalstellen erhöhen sehen Sie auch warum.

Eine typische Lösung für dieses Problem ist z.B. die Funktion Runden.

Wenn in A3 die Formel so geändert wird: =RUNDEN(A1-A2;2) wird in A4 als Ergebnis "WAHR" ausgegeben.

Weiterführende Hinweise:

http://de.wikipedia.org/wiki/Gleitkommazahl

http://de.wikipedia.org/wiki/IEEE_754

http://www.bernd-leitenberger.de/genauigkeit-von-computern.shtml

[#12]

@peter 

ein sehr schönes Beispiel, und vermeintlich so trivial.

Man erhält oft von Mandanten irgendwelche Excel-Tabellen mit irgendwelchen Kalkulationen und die Mandanten sind oft sehr überzeugt von ihren Rechenkünsten.

Aber man sieht ja, dass schon bei vermeintlich einfachsten Operationen Denk- und Rechenfehler passieren können.

Die Excel-Funktion "RUNDEN" ist tatsächlich in unserem Bereich eine der wichtigsten Funktionen überhaupt

Übrigens, noch eine kurze Frage an die Anwender von Excel für MAC (der ich nicht bin):

bekanntlich entspricht die Zahl 44000 dem Datum 18.06.2020

Entspricht die Zahl 44000 auch beim Excel für Mac dem Datum 18.06.2020 ?

(Ich meine nämlich, mich zu erinnern, dass es bei Datums- und Uhrzeit-Umrechnungen zwischen Windows und Mac Unterschiede gibt, kann mich aber auch täuschen. Vielleicht war das ja nur ein Alptraum, in dem ich verzweifelt nach einer Rundungsdifferenz gesucht hatte) 😀

Nachtrag: 

Ich hatte übrigens mal einen Fall aus der Praxis, bei dem sich die Rundungsdifferenzen zu Tausenden von Euros aufsummiert haben.

Es ging damals um große Lieferungen und um Preisvereinbarungen mit REWE (dem Lebensmittelkonzern) und der Umrechnung von DM in Euro.

Die dritte und vierte Stelle hinter dem Komma wurde einfach abgerundet, was sich im Laufe eines Jahres zu einigen Tausend Euro aufsummiert hat.

Der Mandant bemerkte diese Differenz nicht selbst, hatte dann aber doch kurz vor der Verjährung Grund zur Freude 😀

[#13]

Hallo,

Ich meine nämlich, mich zu erinnern, dass es bei Datums- und Uhrzeit-Umrechnungen zwischen Windows und Mac Unterschiede gibt, kann mich aber auch täuschen

die Erinnerung täuscht nicht.

https://docs.microsoft.com/de-de/office/troubleshoot/excel/1900-and-1904-date-system

[#14]

@peter ,

... eine sehr schöne Quelle, danke !

... man sieht, dass das Runden von Zeiten per Formel ähnlich wichtig ist wie die Runden-Zeiten in der Formel 1😀

(die Aussage "genauso wichtig" vermeide ich lieber, weil es auch hier kleine Differenzen geben könnte 😀)