Bug(s) functie(s) "afronden naar beneden", "geheel getal (met aantal decimalen)", en wellicht soortgelijke- in Excel 2016 (Office 365)

Question

Hallo,

Als boekhouder heb ik vandaag gemerkt dat bij sommige functies in Excel 2016(Office 365 , en misschien ook eerdere versies) een bug schuilt.

Deze kan ervoor zorgen dat er grote verschillen in het eindkapitaal kunnen optreden indien je, met een door deze bug gemaakte rekenfout, een ketting aan sommetjes (of vermenigvuldigingen) maakt met bijzonder veel (herhalende) bedragen over een volledig jaar.  Ook al is deze fout maar ogenschijnlijk een verschil van 0,01 EUR, geloof me ... als boekhouder weet ik dat iedere cent kan tellen!!!

Ik ga jullie mijn boekhouding niet tonen, maar puur ter voorbeeld en vereenvoudigd voorstel zie afbeelding. (de bedragen zijn louter ter voorbeeld)

Kolom A, waar er in het echt 10 tot 100x meer ('of zelfs nog meer') bedragen kunnen staan, stelt verschuldigde bedragen voor. A3 stelt in dit voorbeeld een halfjaarlijks bedrag & A4 een maandelijks bedrag voor.  In Kolom B staan de bedragen omgerekend naar "per maand", in kolom C "per halve maand".  Deze laatste genoemde, zijn de bedragen die ter afbetaling worden gedomicilieerd.  Omdat je maar minstens per 0,01 EUR kunt afrekenen/betalen, dienen deze bedragen eerst tot twee decimalen naar boven afgerond te worden (naar beneden afronden gaat niet want dan zou je niet genoeg per maand afrekenen).  Hierdoor moet er een correctie, waar volgend jaar rekening mee gehouden wordt, berekend worden ( = de som van de verschillen x 12 maanden) en dient dus ten alle tijden correct te zijn.

Ondanks het voorbeeld dat, "per toeval", [de som van de verschillen x 12 maanden] een getal tot twee decimalen is (wegens [de som van de verschillen] eindigend op [5/1000 van een euro]),  dient er voor die waarde/cel steeds een afgerond getal tot op 2 decimalen berekend te worden (voor het geval dat [de som van de verschillen x 12 maanden] geen afgerond getal is).

Nu doet zich echter het probleem voor volgens de nota's en opmerkingen in de afbeelding.

Nogmaals... 0,01 EUR is misschien niet veel. Maar indien je zo'n fout tot 100'en tot 10.000'en keren optelt, hergebruikt of vermenigvuldigd in lange rekenreeksen, zul je (enorm) grote verschillen krijgen, waarvan je niet weet van waar deze komen indien alles automatisch berekend wordt.

Met alle respect en begrip voor de makers van Excel en "hoedje af voor hen die zo'n geweldig rekenprogramma hebben gemaakt", maar deze is een bug die vast en zeker dient opgelost te worden.  Ik ben ervan overtuigd dat deze al enorm veel administratieve problemen/conflicten heeft veroorzaakt bij vele bedrijven (of particulieren die hun eigen financiën bijhouden).

Wie weet hebben ze zelf bij de overheden geen weet van dit probleem en dat ze zelf soms met de handen in het haar zitten bij "Financiën".  :-p

Gelieve een antwoord te sturen, al is het maar om te zeggen dat er effectief naar gekeken en verholpen wordt :-)

Met vriendelijke groeten,

Yannick

Answer 1

Met plezier! Het begint eigenlijk al in A.

302,79 is intern 302,79000000000002046363078989088535308837890625

Je meot eens volgende oefening proberen.

Vul tien cellen met 0,1

0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0.1
0

in de 11° cell zet je =SUM(G1:G10)-1 (of SOM).

Het antwoord lijkt perfect.

Verander nu de formule in: =SUM(G1:G10)-1-0

Je zal verbaasd zijn over het resultaat.

Answer 2

Yannick,

In aanvulling op Jan's antwoord.

Intern in de CPU wordt 0,30 voorgesteld als:

2,99999999999999.988897769753748434595763683319091796875E-1

Ik gebruik het "." om aan te geven wat Excel kan "tonen".

Elk getal wordt in een floating point omgezet. Dit is gewoon de manier waarop een computer werkt. Het probleem situeert zich bij decimale getallen omdat dit niet altijd in een exacte power van 2 kan vertaald worden.

Nu, Excel rekent niet met de 15 betekenis volle getallen maar met de volledige IEEE754 conversie.

Answer 3

Dus als ik het goed versta...

loopt het, zoals in laatste afbeelding, bij wijze van spreken al mis in kolom B/C & E waarbij die waarden al Floating point-getallen zijn volgens het IEEE754 conversie, ondanks Excel deze afgerond toont tot op 15 decimalen.  En dat je het pas merkt in de laatste kolom wanneer je het verschil berekent.

Nu begrijp ik goed waar het probleem schuilt.

Dank voor het antwoord, JP.

Answer 4

Beste Jan,

Hartelijk dank voor je antwoord.

Het feit dat deze berekende getallen in werkelijkheid #,#99... ("floating point getallen") zijn, was me niet eerder opgevallen totdat ik de nauwkeurigheid tot op minstens 13 decimalen weergaf.

Ik kan best inkomen dat Excel wegens programmatietaal deze techniek gebruikt.

Enkel vind ik het ergens niet logisch dat deze getallen niet perfect afgerond zijn... wanneer de basisgetallen (zoals in deze afbeelding; hier heb ik meerdere kolommen per stap ingesteld) allemaal afgeronde getallen zijn (tot op 15 significante cijfers).  Enkel bij F2 & F3 begint Excel plots met "Floating points" getallen te werken ???

Maar oke, dat verklaard dan ook waarom we soms problemen hebben indien we getallen met decimalen exporteren in Word-bestanden met velden, als samenvattingslijsten. (hier durven sommige bedragen te eindigen met x,x99...). :-p

Nogmaals dank voor het antwoord,

Vriendelijke groeten, Yannick

Answer 5

Yannick,

Dit 'probleem' is zeker niet onbekend en bestaat al zolang Excel bestaat en een gebruiker zal daar rekening mee moeten houden.

Aangezien Excel rekent met floating point getallen (met 15 significante cijfers) zul je afrondingen zorgvuldig moeten kiezen.

De 0,30 in D6 is in werkelijkheid 0,299999999999898

De functie Geheel kapt domweg een aantal decimalen weg en zal hier het resultaat inderdaad 0,29 zijn.

Afronden naar beneden gaat naar het dichtstbijzijnde (decimale) getal lager dan het huidige en komt dus ook uit op 0,29.

Overigens waarom zou je het controle getal naar beneden willen afronden?

Logischer lijkt mij (voor het controle getal) om af te ronden naar het dichtstbijzijnde getal, met bv Afronden:

=AFRONDEN(D6;2)

Of bijvoorbeeld in plaats van

=Geheel(D6;2)

gebruik je

=Geheel(D6+0,005;2)

Jan