Supportnet / Forum / Tabellenkalkulation
Probleme beim Runden in Excel
Frage
Hallo zusammen,
Ich habe folgendes Problem:
Beim Rechnen mit Geldbetraegen habe ich immer wieder das Problem, dass Excel beim Runden ein paar Cent unter den Tisch fallen laesst - oder zuviel berechnet. Das Problem ist, dass man diese Zahlen praktisch nicht zum Weiterarbeiten nutzen kann, da ja die Summe (gerechnet mit den auf 2 Dezimale gerundeten Betraegen) hoeher bzw. niedriger ist als der urspruengliche Wert...
Ganz schoen verwirrend, wenn ich das so lese, also lieber mal ein Beispiel:
10 € aufgeteilt auf 6 Parteien sind per Excel (gerundet auf 2 Dezimale): 6 x 1,67. Wenn man diese gerundeten Zahlen manuell (sprich mit eigenem Gehirn) summiert, kommt man auf 10,02€.
Gibt es eine Moeglichkeit, dass Excel im Sinne der richtigen Gesamtsumme z.B. die letzten beiden Ergebnisse abrundet? Oder wenigstens eine Funktion, die auf eine falsche Summe durch Rundungsfehler hinweist?
Danke, danke, danke fuer eure Hilfe!!!
Gruss,
Christian
Antwort 1 von pelle
Hallo Christian,
so ganz verstehe ich dein Problem bzw. die Wertigkeit nicht; sorry!
Excel kann sicherlich nichts für das Problem, das es keine 1/100 Euro Münzen kennt!
Ein blöder Spruch ist:
"Ein wenig Schwund ist immer"!
Ist dieses Problem nicht vernachlässigbar?
gruss
pelle
so ganz verstehe ich dein Problem bzw. die Wertigkeit nicht; sorry!
Excel kann sicherlich nichts für das Problem, das es keine 1/100 Euro Münzen kennt!
Ein blöder Spruch ist:
"Ein wenig Schwund ist immer"!
Ist dieses Problem nicht vernachlässigbar?
gruss
pelle
Antwort 2 von Primut
Hi Christian,
um ehrlich zu sein kann ich dein Problem auch nicht verstehen! Hast du denn mal versucht, mit den entsprechenden Werten weiterzurechnen?
Hinweis: Excel speichert die Orginal - Werte, zeigt aber entsprechend der Formatierung entsprechende Rundungen an, so zB. bei Währung immer auf zwei Komma-Stellen.
D.h. auf dein Beispiel bezogen: Scheibe 10 in A1 und formatiere als Währung: es werden 10,00 € angezeigt, schreibe in B1: =A1/6 und formatiere als Währung: es wird 1,67 € angezeigt, , schreibe in C1: =B1*6 und formatiere als Währung: es werden wieder 10,00 € angezeigt!
Im übrigen wird im Finanzwesen auch gerundet (zB bei Zinsen), da es, wie Pelle richtig feststellte, keine 1/100 Münzen gibt!
Gruß
um ehrlich zu sein kann ich dein Problem auch nicht verstehen! Hast du denn mal versucht, mit den entsprechenden Werten weiterzurechnen?
Hinweis: Excel speichert die Orginal - Werte, zeigt aber entsprechend der Formatierung entsprechende Rundungen an, so zB. bei Währung immer auf zwei Komma-Stellen.
D.h. auf dein Beispiel bezogen: Scheibe 10 in A1 und formatiere als Währung: es werden 10,00 € angezeigt, schreibe in B1: =A1/6 und formatiere als Währung: es wird 1,67 € angezeigt, , schreibe in C1: =B1*6 und formatiere als Währung: es werden wieder 10,00 € angezeigt!
Im übrigen wird im Finanzwesen auch gerundet (zB bei Zinsen), da es, wie Pelle richtig feststellte, keine 1/100 Münzen gibt!
Gruß
- Primut
Antwort 3 von Kenshinsai
Danke für die Antworten.
Scheinbar scheint es keine Lösung für mein Problem zu geben. Aber des Verstehens halber mal die Erklärung:
Bin grad in Spanien in einer Im-/Export-Agentur dabei ein paar Vorgänge zu optimieren. Die eingehenden Rechnungen werden anteilig auf die Klienten abgerechnet. Um das zu vereinfachen hab ich eben eine Tabelle für einen bestimmten Vorgang gemacht. Wenn allerdings die Werte nicht auf den Cent stimmen kann man das ganze nicht buchen, weil der Gegenwert nicht stimmt. Und welchen Sinn hat eine Tabellenkalkulation, wenn man am Ende doch alles nachrechnen muss? ;-)
Aber danke trotzdem, ich finde schon eine Lösung.
Gruß,
Christian
Scheinbar scheint es keine Lösung für mein Problem zu geben. Aber des Verstehens halber mal die Erklärung:
Bin grad in Spanien in einer Im-/Export-Agentur dabei ein paar Vorgänge zu optimieren. Die eingehenden Rechnungen werden anteilig auf die Klienten abgerechnet. Um das zu vereinfachen hab ich eben eine Tabelle für einen bestimmten Vorgang gemacht. Wenn allerdings die Werte nicht auf den Cent stimmen kann man das ganze nicht buchen, weil der Gegenwert nicht stimmt. Und welchen Sinn hat eine Tabellenkalkulation, wenn man am Ende doch alles nachrechnen muss? ;-)
Aber danke trotzdem, ich finde schon eine Lösung.
Gruß,
Christian
Antwort 4 von Kenshinsai
PS: Probleme macht dabei das Buchungsprogramm, dass die Buchung nicht annimmt, wenn die Beträge nicht stimmen.
Antwort 5 von Tomschi
Hi Christian!
Wie wäre es damit:
Mach doch eine bedingte Formatierung die Dir anzeigt, ob das Ergebnis mal dem Divisor gleich dem Dividenden ist.
Beispiel:
A1 = 10
B1 = 6
C1= Ergebnis
Bedingte Formatierung für C1:
=C1*B1<>A1
Tom
Wie wäre es damit:
Mach doch eine bedingte Formatierung die Dir anzeigt, ob das Ergebnis mal dem Divisor gleich dem Dividenden ist.
Beispiel:
A1 = 10
B1 = 6
C1= Ergebnis
Bedingte Formatierung für C1:
=C1*B1<>A1
Tom
Antwort 6 von Kenshinsai
Danke Tomschi,
du hast recht, das scheint mir auch das einzig Sinnvolle! Wenigstens ist man sich dann der Fehler bewusst ;-)
ta-ta
Christian
du hast recht, das scheint mir auch das einzig Sinnvolle! Wenigstens ist man sich dann der Fehler bewusst ;-)
ta-ta
Christian
Antwort 7 von CaroS
Hallo Christian,
Das ist das Problem! Es entsteht, weil es an diesem Punkt nun mal einen Unterschied zwischen Theorie und Praxis gibt. Theoretisch kannst Du jeder Deiner 6 Parteien "anteilig" genau ein Sechstel von 10 € in Rechnung stellen. Nur praktisch geht das eben nicht, da 10/6 eine rationale Zahl in Form eines unendlichen Bruchs ergibt.
Jeder endliche Dezimalbruch enthält aber unvermeidlich einen Fehler gegenüber dem exakten theoretischen Wert. Das ist kein Problem von Excel oder einem anderen Rechenprogramm und kann deshalb auch nicht im Rahmen irgendeines praktischen Programms gelöst werden, jedenfalls nicht unter der Voraussetzung, dass alle Bedingungen (gleich große Anteile, exakte Summe) gleichzeitig erfüllt werden.
Wenn das Problem in der Praxis gelöst werden soll - für eine statistische Aussage ist dies sicherlich nicht nötig - , wird sich die Buchhaltung auf die Praxis zubewegen und Abstriche an der "Theorie" machen müssen, anders geht es nicht. Eine Buchhaltung, die dazu nicht in der Lage ist, ist keine besonders gute. (Sie ist etwa genauso nützlich wie die Aussage "An jedem Tag waren 1453,33 Leute im Museum" - was theoretisch absolut richtig sein kann, man glaubt es aber erst, nachdem man in Gedanken eine "kleine Korrektur" vorgenommen hat.)
Wenn 6 Leute Geld zusammenlegen, um eine gemeinsame Zeche von genau 10 € zu zahlen, und die Anteile möglichst gerecht, aber natürlich nicht gleich groß sein sollen, weil das ja praktisch nicht geht, dann werden am Ende wohl 4 Leute 1,66 € und zwei Leute 1,67 € dazugeben. So läuft es unter vernünftigen Menschen. Da es keine Zwei-Drittel-Cent-Münzen gibt, ist dies die einzige und gerechteste Möglichkeit, die 10 € zusammenzubekommen. (Sollten alle 1,67 € geben, hat der Wirt eben 0,02 € Trinkgeld, auch kein Problem für den Wirt.)
Ich finde, daran sollten sich Buchhaltungsprogramme und ihre Benutzer orientieren. Entweder man verabschiedet sich rechtzeitig von der totalen Gleicheit und "Gerechtigkeit" aller 6 Anteile, oder wenn einem so viel an absoluter Gleichheit und Exaktheit liegt, bucht man Rundungsdifferenzen und andere rechnerisch bedingte Abweichungen bewusst als "Sonderposten".
Für Deine Arbeit in Excel bedeutet das, dass Du bei jeder Division prüfen müsstest, ob das Ergebnis einen ganzzahligen Cent-Anteil ergibt. Für den Fall, dass dies nicht so ist, musst Du Dir was einfallen lassen, wie Du den "Fehler" möglichst gar nicht erst in die Berechnungen eingibst, denn er setzt sich nun mal fort und verschwindet nur ganz selten wieder von allein. (Wenn sich mehrere Cent-Bruchteile zufällig zu einem vollen Cent ergänzen oder aufheben.)
Gruß,
CaroS
Zitat:
Die eingehenden Rechnungen werden anteilig auf die Klienten abgerechnet.
Die eingehenden Rechnungen werden anteilig auf die Klienten abgerechnet.
Das ist das Problem! Es entsteht, weil es an diesem Punkt nun mal einen Unterschied zwischen Theorie und Praxis gibt. Theoretisch kannst Du jeder Deiner 6 Parteien "anteilig" genau ein Sechstel von 10 € in Rechnung stellen. Nur praktisch geht das eben nicht, da 10/6 eine rationale Zahl in Form eines unendlichen Bruchs ergibt.
Jeder endliche Dezimalbruch enthält aber unvermeidlich einen Fehler gegenüber dem exakten theoretischen Wert. Das ist kein Problem von Excel oder einem anderen Rechenprogramm und kann deshalb auch nicht im Rahmen irgendeines praktischen Programms gelöst werden, jedenfalls nicht unter der Voraussetzung, dass alle Bedingungen (gleich große Anteile, exakte Summe) gleichzeitig erfüllt werden.
Wenn das Problem in der Praxis gelöst werden soll - für eine statistische Aussage ist dies sicherlich nicht nötig - , wird sich die Buchhaltung auf die Praxis zubewegen und Abstriche an der "Theorie" machen müssen, anders geht es nicht. Eine Buchhaltung, die dazu nicht in der Lage ist, ist keine besonders gute. (Sie ist etwa genauso nützlich wie die Aussage "An jedem Tag waren 1453,33 Leute im Museum" - was theoretisch absolut richtig sein kann, man glaubt es aber erst, nachdem man in Gedanken eine "kleine Korrektur" vorgenommen hat.)
Wenn 6 Leute Geld zusammenlegen, um eine gemeinsame Zeche von genau 10 € zu zahlen, und die Anteile möglichst gerecht, aber natürlich nicht gleich groß sein sollen, weil das ja praktisch nicht geht, dann werden am Ende wohl 4 Leute 1,66 € und zwei Leute 1,67 € dazugeben. So läuft es unter vernünftigen Menschen. Da es keine Zwei-Drittel-Cent-Münzen gibt, ist dies die einzige und gerechteste Möglichkeit, die 10 € zusammenzubekommen. (Sollten alle 1,67 € geben, hat der Wirt eben 0,02 € Trinkgeld, auch kein Problem für den Wirt.)
Ich finde, daran sollten sich Buchhaltungsprogramme und ihre Benutzer orientieren. Entweder man verabschiedet sich rechtzeitig von der totalen Gleicheit und "Gerechtigkeit" aller 6 Anteile, oder wenn einem so viel an absoluter Gleichheit und Exaktheit liegt, bucht man Rundungsdifferenzen und andere rechnerisch bedingte Abweichungen bewusst als "Sonderposten".
Für Deine Arbeit in Excel bedeutet das, dass Du bei jeder Division prüfen müsstest, ob das Ergebnis einen ganzzahligen Cent-Anteil ergibt. Für den Fall, dass dies nicht so ist, musst Du Dir was einfallen lassen, wie Du den "Fehler" möglichst gar nicht erst in die Berechnungen eingibst, denn er setzt sich nun mal fort und verschwindet nur ganz selten wieder von allein. (Wenn sich mehrere Cent-Bruchteile zufällig zu einem vollen Cent ergänzen oder aufheben.)
Gruß,
CaroS
Antwort 8 von achim_13086
Hallo Christian,
warum die Fähigkeiten von Excel nicht nutzen?
Bei deinem Beispiel (10 / 6) ermittelt Excel das
Ergebnis 1,66666667 .
Den Wert mit 6 multipliziert und das Ergebnis ist 10.
Einer besonderen Formel zum Runden bedarf es dabei nicht.
Um aber beim kaufmännischen bzw. fiskalischem Rechnen möglichst genau nahe zu kommen, gehe ich
in der Regel wie folgt vor.
In Zelle C1 würde ich folgende Formel benutzen:
= RUNDEN ( A1 / B1 ; 5 )
Das Ergebnis wäre 1,66667
Soll die Spalte C addiert werden, müßte am Ende der Spalte folgende Formel stehen:
= RUNDEN ( SUMME ( C1 : C?? ) ; 2 )
Die Fragezeichen stehen für die letzte Zeile in der
Spalte C .
Erst jetzt werden zweistellige Endergebnisse ausgegeben.
Die Zahlensplaten formatiere ich grundsätzlich wie folgt:
#.##0,00;[Rot]-#.##0,00
Durch diese Formatierungen werden keine Runden vorgenommen. Lediglich die Darstellung wird gerundet
angezeigt.
Sofern Anteile zu verteilen sind, wird sich eine gewisse Ungerechtigkeit leider nicht vermeiden lassen, wenn ein fester Betrag zur Verfügung steht.
Innerhalb einer Buchhaltung ist das Runden auf 5 Stellen hinter dem Komma erlaubt.
Ich hoofe, ich konnte mit meinem Beitrag zur Lösung des Problems beitragen.
Gruß
Joachim
warum die Fähigkeiten von Excel nicht nutzen?
Bei deinem Beispiel (10 / 6) ermittelt Excel das
Ergebnis 1,66666667 .
Den Wert mit 6 multipliziert und das Ergebnis ist 10.
Einer besonderen Formel zum Runden bedarf es dabei nicht.
Um aber beim kaufmännischen bzw. fiskalischem Rechnen möglichst genau nahe zu kommen, gehe ich
in der Regel wie folgt vor.
In Zelle C1 würde ich folgende Formel benutzen:
= RUNDEN ( A1 / B1 ; 5 )
Das Ergebnis wäre 1,66667
Soll die Spalte C addiert werden, müßte am Ende der Spalte folgende Formel stehen:
= RUNDEN ( SUMME ( C1 : C?? ) ; 2 )
Die Fragezeichen stehen für die letzte Zeile in der
Spalte C .
Erst jetzt werden zweistellige Endergebnisse ausgegeben.
Die Zahlensplaten formatiere ich grundsätzlich wie folgt:
#.##0,00;[Rot]-#.##0,00
Durch diese Formatierungen werden keine Runden vorgenommen. Lediglich die Darstellung wird gerundet
angezeigt.
Sofern Anteile zu verteilen sind, wird sich eine gewisse Ungerechtigkeit leider nicht vermeiden lassen, wenn ein fester Betrag zur Verfügung steht.
Innerhalb einer Buchhaltung ist das Runden auf 5 Stellen hinter dem Komma erlaubt.
Ich hoofe, ich konnte mit meinem Beitrag zur Lösung des Problems beitragen.
Gruß
Joachim
Antwort 9 von Kenshinsai
Danke fuer die vielen Ratschlaege. Hab das jetzt so geloest:
Ich hab jetzt einfach die Differenz zwischen dem zu verteilenden Betrag und der Summe der gerundeten Teilbetraege Darstellen lassen. Diese Differenz wird dann bei einem der Klienten verrechnet (d.h. addiert oder abgezogen). Am bewaehrten Beispiel:
zu verteilender Betrag: 10€
Parteien: 6
Teilbetraege: 1,67€
Summe: 10,02€
Differenz: 0,02€
daraus folgt:
Neue Teilbetraege:
Parteien 1-5: 1,67€
Partei 6: 1,65€
Genau so haben die das auch vorher manuell geregelt. Damit hab ich das jetzt genau so, wie ich es wollte. Danke fuer die Inspirationen!
Christian
Ich hab jetzt einfach die Differenz zwischen dem zu verteilenden Betrag und der Summe der gerundeten Teilbetraege Darstellen lassen. Diese Differenz wird dann bei einem der Klienten verrechnet (d.h. addiert oder abgezogen). Am bewaehrten Beispiel:
zu verteilender Betrag: 10€
Parteien: 6
Teilbetraege: 1,67€
Summe: 10,02€
Differenz: 0,02€
daraus folgt:
Neue Teilbetraege:
Parteien 1-5: 1,67€
Partei 6: 1,65€
Genau so haben die das auch vorher manuell geregelt. Damit hab ich das jetzt genau so, wie ich es wollte. Danke fuer die Inspirationen!
Christian
Antwort 10 von Primut
Hi Christian,
ok, ich denke, du hast mitbekommen, das es sich nicht um ein Excel - Problem sondern um ein buchhalterisches Problem handelt.
Wenn dein Buchhaltungsprogram solche Möglichkeiten nicht anbietet und du mit Excel rechnen willst (bzw. mußt), muß man sich tatsächlich eine Strategie ausdenken.
Ich denke, es wäre buchhalterisch am richtigsten, wie schon CaroS erwähnte, zu den 6 gleichen Buchungen eine Zusatzbuchung als "Rundungsfehlbetrag" auf ein "Sonderpostenkonto" o.ä. sozusagen als Ausgleichsbuchung vorzunehmen. Wenn du viele derartige Buchungen hast, dürfte der Saldo des Sonderkostenkontos gegen Null laufen, da die "Rundungsfehlbeträge" sowohl positiv als auch negativ sein können.
Wie ich schon in AW2 erwähnte, rechnet Excel aber nicht mit den angezeigten Werten, daher funktioniert AW5 auch nicht!
Wenn du mit dem angezeigten Wert in Excel weiterrechnen willst, mußt du in Excel direkt runden.
Angenommen, das Ergebnis steht in C1, dann schreibst du in D1: =Runden(C1;2)
Mit dieser Runden-Funktion kannst auch die Fehlbeträge (als Differenz zwischen Ergebnis und gerundetem Ergebnis) ausrechnen.
Gruß
ok, ich denke, du hast mitbekommen, das es sich nicht um ein Excel - Problem sondern um ein buchhalterisches Problem handelt.
Wenn dein Buchhaltungsprogram solche Möglichkeiten nicht anbietet und du mit Excel rechnen willst (bzw. mußt), muß man sich tatsächlich eine Strategie ausdenken.
Ich denke, es wäre buchhalterisch am richtigsten, wie schon CaroS erwähnte, zu den 6 gleichen Buchungen eine Zusatzbuchung als "Rundungsfehlbetrag" auf ein "Sonderpostenkonto" o.ä. sozusagen als Ausgleichsbuchung vorzunehmen. Wenn du viele derartige Buchungen hast, dürfte der Saldo des Sonderkostenkontos gegen Null laufen, da die "Rundungsfehlbeträge" sowohl positiv als auch negativ sein können.
Wie ich schon in AW2 erwähnte, rechnet Excel aber nicht mit den angezeigten Werten, daher funktioniert AW5 auch nicht!
Wenn du mit dem angezeigten Wert in Excel weiterrechnen willst, mußt du in Excel direkt runden.
Angenommen, das Ergebnis steht in C1, dann schreibst du in D1: =Runden(C1;2)
Mit dieser Runden-Funktion kannst auch die Fehlbeträge (als Differenz zwischen Ergebnis und gerundetem Ergebnis) ausrechnen.
Gruß
- Primut
Antwort 11 von Primut
@ Christian,
oh sorry, ich war zu langsam.
Klar, wenn du die Ungleichbehandlung der Parteien betriebstechnisch vertreten kannst, ist diese Lösung natürlich einfacher als eine zusätzliche Ausgleichsbuchung.
So Long
oh sorry, ich war zu langsam.
Klar, wenn du die Ungleichbehandlung der Parteien betriebstechnisch vertreten kannst, ist diese Lösung natürlich einfacher als eine zusätzliche Ausgleichsbuchung.
So Long
- Primut
Antwort 12 von woher
Hallo,
wenn Excel als Hilfsprogramm für die Buchhaltung benutzt wird, dann ist es oft sinnvoll die Genauigkeit der Berechnungen einzuschränken. Und zwar So:
Im Menü Extras auf Optionen, dort auf das Register Berechnen klicken, setze einen Haken bei "Genauigkeit wie angezeigt". Dann treten die meisten Rundungfehler nicht mehr auf.
mfg
woher
wenn Excel als Hilfsprogramm für die Buchhaltung benutzt wird, dann ist es oft sinnvoll die Genauigkeit der Berechnungen einzuschränken. Und zwar So:
Im Menü Extras auf Optionen, dort auf das Register Berechnen klicken, setze einen Haken bei "Genauigkeit wie angezeigt". Dann treten die meisten Rundungfehler nicht mehr auf.
mfg
woher