Afronden in excel werkt niet

[Malt007]

Voor een rapportage moet ik gegevens invoeren en zoals wel vaker als boekhouder moet debet gelijk zijn aan credit. Aangezien uit het systeem de gegevens tot 10 cijfers achter de komma komen rond ik deze af op 2 cijfers. Echter bij controle blijkt dat er nog een verschil zit vanaf 3 cijfers achter de komma. De afgeronde cijfers als harde waardes plakken helpt ook niet. Wat doe ik fout?

[zeeg]

Je doet toch niets fout? Door de afronding klopt het dat ze niet exact aansluiten (tenzij alle debet en credit bedragen exact gelijk zijn). Een fout van x/1000 doet er toch niet toe dan?
Maar misschien begrijp ik je verkeerd?

[Malt007]

Het probleem is dat ik de gegevens vervolgens in een template moet plakken en die controleert of debet en credit gelijk zijn tot ver achter de komma. Als het niet exact gelijk is dan werkt de template niet.
Maar hoe kan er een verschil zijn vanaf 3 cijfers achter de komma als je hebt afgerond op 2 cijfers en vervolgens harde waardes hebt geplakt?

[SinergyX]

Afhankelijk welke 'afronding' je gebruikt, zal excel gewoon achter de schermen doorrekenen met de complete waardes. Als je via celeigenschappen deze gewoon op 2 decimalen hebt gezet, rekent excel rustig door met de 10 cijfers achter de komma.

Ik zou dan eerder een afronding in je controleberekening maken (afronden functie) zodat je dat daar automatisch corrigeer.

Vraag ik me af welk financieel pakket je dan gebruikt die met 10 cijfers achter de komma werkt, 99% werkt tot maximaal maar 2 cijfers.

[zeeg]

Malt007 schreef op woensdag 29 februari 2012 @ 11:26:
Het probleem is dat ik de gegevens vervolgens in een template moet plakken en die controleert of debet en credit gelijk zijn tot ver achter de komma. Als het niet exact gelijk is dan werkt de template niet.
Maar hoe kan er een verschil zijn vanaf 3 cijfers achter de komma als je hebt afgerond op 2 cijfers en vervolgens harde waardes hebt geplakt?

Dat kan niet in principe.

Als Debet 10,78895 door jouw bewerking 10,79 wordt en credit tweemaal 5,394475 5,39 wordt dan klopt door afronding de optelling natuurlijk niet meer. 2 * 5,39 =/= 10,79. Maar dat is dan altijd een fout in de honderdsten.

Maar waarom zoveel cijfers achter de komma als het om euro's gaat?

[Malt007]

Ik gebruik de functie =afronden(cel;2) dus het ligt niet aan de weergave.

De gegevens worden aangelevert door een klant, geen idee waarom zoveel cijfers achter de komma.

De template controleert of debet en credit exact gelijk zijn. Als dit niet exact gelijk is kan deze niet gebruikt worden. Het is helaas ook geen optie dit aan te passen.

[sjonie100]

Miischien gaat het om de prijs van een product dat vele malen verkocht wordt en zal daarom bij miljoenen verkoop de prijs verder achter de komma toch verschil maken...
Ben wel benieuwd waar het om gaat...

[SinergyX]

sjonie100 schreef op woensdag 29 februari 2012 @ 11:38:
Miischien gaat het om de prijs van een product dat vele malen verkocht wordt en zal daarom bij miljoenen verkoop de prijs verder achter de komma toch verschil maken...
Ben wel benieuwd waar het om gaat...

Zo'n prijs per product zal dan tot maximaal 2-3 cijfers achter de komma zijn, zelfs op miljoenen praat je nog steeds over hetzelfde percentage afwijking van 10 of 2-3 decimalen.

Maar zoals Zeeg zegt, deze verschillen zal je altijd houden. 2x 0.075 is nooit gelijk aan 0.15 als je op 2 decimalen gaat afronden. Je kan je debet/credit controle-formule toch ook gewoon aanpassen zodat hij een marge heeft van 0.10 of minder?

[Malt007]

Ik zal proberen het wat duidelijker uit te leggen. Het draait in ieder geval niet om miljoenen.

Een buitenlandse deelneming levert een export van hun administratie aan die ik moet inladen in ons systeem. Deze cijfers worden aangelevert met ongeveer 10 cijfers achter de komma, waarschijnlijk veroorzaakt door btw. Deze cijfers moet ik in een exceltemplate kopieeren en deze template wil dat debet en credit tot oneindig achter de komma gelijk is. (Template aanpassen kan niet, betreft een template die companywide wordt gebruikt bij een mulitnational)

Mijn werkwijze is dat ik de orginele gegevens afrond tot 2 cijfers achter de komma met de formule =afronden(cel,2), daarna plak ik deze als waardes waardoor het gewoon harde cijfers zijn. Voor het oog zie je dus vervolgens slechts 2 cijfers achter de komma en als ik alles optel komt er gewoon 0,00 uit.

Maar bij het plakken van de gegevens in de template blijkt dus dat excel toch doorwerkt met cijfers die je niet ziet. Er blijft namelijk een verschil wat begint bij 3 cijfers achter de komma. En dat is het probleem.

