Fehler in der EDATE/EDATUM-Funktion ?
Frank Massel
Wir haben in EXCEL 2003 SP2 bei der EDATUM-Funktion folgenden Fehler entdeckt:
Zelle A1: 01.01.2008
Zelle B1: -228
(soll als Monatszahl interpretiert werden, also = - 19 Jahre)
Zelle C1: =EDATUM(A1; B1),
Korrektes Ergebnis in C1: 01.01.1989
Jetzt weiter mit
Zelle B2: =-2,28 * 100
Zelle C2: =EDATUM(A1; B2),
Falsches Ergebnis in C2: 01.02.1989
Die Prüfung (z.B. in Zelle D1) =(B1=B2) liefert WAHR zurück, auch die
entsprechende WENN-Prüfung. D.h. -2,28 * 100 = -228, aber trotzdem sind die
beiden EDATUM-Funktionswerte unterschiedlich.
Mit = -22,8 * 10; -0,228 * 1000; -0,0228 * 10000 liefert die entsprechend
belegte EDATUM-Funktion wieder korrekt den 01.01.1989 zurück.
Beheben lässt sich der Fehler in C2 mit =EDATUM(GANZZAHL(A1); B1)
oder mit Runden(A1;0), aber das ist ja wohl übertrieben, oder ?
Machen wir was falsch oder ist das tatsächlich ein Fehler in der
EDATUM-Funktion ?
Danke im voraus !
Viele Grüße
Frank Massel
Alexander Wolff
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Frank Massel
Danke für die prompte Hilfe !
Die Lösung in dem von Dir angegebenen Querverweis behebt, das
"EDATUM"-Problem. Aber was doch merkwürdig bleibt:
Wieso liefert die EDATUM-Fkt. in meinen Beispielen für verschiedende
(-228)-Werte unterschiedliche Ergebnisse, obwohl die Gleichheitsprüfung
sowohl in der EXCEL-Tabelle als auch via VBA (Werte in double-Variablen
eingelesen) positiv ausfällt ?
Sollte das nicht allen EXCEL-AnwenderINNEN zu denken geben ? Liegt das
wirklich nur an der EDATUM-Funktion ?
Zur Erinnerung: Die "verschiedenen" (-228)-Werte sind:
-228; -22,8 *10; -2,28 * 100; -0,228 * 1000; ... wobei die EDATUM-Fkt. im
Fall
-2,28 * 100 ein falsches Ergebnis liefert (1 Tag zuviel, vgl. meine Frage im
ersten Posting).
Viele Grüße
Frank Massel
Thomas Ramel
Frank Massel schrieb am 28.01.2008
> Wieso liefert die EDATUM-Fkt. in meinen Beispielen für verschiedende
> (-228)-Werte unterschiedliche Ergebnisse, obwohl die Gleichheitsprüfung
> sowohl in der EXCEL-Tabelle als auch via VBA (Werte in double-Variablen
> eingelesen) positiv ausfällt ?
> Sollte das nicht allen EXCEL-AnwenderINNEN zu denken geben ? Liegt das
> wirklich nur an der EDATUM-Funktion ?
>
> Zur Erinnerung: Die "verschiedenen" (-228)-Werte sind:
> -228; -22,8 *10; -2,28 * 100; -0,228 * 1000; ... wobei die EDATUM-Fkt. im
> Fall
> -2,28 * 100 ein falsches Ergebnis liefert (1 Tag zuviel, vgl. meine Frage im
> ersten Posting).
Das dürfte nicht an EDATUM() liegen, sondern an der Gleitkommaberechnung,
die je nach Funktion und Wert nicht korrekt gerundete Werte zurück liefert.
Das ist aber kein Excel-spezifisches Problem.
Schau dir zur Vertigfung die folgenden Links noch näher an:
http://de.wikipedia.org/wiki/Gleitkommazahl
http://www.fmsinc.com/tpapers/math/index.html
http://support.microsoft.com/default.aspx?scid=kb;de;42980
http://support.microsoft.com/default.aspx?scid=kb;de;78113
http://support.microsoft.com/?id=196652
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps
Peter Schleif
> Wir haben in EXCEL 2003 SP2 bei der EDATUM-Funktion folgenden Fehler entdeckt:
Vermutlich eher die grundsätzliche Problematik bei der Darstellung von
Zahlen auf binären Systemen.
Stichwort: Maschinengenauigkeit
http://de.wikipedia.org/wiki/Maschinengenauigkeit
Peter
Frank Massel
danke für Deine Antwort !
Ich kenne die Gleitkommaproblematik und die damit verbundenen Ungenauigkeiten.
ABER worauf man sich bisher immer verlassen kann, sind die
Gleichheitsprüfungen in den mir bekannten Programmiersprachen (VB 6.0; VBA;
.NET VB; .NET C#; reines C):
Dort gilt doch immer:
Sind zwei Double-Variablen technisch(!) gleich (If d1 = d2 bzw. if(d1 == d2))
dann liefert eine Funktion mit diesen Variablen als Eingabeparameter an
derselben Stelle (und weitere vorhandene Parameter identisch belegt),
dasselbe(!) Ergebnis.
Ist doch unstrittisch, oder ?
Also als Fehlerprüfung umzusetzen z.B. mit (in VBA)
If d1 = d2 AND EineFkt(..., d1, ...) <> EineFkt(..., d2, ...) Then
MsgBox "Ich verstehe die Programmierwelt nicht mehr !!!"
EndIf
Konkret für mein Formel-Bsp. hier der entsprechende VBA-Quellcode:
---------------------------------------------
Public Sub Test_EDatum()
Dim Datum As Date
Dim Erg_Dat_EXCEL As Date
Dim Monate#
Monate# = CDbl(-2.28) * CDbl(100)
' CDbl(-2.28) * CDbl(100) oder CSng(...) und alle Kombinationen bringen
auch keine Gleichheit !
If Monate# = CDbl(-228) And EDATE(Datum, Monate#) <> EDATE(Datum, -228)
Then
MsgBox "Ich verstehe die Programmierwelt nicht mehr !"
End If
End Sub
---------------------------------------------
Hier wird die Meldung NICHT angezeigt (weil Monate# <> CDbl(-228)) und damit
dürfen sich dann auch EDATE(Datum, -228) und EDATE(Datum, -2.28 * 100)
unterscheiden (fällt eben unter die von Dir angesprochene
Gleitkommazahl-Problematik) -> Programmierwelt in Ordnung !
Aber dasselbe Szenario in einer Tabelle umgesetzt, liefert Gleichheit von
-228 und
-2.28 * 100 (z.B. mit =WENN(-228 = -2,28*100; "Gleich"; "Ungleich"..)
geprüft), aber unterschiedliche Ergebnisse bei der EDATUM-Funktion(...)
=EDATUM-Funktion("01.01.2008"; -228) liefert 01.01.1989 ABER
=EDATUM-Funktion("01.01.2008"; -2,28 * 100) liefert 01.02.1989 !?!?!
Damit haben wir auf jeden Fall ein unterschiedliches Verhalten zwischen der
EXCEL-Tabellenseite und der EXCEL-VBA-Seite !
Und schließlich: Wäre in EXCEL-Zellen wirklich der Wert von -228 und -2.28 *
100 unterschiedlich, dann sollten sich doch wohl wenigstens folgende
Ergebnisse unterscheiden, oder:
a) =EDATUM("01.01.2008";AUFRUNDEN(-2,28*100; 0))
b) =EDATUM("01.01.2008";ABRUNDEN(-2,28*100; 0))
Tun sie abr nicht: Liefern beide den (richtigen) 01.01.1989 zurück.
Nun gilt (wenigstens mathematisch) und meiner Meinung nach auch technisch in
der Gleitkommazahlenarithmetik (für eine bel. auch negative(!) Kommazahl z):
WENN AUFRUNDEN(z; 0) = ABRUNDEN(z; 0) genau dann wenn z eine ganze Zahl ist,
also wenn AUFRUNDEN(z; 0) = ABRUNDEN(z; 0) = z ist.
Und warum kommt dann in c) was anderes heraus ?
a) =EDATUM("01.01.2008";AUFRUNDEN(-2,28*100; 0)) = 01.01.1989
b) =EDATUM("01.01.2008";ABRUNDEN(-2,28*100; 0)) = 01.01.1989
c) =EDATUM("01.01.2008"; -2,28*100) = 01.02.1989
Und schlimmer noch:
Die Funktion EDATUM(Datum; Monate) sollte per logischer Definition bei
konstantem Datum bzgl. dem Parameter "Monate" wenigstens monoton steigend
sein (normalerweise sogar streng monoton steigend), auch in der
Gleitkommazahlarithmetik:
D.h. bei fixem Datum: Wenn Monate1 <= Monate2, dann EDATUM(Datum, Monate1)
<= EDATUM(Datum; Monate2) bzw. umgekehrt:
Wenn EDATUM(Datum, Monate1) > EDATUM(Datum; Monate2), dann Monate1 > Monate2
(wenigstens in dem Datumbereich "um den 01.01/02.1989", Schaltjahre mit
29.2.JJJJ mal weggelassen).
Damit und mit obigen a), b), c) folgt:
01.02.1989 = EDATUM("01.01.2008";-2,28*100)
>
01.01.1989 = EDATUM("01.01.2008";AUFRUNDEN(-2,28*100; 0))
= EDATUM("01.01.2008";ABFRUNDEN(-2,28*100; 0))
und damit müsste
-2,28*100 > AUFRUNDEN(-2,28*100; 0)
UND (!!!)
-2,28*100 > ABRUNDEN(-2,28*100; 0)
und das ist ohne Fehler in der Funktion EDATUM und/oder in der AUF- bzw.
ABRUNDEN-Funktion meines Erachtens nicht möglich...
Viele Grüße
Frank
Frank Massel
danke für den Hinweis. Das hier die Maschinengenauigkeit die alleinige
Ursache ist, glaube ich nicht. Lies Dir dazu bitte mal mein drittes Posting
(= Posting Nr. 5 absolut) durch:
Es gibt ein widersprüchliches Verhalten auf der EXCEL-Tabellenseite
gegebenüber z.B. EXCEL-VBA was die Gleichheit von -228 und -2,28 * 100 und
damit aufgerufenen EDATUM-Funktionen angeht.
Wenn eine Programmiersprache (oder eben eine EXCEL-Formel) uns sagt, dass
zwei Double-Werte technisch (= unter Berücksichtigung der
Gleitkommazahl-Problematik !) identisch sind, dann darf eine Funktion, die
mit beiden (jeweils) als Eingabeparameter gefüttert wird (natürlich an der
selben Parameter-Stelle und andere Eingabeparameter identisch) nicht
unterschiedliche Ergebnisse zurückliefern, oder ?
Schließlich sind dann ja in beiden Funktionsaufrufen BEIDE Parameterlisten
technisch komplett identisch und damit liefert keine (rein
mathematische/deterministische = ohne dynamische Einflussgrößen, wie aktuelle
Zeit uws.) Funktion unterschiedliche Werte zurück.
Ist mir bislang noch nirgendwo (VB; C; C#; VBA;...) untergekommen:
Wenn das so wäre, würden wir keine Programme, sondern Glückspiele
erstellen...
Viele Grüße
Frank
Thomas Ramel
Frank Massel schrieb am 28.01.2008
> Hallo Thomas,
>
> danke für Deine Antwort !
> Ich kenne die Gleitkommaproblematik und die damit verbundenen Ungenauigkeiten.
Na, dann ist ja alles bestens ;-)
> Aber dasselbe Szenario in einer Tabelle umgesetzt, liefert Gleichheit von
> -228 und
> -2.28 * 100 (z.B. mit =WENN(-228 = -2,28*100; "Gleich"; "Ungleich"..)
> geprüft),
Prüfe hier mal die Diffenz gegen 0, z.B. mit =(-228-(-2.28*100))=0, dann
zeigt sich die Gleikomma-Problematik hier wieder.
> aber unterschiedliche Ergebnisse bei der EDATUM-Funktion(...)
> =EDATUM-Funktion("01.01.2008"; -228) liefert 01.01.1989 ABER
> =EDATUM-Funktion("01.01.2008"; -2,28 * 100) liefert 01.02.1989 !?!?!
>
> Damit haben wir auf jeden Fall ein unterschiedliches Verhalten zwischen der
> EXCEL-Tabellenseite und der EXCEL-VBA-Seite !
Da bin ich nicht ganz gleicher Ansicht - siehe den Differenz-Vergleich mit
0.
Daher würde ich (falls keine Ganzzahl als Parameter übergeben werden kann)
mit RUNDEN() dafür sorgen, dass es eine wird.
Frank Massel
danke für Deine Antwort !
Wie gesagt: U.a. in VBA ist -228 <> -2,28 * 100, also war schon klar, dass
es an der Gleitkommazahl-Problematik liegt.
"Thomas Ramel" wrote:
>Prüfe hier mal die Diffenz gegen 0, z.B. mit =(-228-(-2.28*100))=0, dann
>zeigt sich die Gleikomma-Problematik hier wieder.
Danke für den Tipp, damit haben wir auch auf der EXCEL-Tabellenseite endlich
den Beweis, dass -228 <> -2,28 * 100 ist !
Aber mal ehrlich: Wenn man sich immer erst in Abhängigkeit der
Programmierumgebung überlegen muss, wie man die Gleichheit von Zahlen prüft,
wird's irgendwie lästig:
=WENN(-228-(-2,28*100)=0;"Gleich";"Ungleich") liefert "Ungleich" und
=WENN(-228=-2,28*100;"Gleich";"Ungleich") liefert "Gleich" ...
Selbst wenn das von ProgrammierINNEN noch erwartet werden kann:
Die EXCEL-Tabellenseite (gegenüber EXCEL-VBA) wird oft von
Programmier-"Laien" verwendet (bei uns sind es ca. 70) und denen kann man
solche Merkwürdigkeiten weder erklären noch sie zu solchen scheinbar
unsinnigen Formel-Ergänzungen wie z.B. die RUNDEN()-Fkt. in
=EDATUM("01.01.2008"; RUNDEN(-2,28*100; 0)) überreden.
Ich werde in unsere zentrale EXCEL-Funktionenbibliothek eine "eigene"
EDATUM-Fkt. ergänzen (nach dem Formel-Bsp. im Querverweis von Alexanders
Antwort), und zwar mit dem Monate-Parameter als Integer (damit funktioniert
auch das -2,28 * 100 -Beispiel). Deren Anwendung bekommen wir bei den
AnwenderINNEN auch durchgesetzt.
Nebeneffekt: Und wir brauchen damit das Microsoft AddIn "Analyse-Funktionen"
nicht mehr zu laden.
Viele Grüße
Frank
Thomas Ramel
Frank Massel schrieb am 29.01.2008
> danke für Deine Antwort !
>
> Wie gesagt: U.a. in VBA ist -228 <> -2,28 * 100, also war schon klar, dass
> es an der Gleitkommazahl-Problematik liegt.
>
> "Thomas Ramel" wrote:
>
>>Prüfe hier mal die Diffenz gegen 0, z.B. mit =(-228-(-2.28*100))=0, dann
>>zeigt sich die Gleikomma-Problematik hier wieder.
>
> Danke für den Tipp, damit haben wir auch auf der EXCEL-Tabellenseite endlich
> den Beweis, dass -228 <> -2,28 * 100 ist !
Fein; dann hängt das Excel-Bild ja wieder gerade :-)
> Aber mal ehrlich: Wenn man sich immer erst in Abhängigkeit der
> Programmierumgebung überlegen muss, wie man die Gleichheit von Zahlen prüft,
> wird's irgendwie lästig:
>
> =WENN(-228-(-2,28*100)=0;"Gleich";"Ungleich") liefert "Ungleich" und
> =WENN(-228=-2,28*100;"Gleich";"Ungleich") liefert "Gleich" ...
Ja, da legst Du die Reihenfolge der Arithemtik fest, was durchaus einen
Einfluss haben kann - zumal duale Logik nicht ganz dasselbe ist wie im
Zehner-System.
> Selbst wenn das von ProgrammierINNEN noch erwartet werden kann:
> Die EXCEL-Tabellenseite (gegenüber EXCEL-VBA) wird oft von
> Programmier-"Laien" verwendet (bei uns sind es ca. 70) und denen kann man
> solche Merkwürdigkeiten weder erklären noch sie zu solchen scheinbar
> unsinnigen Formel-Ergänzungen wie z.B. die RUNDEN()-Fkt. in
> =EDATUM("01.01.2008"; RUNDEN(-2,28*100; 0)) überreden.
Vielleicht klappt das mit dem Studium der oben genannten Links ja nun etwas
besser ;-)
> Ich werde in unsere zentrale EXCEL-Funktionenbibliothek eine "eigene"
> EDATUM-Fkt. ergänzen (nach dem Formel-Bsp. im Querverweis von Alexanders
> Antwort), und zwar mit dem Monate-Parameter als Integer (damit funktioniert
> auch das -2,28 * 100 -Beispiel). Deren Anwendung bekommen wir bei den
> AnwenderINNEN auch durchgesetzt.
>
> Nebeneffekt: Und wir brauchen damit das Microsoft AddIn "Analyse-Funktionen"
> nicht mehr zu laden.
Wenn das machbar ist und bei euch eh Uso, dann macht das durchaus Sinn.
Peter Schleif
>
> Sind zwei Double-Variablen technisch(!) gleich [...]
^^^^^^^^^^
Hier ist IMHO schon dein gedanklicher Fehler. Woher willst Du wissen,
dass sie technisch (IEEE) gleich sind?
Du übergibst Excel letztlich nur eingetippte Zahlenfolgen:
"228" oder "2,28 * 100"
Was Excel daraus macht und wie diese Eingaben umgesetzt werden
entzieht sich (zumindest) meiner Kenntnis. Und Deiner wohl auch. Es
sei denn Du hast zufälligerweise den Excel-Quellcode zu Hause
rumliegen. Und am besten auch noch das Layout deiner FPU. :-)
Peter
P.S.: Hast Du?