Runden in Excel die Zweite

Matthias8519 · 14. April 2008 um 18:19

Hallo,

ich hatte weiter unten die Frage stehen, wie man bei einer Zahl anhand einer Formel auf die zweite Stelle einer Zahl mit drei Nachkommastellen runden kann und zwar immer auf X,X5 oder X,X0. Nun habe ich anhand der Hilfen hier aus dem Forum und mit einem Freund die folgende Formel gebaut:

=WENN(REST(RUNDEN(A1;2)*10;1)=0;RUNDEN(A1;2);(RUNDEN(A1;2)*100-REST(RUNDEN(A1;2)*100;10)+WAHL(REST(RUNDEN(A1;2)*100;10);0;0;5;5;5;5;5;10;10))/100)

Allerdings gibt es hier folgendes Phänomen, und nach gut 3 Stunden suchen sind wir nicht drauf gekommen woher das Phänomen kommt und nun hoffe ich auf eure Hilfe: bei den folgenden Zahlenbereichen kommt immer ein Fehler („WERT“):
20-16,305 bis 20-16,314 (also 20,305; 20,304; … ; 16,306; 16,305)
19-16,805 bis 19-16,805 (also 19,805; 19,804; … ; 16,806; 16,805)
4,815 bis 4,806
4,315 bis 4,306
2,515 bis 2,506
2,015 bis 2,006

Alle anderen Zahlen (Bereich 1,000 bis 25,000) werden richtig ausgegeben!!! Haben wir es hier mit einem Bug zu tun???

Ich hoffe das ihr mir nochmal weiterhelfen könnt!!!

Grüße
Matthias

Matthias8519 · 14. April 2008 um 18:30

Richtigstellung Zahlenbereiche
Hier habe ich die Zahlenbereiche in denen die Formel nicht funktioniert nochmal verständlicher aufgeschrieben, wobei es wohl am einfachsten ist diese kurz in Excel zu erstellen

20,314 bis 20,305; 19,314 bis 19,305; 18,314 bis 18,305; 17,314 bis 17,305; 16,314 bis 16,305
19,814 bis 19,805; 18,814 bis 18,805; 17,814 bis 17,805; 16,814 bis 16,805
4,815 bis 4,806
4,315 bis 4,306
2,515 bis 2,506
2,015 bis 2,006

Reinhard_fb8cb7 · 14. April 2008 um 18:47

Hi Matthias,

ich hatte weiter unten die Frage stehen, wie man bei einer
Zahl anhand einer Formel auf die zweite Stelle einer Zahl mit
drei Nachkommastellen runden kann und zwar immer auf X,X5 oder
X,X0. Nun habe ich anhand der Hilfen hier aus dem Forum und
mit einem Freund die folgende Formel gebaut:

=WENN(REST(RUNDEN(A1;2)*10;1)=0;RUNDEN(A1;2);(RUNDEN(A1;2)*100-REST(RUNDEN(A1;2)*100;10)+WAHL(REST(RUNDEN(A1;2)*100;10);0;0;5;5;5;5;5;10;10))/100)

meine Formel sah so aus: =RUNDEN(A1*2;1)/2

Was gefällt dir an ihr nicht daß du lieber so einen Formelwurm
benutzen willst?
Nachstehend sind die Formeln im Vergleich gelistet.

Alle anderen Zahlen (Bereich 1,000 bis 25,000) werden richtig
ausgegeben!!! Haben wir es hier mit einem Bug zu tun???

Glaube nicht. Ich nehme an Fehler ist in der Formel, vielllicht wählt ihr durch Wahl das vierte Element aus aber es gibt nur drei, oder sowas ähnliches liegt vor.

Tabellenblatt: [Mappe2]!Tabelle1
 │ F │
──┼────────┤
1 │ a │
──┼────────┤
2 │ b │
──┼────────┤
3 │ c │
──┼────────┤
4 │ #WERT! │
──┴────────┘
Benutzte Formeln:
F1: =WAHL(ZEILE();"a";"b";"c")
F2: =WAHL(ZEILE();"a";"b";"c")
F3: =WAHL(ZEILE();"a";"b";"c")
F4: =WAHL(ZEILE();"a";"b";"c")

