Excel Kontierungsformular Grundbesitzabgabenbescheid runden

I

icerocks

Cadet 4th Year

Registriert

Jan. 2009

Beiträge

107

• 23. Februar 2017

• #1

Hallo liebes Forum,

Im Grundbesitzabgabenbescheid werden 5 Kostenpositionen festgesetzt, die Summe wird auf 4 Raten aufgeteilt, wobei die ersten 3 gleich sind und die Schlussrate eben abweichen kann. Die Schlussrate darf also maximal 3 Cent abweichen, sonst würde es ja aufgehen.
Mein Problem:
Ich muss die Kosten noch unterverteilen nach 5 Kostenarten und 5 verschiedene Kostenstellen. Ich bekomme das mit abrunden nicht hin.
ersten 3 Raten Betrag steht in A1: =ABRUNDEN(A1/4;2)
errechnet die ersten 3 Raten (kommt in A2)
Schlussrate: =A1-A2*3
Habe dann eben eine Abweichung von 5 Cent, verdammt.

Habt Ihr ne Idee?

 

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 23. Februar 2017

• #2

Stell mal Deine xlsx hier rein ...

 

stummerwinter

Admiral

Registriert

Feb. 2004

Beiträge

8.847

• 23. Februar 2017

• #3

Ich habe das Problem zwar noch nicht ganz verstanden, vermute aber mal, dass durch das Abrunden der Fehler kommt...

Wenn Du die Beträge nicht rundest und nur auf zwei Nachkommastellen formatierst und erst am Schluss rundest, könnte das Problem weg fallen...oder gar nicht runden sondern nur formatieren...

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 23. Februar 2017

• #4

Hier die Datei

 

Anhänge

• Kontierung Test.xlsx

  14,8 KB · Aufrufe: 264

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 23. Februar 2017

• #5

Ich gehe mal davon aus, dass Dich die 5 Cent in Deiner H15 stören.

Das kommt daher, weil Du im Bereich E10:G14 ausschließlich abrundest. D.h. Du schneidest 15x bis zu 0,99 Cent ab. Damit können logischerweise auch mehr als 4 Cent Differenz zum ursprünglichen Betrag vor Aufteilung zustande kommen.

Der Hinweis von stummerwinter mit dem Formatieren ohne Runden ist falsch. So wird die Diffrenz nur noch undurchsichtiger.

Im Übrigen solltest Du auch in E18:I25 Deine Formeln in ein RUNDEN(...;2) einstellen.

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 23. Februar 2017

• #6

Hi ms007,

ja genau, du hast völlig Recht.

Die 5 Cent sind inakzeptabel. Muss die Lösung für 20 Leute mit jeweils 40 Kontierungen finden, maximale Rundungsdifferenz darf nur 1 Cent betragen, wichtig für Buchhaltung und Jahresabschlüsse.

Ich brauche eben eine Formel die nicht generell abrundet, sondern manchmal eben nicht oder manchmal die ersten 3 Raten aufrundet und die letzte Rate dann abrundet.

Verdammt, ich komme nicht dahinter.

Deinen Tip zu E18:I25 werde ich umsetzen.

Vorab VlD

 

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 23. Februar 2017

• #7

Nimm auch im Bereich E10:G14 statt ABrunden(...;2) den normalen RUNDEN(...;2).
Damit wird die Abweichung zwangsläufig kleiner.

Bei jedem einzelnen Rundungsvorgang bist Du damit ja dann unter 1 Cent. Wenn dann der gerundete Wert mehrfach addiert wird, steigt logischerweise die Gesamtdifferenz: drei gerundete Werte addieren, dreifacher Rundungseffekt als Unterschied zum ursprünglichen Gesamtbetrag, zwanzig gerundete Werte addieren, zwanzigfacher ...
Egal, ob das dem Buchhalter gefällt oder nicht ;-)

... nicht generell abrundet, sondern manchmal eben nicht oder manchmal die ersten 3 Raten aufrundet und die letzte Rate dann abrundet ...

Zum Vergrößern anklicken....

Wie soll denn die Regel dafür lauten? "Manchmal" kennt Excel nicht!
Alternative für "manchmal" wäre dann wohl nur "ibm": immer besser manuell ...

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 23. Februar 2017

• #8

ich dachte mann könnte irgendwie sowas formulieren:

