Fout bij optelling in Excel

Question

Ik heb een werkblad met 850 regels. De totalen van de euro's moeten komen op 400,85 maar Excel maakt daarvan 400,850000000009 en bij het aanbieden van een bestand met zo'n afwijking stuurt de bank je het bos in.

Iemand een idee wat hier aan de hand is?

Answer 1

Dag Kees,

Iets meer detail. Excel gebruikt de IEEE754-64 bit norm om getallen op te slaan. Elk geheel getal heeft een binaire representatie. Het zijn de decimale waarden die niet altijd exact in een binaire waarde kunnen omgezet worden. 

Zo is 0,5 intern 3FE0000000000000 en bij terugzetting naar decimaal ook 0,5.

0,7 is intern 3FE6666666666666 en bij terugzetting 

0,699999999999999.9555910790149937383830547332763671875

Exel toont 15 betekenisvolle cijfers, daarom heb ik ook een punt gezet in de vertaling.

In sommige gevallen ziet Excel de afwijking voldoende klein om dit terug af te ronden naar 0,7.

In andere gevallen wordt die afronding niet toegepast zodat je inderdaad een kleine afwijking kunt overhouden. Zoals Aalpha aangeeft, AFRONDEN is de enige oplossing.

Bij heel grote getallen, meer dan 15 cijfers, kan dit ook optreden omdat maar 15 betekenisvolle cijfers getoond worden.

De getallen in de 2° kolom zijn de ingegeven, werkelijke getallen. Zoals je ziet vervangt Excel de 13 door 00 en is er bijgevolg ook een afwijking op de som in de 3° rij.

12345678910111200,00	12345678910111213
12345678910111200,00	12345678910111213
24691357820222400,00	24691357820222426

De getallen in de 2° kolom werden als tekst ingegeven en de berekening in de 3° rij werd gedaan met een hiervoor bestemde add-in.

Answer 2

Ik heb een werkblad met 850 regels. De totalen van de euro's moeten komen op 400,85 maar Excel maakt daarvan 400,850000000009 en bij het aanbieden van een bestand met zo'n afwijking stuurt de bank je het bos in. Iemand een idee wat hier aan de hand is?

Dat heeft te maken met het feit dat Excel (en niet alleen Excel) getallen binair opslaat (als nullen en enen). Je kunt daar op internet legio webpagina's over vinden. Je kunt het probleem eenvoudig oplossen door bij berekeningen de functie AFRONDEN te gebruiken, in jouw geval afronden op twee decimalen, dus (voorbeeld): 

=AFRONDEN(jouw berekening;2)

Answer 3

Ja....dat had ik zelf ook al verzonnen maar daar hebben onze klanten maling aan. In dit geval gaat het om de SUM functie. Leg maar eens aan een klant uit dat de optellen van 850 gehele getallen geen geheel getal oplevert. 

Maar dit "...getallen binair opslaat..." kan ik wel gebruiken. Dank je wel, Aalpha.

Answer 4

pfjioeeee! Helemaal top! Dank je wel JP.