Edit: Als ik de orinele cijfers heb afgerond en hard heb geplakt en ik vervolgens tot 20 cijfers achter de komma laat zien staat er bijv: 10,01000000000000000000. Als ik deze met een sum controleer en daar laat ik 20 cijfers achter de komma zien krijg ik alsnog 10,0100000023414134513. Hoe kan dit?

[ Voor 12% gewijzigd door Malt007 op 29-02-2012 12:07 ]

[SinergyX]

Doen je dan een plakken speciaal -> waarden of andere methode?

Als ik een afronden(cel;2) namelijk kopieer en via speciaal->waarden plak, wordt het wel een absoluut 2 cijferig bedrag.

[Malt007]

SinergyX schreef op woensdag 29 februari 2012 @ 12:08:
Doen je dan een plakken speciaal -> waarden of andere methode?

Als ik een afronden(cel;2) namelijk kopieer en via speciaal->waarden plak, wordt het wel een absoluut 2 cijferig bedrag.

Ik gebruik inderdaad die methode.

Ik ga het even op een andere pc proberen. Wellicht ligt het aan de instellingen van mijn excel.

Edit: Het lijkt te liggen aan mijn excel instellingen. Met random ingevoegde waarden gebeurt bovenstaande niet. Nu nog even uitsluiten dat het niet ligt aan de aangeleverde file.

[ Voor 17% gewijzigd door Malt007 op 29-02-2012 12:13 ]

[Avar]

Anders even:
=GEHEEL(CEL*100)/100
in plaats van afronden()

[Malt007]

Bij controle op een andere pc leek het dus aan de instellingen van mijn pc te liggen. Nu heb ik op die andere pc dezelfde gegevens geopend, gekopieerd naar een nieuwe excel file en daar nog vreemdere dingen geconstateerd.

Ik heb dus de originele gegevens gekopieerd en als waarde geplakt (was geen formule, maar toch). Deze gegevens hebben alleen maar 2 cijfers achter de komma in gebruik, dus bijv: 10.0100000000000000. Als ik vervolgens al deze getallen middels een som formule optel krijg ik als antwoord: 0.000000414312. Nog even voor de duidelijkheid, geen van de individuele getallen heeft iets staan zover achter de komma.

Nou komt het mooie, ik heb al deze getallen weer met een afrondingsformule verwerkt en opgeteld en dan krijg ik een andere uitkomst dan met de originele waardes :-S

[pedorus]

10.01 is nou een slecht voorbeeld, je kunt beter 10.05 gebruiken. Maar zie artikelen als (Complete) Tutorial to Understand IEEE Floating-Point Errors en http://www.cpearson.com/excel/rounding.htm ; dit is niets bijzonders en heeft te maken met hoe de meeste programma's kommagetallen op slaan.

Edit: of op deze site: Programming FAQ - Getallen en talstelsels

[ Voor 12% gewijzigd door pedorus op 29-02-2012 13:46 ]

[Malt007]

pedorus schreef op woensdag 29 februari 2012 @ 13:44:
10.01 is nou een slecht voorbeeld, je kunt beter 10.05 gebruiken. Maar zie artikelen als (Complete) Tutorial to Understand IEEE Floating-Point Errors en http://www.cpearson.com/excel/rounding.htm ; dit is niets bijzonders en heeft te maken met hoe de meeste programma's kommagetallen op slaan.

Edit: of op deze site: Programming FAQ - Getallen en talstelsels

Ok maar hoe kan het dan dat afronden normaal wel werkt? En belangrijker, hoe los ik dit op?

[pedorus]

In plaats van =A1=A2 iets gebruiken als =ABS(A1-A2)<0.005. Of vaak genoeg tussendoor afronden, maar het is nu eenmaal niet mogelijk om elk getal te representateren.

Excel heeft helaas geen 'decimal'-datatype, maar je zou ook handmatig alle getallen keer 100 kunnen doen en daar mee werken. Dat heeft enkel ook zo zijn nadelen als iemand niet door heeft dat het om centen gaat, dus toch liever niet.

[Reptile209]

Kan je niet (in Excel) een post 'Rounding error' introduceren die het verschil opeet? Die dus als formule SOM(credit)-SOM(debet) heeft? Dan klopt het per definitie weer.

[wellproc]

Enorme topic-kick, maar ik kwam dit topic tegen en vond geen echte oplossing (afgezien van de verwijzingen naar leerzame uitleg over het fenomeen). Voor latere lezers van dit topic toch ook nog de praktische oplossing in Excel: https://support.microsoft...accurate-results-in-excel.

In het kort: ga in Excel naar Bestand > Opties > Geavanceerd.
Daar vink je de optie 'Precisie instellen als weergegeven' aan (onder kopje 'Bij het berekenen van deze werkmap'). Hiermee verander je voor de gekozen werkmap de weergave.
In mijn geval ging het om een werkblad met financiële administratie waarin ik alleen maar bedragen had staan. Ondanks dat kreeg ik op een gegeven moment toch een controlefout die neerkwam op een 0,0000000000001 verschil... (ja: het 13e getal achter de komma).

Hopelijk heeft dit nog waarde voor een lezer na mij