Wenn Betrag glatt durch 4 teilbar= nehme Betrag auf alle 4 Raten
wenn Betrag geteilt durch 4 plus 1 Cent= der Cent auf Rate 4
wenn Betrag geteilt durch 4 plus 2 Cent = 2 Cent auf Rate 4
wenn Betrag geteilt durch 4 plus 3 Cent = 3 Cent auf Raten 1-3 und Rate 4 abrunden

dat muss doch irgendwie gehen, mhh

Grübel

VlD für Ideen

 

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 23. Februar 2017

• #9

Ja, klar, so geht's durchaus:

- gerundete Zahlen *100 (damit die Cents weg sind)
- mit REST(...;4) die Restklassen mod 4 bestimmen, also

WENN(REST(...;4)=0;"gleich verteilen")
WENN(REST(...;4)=1;"1 Cent auf Rate4")
...

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 23. Februar 2017

• #10

Hi ms007,
holy moly, du bist ein crack und ich doof.

Brauche klare Formel, außerdem müssen die Beträge ja eben auch auf glatte Cent-Beträge gerundet sein, also nicht Cent_beträge vorab wegrunden.

 

ohmsl

Lt. Commander

Registriert

Apr. 2007

Beiträge

1.678

• 23. Februar 2017

• #11

Wie wäre es, wenn du mit dem jeweiligen Restwert die 2. - 4. Rate/Abschlag ermittelst.
Anhang anzeigen Kontierung Test1.xlsx

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 24. Februar 2017

• #12

hi ohmsi, so gehts auch nicht. Raten 1-3 sind unterschiedlich.

@ms007 Kannst Du bitte mal die ganzen Formeln Posten oder in die Testtabelle einfügen. VlD

 

ohmsl

Lt. Commander

Registriert

Apr. 2007

Beiträge

1.678

• 24. Februar 2017

• #13

Für Termin 1- 3 für Zeile "Abfallbesetigung". Für Termin 4 lässt du es bei der Ermittlung der Restsumme ( =D11-E11-F11-G11)

=WENN(REST(D11*100;4)=3;ABRUNDEN(D11/4;2)+0,01;ABRUNDEN(D11/4;2))

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 24. Februar 2017

• #14

Heyja ohmsl vielen lieben Dank,

sieht im oberen Teil gut aus. Habe deine Formel in alle Kostenpositionen eingefügt, jetzt habe ich nur noch im unteren Teil Cent-Abweichungen, also bei der Verteilung auf die Kostenstellen. Käse

 

ohmsl

Lt. Commander

Registriert

Apr. 2007

Beiträge

1.678

• 24. Februar 2017

• #15

Wie wäre es so:

Anhang anzeigen Kontierung Test2.xlsx

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 24. Februar 2017

• #16

Sieht erst mal gut aus,

werde noch alle Formeln auf alle Pos. anwenden und noch ein paar Kontrolldateien fahren.

Auf jeden Fall schon mal grosses Danke mein Lieber.

Ergänzung (25. Februar 2017)

hi ohmsl,

nochmals herzlichen Dank. Jetzt hab ich nur noch ein Problem mit der unteren Berechnung.
Zeile 18, Spalten "E" bis "I"
Haste noch ne Idee?

 

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 25. Februar 2017

• #17

Schau noch einmal in meinen Post 5:

Im Übrigen solltest Du auch in E18:I25 Deine Formeln in ein RUNDEN(...;2) einstellen.

Zum Vergrößern anklicken....

Da hattest Du geschrieben, dass Du das machen wolltest, ist aber in dem letzten xlsx von ohmsl noch nicht mit drin ...

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 26. Februar 2017

• #18

Hi ms007,

normales runden finktionier eben nicht, habs probiert. im unteren Teil wird die Summe nicht richtig berechnet, Rundungsdifferenz bleibt bei 1 Cent.

Haste noch Bock ohmsl?

 

M

ms007

Lt. Junior Grade

Registriert

März 2011

Beiträge

404

• 26. Februar 2017

• #19

Dann stell mal Deine letzte xlsx-Version hier rein und sage, welche Zellen Dir nicht gefallen ...

 

I

icerocks

Cadet 4th Year

Ersteller dieses Themas

Registriert

Jan. 2009

Beiträge

107

• 26. Februar 2017

• #20

Ja narürlich, glaube omsl is eher auf richtigem Weg, nur ich bin panne.

Ergänzung (26. Februar 2017)

Zeile 18 "E-I"

 

Anhänge

• Kontierung_Test_3.xlsx

  77,5 KB · Aufrufe: 335