Gruß
Reinhard



    Tabellenblatt: [Mappe2]!Tabelle1
     │ A │ B │ C │
    ──┼───────┼────────┼──────┤
    1 │ 4,807 │ #WERT! │ 4,80 │
    ──┼───────┼────────┼──────┤
    2 │ 4,806 │ #WERT! │ 4,80 │
    ──┼───────┼────────┼──────┤
    3 │ 4,805 │ #WERT! │ 4,80 │
    ──┼───────┼────────┼──────┤
    4 │ 4,804 │ 4,80 │ 4,80 │
    ──┼───────┼────────┼──────┤
    5 │ 4,803 │ 4,80 │ 4,80 │
    ──┼───────┼────────┼──────┤
    6 │ 4,802 │ 4,80 │ 4,80 │
    ──┼───────┼────────┼──────┤
    7 │ 4,801 │ 4,80 │ 4,80 │
    ──┼───────┼────────┼──────┤
    8 │ 4,800 │ 4,80 │ 4,80 │
    ──┴───────┴────────┴──────┘
    Benutzte Formeln:
    A4: =A3-0,001
    A5: =A4-0,001
    A6: =A5-0,001
    A7: =A6-0,001
    A8: =A7-0,001
    B1: =WENN(REST(RUNDEN(A1;2)\*10;1)=0;RUNDEN(A1;2);(RUNDEN(A1;2)\*100-REST(RUNDEN(A1;2)\*100;10)+WAHL(REST(RUNDEN(A1;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B2: =WENN(REST(RUNDEN(A2;2)\*10;1)=0;RUNDEN(A2;2);(RUNDEN(A2;2)\*100-REST(RUNDEN(A2;2)\*100;10)+WAHL(REST(RUNDEN(A2;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B3: =WENN(REST(RUNDEN(A3;2)\*10;1)=0;RUNDEN(A3;2);(RUNDEN(A3;2)\*100-REST(RUNDEN(A3;2)\*100;10)+WAHL(REST(RUNDEN(A3;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B4: =WENN(REST(RUNDEN(A4;2)\*10;1)=0;RUNDEN(A4;2);(RUNDEN(A4;2)\*100-REST(RUNDEN(A4;2)\*100;10)+WAHL(REST(RUNDEN(A4;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B5: =WENN(REST(RUNDEN(A5;2)\*10;1)=0;RUNDEN(A5;2);(RUNDEN(A5;2)\*100-REST(RUNDEN(A5;2)\*100;10)+WAHL(REST(RUNDEN(A5;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B6: =WENN(REST(RUNDEN(A6;2)\*10;1)=0;RUNDEN(A6;2);(RUNDEN(A6;2)\*100-REST(RUNDEN(A6;2)\*100;10)+WAHL(REST(RUNDEN(A6;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B7: =WENN(REST(RUNDEN(A7;2)\*10;1)=0;RUNDEN(A7;2);(RUNDEN(A7;2)\*100-REST(RUNDEN(A7;2)\*100;10)+WAHL(REST(RUNDEN(A7;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    B8: =WENN(REST(RUNDEN(A8;2)\*10;1)=0;RUNDEN(A8;2);(RUNDEN(A8;2)\*100-REST(RUNDEN(A8;2)\*100;10)+WAHL(REST(RUNDEN(A8;2)\*100;10);0;0;5;5;5;5;5;10;10))/100)
    C1: =RUNDEN(A1\*2;1)/2
    C2: =RUNDEN(A2\*2;1)/2
    C3: =RUNDEN(A3\*2;1)/2
    C4: =RUNDEN(A4\*2;1)/2
    C5: =RUNDEN(A5\*2;1)/2
    C6: =RUNDEN(A6\*2;1)/2
    C7: =RUNDEN(A7\*2;1)/2
    C8: =RUNDEN(A8\*2;1)/2
    
    Zahlenformate der Zellen im gewählten Bereich:
    A1:A8
    haben das Zahlenformat: 0,000
    B1:B8,C1:C8
    haben das Zahlenformat: 0,00


Tabellendarstellung erreicht mit dem Code in [FAQ:2363](/t/faq/9292363)

Georg_V · 14. April 2008 um 20:30

Hallo Matthias,

mit ein bischen Nachdenken, kommt man darauf, dass nur der letzte Teil der Formel der Verursacher sein kann:

WAHL(REST(RUNDEN(A1;2)\*100;10);0;0;5;5;5;5;5;10;10))

genauer der erste Parameter Rest(Runden(a1;2)*100;10). Ein Fehler #Wert (oder OpenOffice Err:502) kann nur kommen, wenn der Wert keine ganze Zahl ist. Also flux mal Ganzzahl(Rest(Runden(a1;2)*100;10)) gebildet und festgestellt, dass dies bei den fraglichen Zahlen einen um eins reduzierten Wert ergibt. (Der Wert der Restfunktion ist genauer aufgeschrieben: 0,99999999999997)

Allerdings gibt es hier folgendes Phänomen, und nach gut 3
Stunden suchen sind wir nicht drauf gekommen woher das
Phänomen kommt und nun hoffe ich auf eure Hilfe: bei den
folgenden Zahlenbereichen kommt immer ein Fehler („WERT“):
20-16,305 bis 20-16,314
Alle anderen Zahlen (Bereich 1,000 bis 25,000) werden richtig
ausgegeben!!! Haben wir es hier mit einem Bug zu tun???

Nein kein Bug, nur mit dem Problem, dass Zahlen nicht mit unendlich langer Nachstellenanzahl gerechnet werden. Also nimm bitte den Vorschlag von Reinhard an, der knapp und knackig und nicht gegen kleines Abweichungen empfindlich.

MfG Georg V.

Thomas_Ramel · 14. April 2008 um 21:17

Grüezi Mathias

ich hatte weiter unten die Frage stehen, wie man bei einer
Zahl anhand einer Formel auf die zweite Stelle einer Zahl mit
drei Nachkommastellen runden kann und zwar immer auf X,X5 oder
X,X0.

Warum denn nicht den Vorschlag verwenden den Du erhalten hast?

20-16,305 bis 20-16,314 (also 20,305; 20,304; … ; 16,306;
16,305)
19-16,805 bis 19-16,805 (also 19,805; 19,804; … ; 16,806;
16,805)
4,815 bis 4,806
4,315 bis 4,306
2,515 bis 2,506
2,015 bis 2,006

Alle anderen Zahlen (Bereich 1,000 bis 25,000) werden richtig
ausgegeben!!! Haben wir es hier mit einem Bug zu tun???

Kaum, ich denke eher, dass euer Formel-Monster nicht ‚tauglich‘ ist, resp. ihr dabei die Gleikomma-Ungenauigkeiten nicht berücksichtigt habt. Ein Vergleich mit ‚0‘ ist in diesem Bereich nie ratsam.

Folgender Auszug mag reichen um zu zeigen, dass die genannte Formel durchaus stimmig ist:

Tabellenblatt: [MAPPE1]!Tabelle1
 │ A │ B │
───┼───────┼──────┤
 1 │ 2.006 │ 2 │
───┼───────┼──────┤
 2 │ 2.007 │ 2 │
───┼───────┼──────┤
 3 │ 2.008 │ 2 │
───┼───────┼──────┤
 4 │ 2.009 │ 2 │
───┼───────┼──────┤
 5 │ 2.01 │ 2 │
───┼───────┼──────┤
 6 │ 2.011 │ 2 │
───┼───────┼──────┤
 7 │ 2.012 │ 2 │
───┼───────┼──────┤
 8 │ 2.013 │ 2 │
───┼───────┼──────┤
 9 │ 2.014 │ 2 │
───┼───────┼──────┤
10 │ 2.015 │ 2 │
───┼───────┼──────┤
11 │ 2.016 │ 2 │
───┼───────┼──────┤
12 │ 2.017 │ 2 │
───┼───────┼──────┤
13 │ 2.018 │ 2 │
───┼───────┼──────┤
14 │ 2.019 │ 2 │
───┼───────┼──────┤
15 │ 2.02 │ 2 │
───┼───────┼──────┤
16 │ 2.021 │ 2 │
───┼───────┼──────┤
17 │ 2.022 │ 2 │
───┼───────┼──────┤
18 │ 2.023 │ 2 │
───┼───────┼──────┤
19 │ 2.024 │ 2 │
───┼───────┼──────┤
20 │ 2.025 │ 2.05 │
───┼───────┼──────┤
21 │ 2.026 │ 2.05 │
───┼───────┼──────┤
22 │ 2.027 │ 2.05 │
───┼───────┼──────┤
23 │ 2.028 │ 2.05 │
───┼───────┼──────┤
24 │ 2.029 │ 2.05 │
───┼───────┼──────┤
25 │ 2.03 │ 2.05 │
───┴───────┴──────┘
Benutzte Formeln:
B1 : =RUNDEN(A1/0.05;0)\*0.05

A1:B25
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen
Thomas Ramel

–

MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-3]

Matthias8519 · 14. April 2008 um 21:39

Vielen Dank schonmal für die beiden Formeln, ist echt deutlich einfacher… keine ahnung wie das untergegangen ist, haben uns wohl so in den wahn dieser langen formel gearbeitet.

allerdings gibt es hier bei beiden deutlich einfacheren formeln (=RUNDEN(C1/0,05;0)*0,05 und =RUNDEN(A1*2;1)/2) das problem, dass ab 1,075 falsch gerundet wird und zwar alle X,X75 und X,X25. Z.B. wird 1,075 abgerundet auf 1,05 obwohl es eigentlich 1,10 sein müsste. 1,025 stimmt -> wird zu 1,05
kann man dieses problemchen auch noch irgendwie lösen???

Matthias8519 · 14. April 2008 um 21:52

ok hab den fehler grad schon selbst gefunden.
wenn man in a1 1,000 schreibt und in a2 dann =A1+0,001 und das dann bis unten kopiert, fängt excel bei 1,47 an mit 1,469999999999 und hier liegt dann wohl der fehler. es wird nicht mehr mit 1,47 sondern mit 1,46 gerechnet. warum auch immer das passiert. wenn man 1,075 von hand in die zelle schreibt, dann geht es!!! DANKE

jetzt teste ich es morgen in der datei in der es benötigt wird, bin aber sehr zuversichtlich das es funktioniert.

anonsten melde ich mich nochmal kurz

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Thomas_Ramel · 14. April 2008 um 23:07

Grüezi Matthias

ok hab den fehler grad schon selbst gefunden.
wenn man in a1 1,000 schreibt und in a2 dann =A1+0,001 und das
dann bis unten kopiert, fängt excel bei 1,47 an mit
1,469999999999 und hier liegt dann wohl der fehler. es wird
nicht mehr mit 1,47 sondern mit 1,46 gerechnet. warum auch
immer das passiert.

Das ist die Gleikomma-Arithmetik die hier mit ins Speil kommt.

Du kannst solch lange Datenreihen mit festem Inkrement zuverlässiger wie folgt erzeugen:

1 in A1 schreiben
A1 wieder markieren
Menü: ‚Bearbeiten‘
Ausfüllen
Reihe…
Reihe in [x] Spalten
Typ: [x] Linear
Inkrement: 0.001
Endwert: 25
[OK]

Schon hast Du eine saubere Datenreihe ohne mit der Maus zu ziehen.

Mit freundlichen Grüssen
Thomas Ramel

–

MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-3]

Matthias8519 · 15. April 2008 um 10:29

Du kannst solch lange Datenreihen mit festem Inkrement
zuverlässiger wie folgt erzeugen:

1 in A1 schreiben

A1 wieder markieren

Menü: ‚Bearbeiten‘

Ausfüllen

Reihe…

Reihe in [x] Spalten

Typ: [x] Linear

Inkrement: 0.001

Endwert: 25

[OK]

Danke für den Tipp. Man lernt doch nie aus!

Conrad_319e51 · 16. April 2008 um 18:38

Hallo Matthias,

ich habe mir schon lange angewöhnt alles in runden() zu packen, auch da wo es nicht notwendig scheint.

Übrigens1: Kann es sein, daß das Gleitkommaproblem in Excel 2003 gelöst wurde? Konnte den Fehler jedenfalls nicht nachstellen.

Übrigens2: Danke für den Hinweis mit Bearbeiten - Ausfüllen. Kannte ich auch noch nicht.

Gruß Conrad

Thomas_Ramel · 16. April 2008 um 19:16

Grüezi Conrad

Übrigens1: Kann es sein, daß das Gleitkommaproblem in Excel
2003 gelöst wurde? Konnte den Fehler jedenfalls nicht
nachstellen.

Nein, auch xl2003 weist diese Besonderheit auf, da dies kein spezifisches Excel-Problem sondern ein allgemeines bei der digitalen Datenverarbeitung ist.
Es gibt nun halt mal Werte (0,1 ist z.b. ein solcher) die binär nicht endlich dargestellt werden können. Daher ergeben sich je nach Konstellation und Erzeugung der Quelldaten die Probleme. Im allgemeinen sind sie im Bereich um ‚0‘ herum am auffallendsten.

Übrigens2: Danke für den Hinweis mit Bearbeiten - Ausfüllen.
Kannte ich auch noch nicht.

Aber gerne doch - für lange Reihen ist dieses Ausfüllen unschlagbar flexibel und schnell.

Mit freundlichen Grüssen
Thomas Ramel

–

MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-3]

Reinhard_fb8cb7 · 16. April 2008 um 19:22

Übrigens1: Kann es sein, daß das Gleitkommaproblem in Excel
2003 gelöst wurde? Konnte den Fehler jedenfalls nicht
nachstellen.

Hi Conrad,

ich habe mein XL2003 noch nicht wieder installiert seit dem Umbau im PC.

Gehe doch mal zu Extras–Optionen–Berechnen, hast du da einen Haken bei "enauigkeit wie angezeigt!?

Und, wennn du im Fenster auf das Fragezeichen klickst dann auf den Punkt, steht da was von 15 Stellen oder steht da eine höhere Zahl?

So oder so, das Gleitkommaproblem kann man grundsätzlich nie lösen.
Man kann es nur durch Erhöhung der Nachkommazahlen für Normaluser „wegzaubern“, aber die Grundproblematik von Dezimal zu binär bleibt.

Als die ersten Taschenrechner rauskamen kam gelegentlich bei 4/2 1,99999999 raus.
Dies wurde dann durch Trickserei in der Software nicht behoben, sondern „weggezaubert“ …

Gruß
Reinhard