Wellicht op het eerste gezicht een vreemde titel van dit artikel. Maakt Excel dan rekenfouten? Jazeker, Excel maakt rekenfouten en ze worden meestal niet eens aangemerkt als bugs. Volgens de specificatie rekent Excel met een nauwkeurigheid van 15 getallen. Dat zijn het aantal getallen voor en na de komma bij elkaar opgeteld. Dus te verwachten is dat als met getallen gewerkt wordt in de buurt van dit aantal, dat er dan afrondingsverschillen zullen ontstaan. Dat is dus niet vreemd maar wat wél vreemd is dat in sommige gevallen al veel eerder rare afrondingsverschillen ontstaan. In dit artikel komen een aantal voorbeelden aan de orde van deze ‘rekenfouten’ inclusief een stuk uitleg over oorzaak, gevolg en wat eraan gedaan kan worden.
Voorbeelden
Hier volgen er een aantal voorbeelden van deze afrondingsproblemen. Deze voorbeelden zijn getest in een aantal Excel-versies maar komen waarschijnlijk in (vrijwel) alle Excel-versies voor.
In onderstaand Excel-sheet is in A1 het getal -1,23 ingevoerd en in A2 het getal 1,12. Vervolgens worden deze getallen opgeteld. Het resultaat is -0,11. Als daar weer 0,11 wordt opgeteld dan is het resultaat niet exact gelijk aan 0, zoals te zien is in A5.
Een ander vergelijkbaar voorbeeld. Voer in A1 tot en met A3 de respectievelijk de getallen 28,552, 27,399 en 26,246 in. En zet in kolom B de formules zoals die zijn aangegeven in kolom C. Ook in dit geval is het verschil niet exact gelijk aan 0.
Of voer eens in een willekeurige cel het getal 7745,11319561 in. Te zien is dat Excel er dan automatisch van maakt 7745,113195609990. Om dat te kunnen zien moet wel het aantal weergegeven decimalen worden aangepast. Een ander voorbeeld is om in A1 het getal 0,1 in te voeren. Zet vervolgens in A2 de formule =A1+A$1 en kopieer deze formule minimaal 60 rijen naar beneden. Kijk nu in in cel A60 en zet het aantal weer te geven decimalen op minimaal 14. Normaal gesproken zou de uitkomst van de berekening 6 moeten zijn, maar het is in werkelijkheid 5,99999999999999. Nog een paar laatste voorbeelden die niet exact de juiste uitkomst opleveren:
=7,11-7,1
=7,11-7,01
=1*(0,5-0,4-0,1)
=43,1-43.2
Oorzaak
Deze rekenfouten worden veroorzaakt door afronding, afkappen van decimalen en de wijze van opslag van getallen. Dit is overigens niet een specifiek probleem van Excel, maar dat geldt voor veel, zo niet alle spreadsheetprogramma’s. Met name de wijze van opslag van getallen kan soms rare onverwachte afrondingsverschillen tot gevolg hebben. De oorzaak van deze rekenfouten heeft te maken met de wijze waarop Excel getallen intern binair opslaat. Het komt er in grote lijnen op neer dat getallen achter de komma binair worden opgeslagen als een optelling van breuken (12)n, waarbij n het positienummer is. De eerste positie achter de komma staat dus voor het getal 12, de tweede positie voor 14, de derde voor 18 enz. In binaire notatie betekent een 0 op een positie dat het betreffende getal niet aanwezig is en een 1 betekent dat dit getal wél aanwezig is. Dus het getal 0,5 wordt binair weergegeven als 0,1, het getal 0,25 als 0,01 en het getal 0,75 als 0,11 (=0,5 + 0,25).
Net zoals in het decimale stelsel bijvoorbeeld een breuk als 16 niet exact kan worden weergegeven, omdat de zessen in de uitkomst 0,1666 oneindig herhaald worden, zijn er ook decimale getallen die binair niet exact kunnen worden weergegeven, zoals 110. Decimaal kan dit getal exact worden weergegeven met 0,1 maar binair wordt dit weergegeven als 0,0001100110011… Dit is een reeks die niet eindig is. Het komt er dus op neer dat het decimale getal 0,1 niet exact kan worden weergegeven door een optelling van breuken (12)n. Hierdoor ontstaan kleine verschillen in cijfers ver achter de komma. Normaal gesproken is het moeilijk te voorspellen wanneer zich dit probleem voordoet.
Gevolg
Maar wat zijn nu de gevolgen van deze fouten? Tenzij met getallen wordt gewerkt in de buurt van de precisie van Excel (15 karakters), zullen de gevolgen normaal gesproken niet zo groot zijn, want het gaat maar om hele kleine verschillen. Maar waar dit probleem wel parten kan spelen is bij vergelijk- en zoekfuncties zoals VERT.ZOEKEN, ALS en VERGELIJKEN. Dan geven deze functies een foutmelding of een verkeerd resultaat omdat de zoekwaarde niet gevonden kan worden. Er wordt bijvoorbeeld gezocht op het getal 78 en er bestaat een getal 78 en toch wordt een foutmelding gegeven dat de waarde niet gevonden kan kan worden. Dit komt dan omdat er geen exacte match is omdat door geschetste problemen het getal 78 geen 78 blijkt te zijn maar bijvoorbeeld 78,0000000000045.
Remedie
De oplossing van deze fouten is eigenlijk betrekkelijk simpel. Het is gewoon een kwestie van het afronden van het betreffende getal met de functie AFRONDEN(getal;aantal decimalen). Let wel, het gaat hierbij om de functie AFRONDEN en niet om het aantal decimalen dat ingesteld kan worden bij de celeigenschappen. Bij de celeigenschappen wordt namelijk het onderliggende getal niet veranderd, maar alleen de opmaak van het getal. Het gaat hierbij dus om een optische verandering, terwijl bij de functie AFRONDEN het getal wél wordt veranderd.
Voor de volledigheid hierbij nog een andere rigoureuze methode die nog wel eens genoemd wordt om dit te verhelpen. Dit kan door bij de menukeuze Bestand→Opties→Geavanceerd een vinkje te plaatsen bij ‘Precisie zoals weergegeven’ (er zijn Excel-versies waar deze keuze op een andere plek is te vinden). Hierdoor worden alle getallen afgerond zoals weergegeven op het werkblad. Let op: dit kan niet meer ongedaan gemaakt worden. Het advies is om hier zich voorzichtig mee om te gaan, want aan de ene kant is hierdoor misschien het probleem met de binaire rekenfouten verdwenen, maar aan de andere kant ontstaan er weer rekenfouten omdat alle getallen in het werkblad hun nauwkeurigheid verliezen. Advies: deze optie alleen gebruiken als de gevolgen bekend en beheersbaar zijn.
Vragen / suggesties
Hopelijk heeft dit artikel geholpen bij het omgaan met rekenfouten van Excel. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.
