8 berichten aan het bekijken - 1 tot 8 (van in totaal 8)
  • Q:
    Bijdrager
    swami

    ms excel 2004 afrond-fout?

    Tot mijn grote verbazing stuitte ik op de volgende afrondingsfout in excel
    startgetal -0,2. Tel bij dit getal steeds 0,01 op. (=vorige cel + 0,01). dus:
    A
    1 -0,2
    2 =A1+0,01
    3 etc

    En ziedaar, waar 0 zou moeten uitkomen komt een getal tot de -17e macht uit. Dit zijn fouten die grover zijn dan die de originele pentium met rekenfout zou maken. Kunnen meerdere mensen deze fout reproduceren? En wie weet waar het vandaan zou kunnen komen?

    Bijdrager
    Yaris

    Ik heb op het werk ook een Excel-spreadsheet (onder Windows) met bedragen van twee cijfers na de komma. Het totaal van alle bedragen per lijn zou 0 moeten geven. Er zijn ongeveer 1.000 lijnen. Voor alle lijnen krijg ik inderdaad 0, met uitzondering van één lijn waar ik
    -0,000000000001973 of zoiets krijg. Ik heb de bedragen op die lijn al herhaaldelijk opnieuw ingetikt, steeds met slechts 2 cijfers na de komma, en toch blijf ik iedere keer dat stom eindtotaal krijgen.

    Bijdrager
    First Edit

    Weet niet waar de fout vandaan komt, maar mijn Macje denkt ook dat -0.01 + 0.01 een uitkomst heeft van 3.1225e-17

    (excel 2004)

    Ben wel nieuwsgierig of er een wiskundig genie is dit dit fenomeen kan verklaren!

    Bijdrager
    JZ

    Wat interessant!

    In Excel2004 is het idd:
    0,2000000000000000000000000000 – 0,01 =
    0,1900000000000000000000000000 – 0,01 =
    0,1800000000000000000000000000 etcetera t/m
    0,0200000000000000000000000000 – 0,01 =
    0,0099999999999999700000000000 – 0,01 =
    -0,0000000000000000312250225676!!!

    En in NeoOffice is het:
    0,03000000000000000000
    0,02000000000000000000
    0,00999999999999997000
    0,00000000000000000000???

    Nog gekker dus.

    Bijdrager
    iJoost

    Computers gebruiken nu eenmaal soms/vaak een representatie van getallen die niet helemaal exact is. Alleen de eerste zoveel getallen zijn dan betrouwbaar. En je moet dus als je dergelijk getallen gebruikt eigenlijk altijd afronden op het gewenste aantal cijfers. En ook moet je bijzonder oppassen als je twee van dergelijke getallen met elkaar vergelijkt (zijn ze ongelijk of toch bijna gelijk).

    Het grote voordeel van die representaties is dat je er ook mee kunt rekenen met heel grote en met heel kleine getallen zonder dat je hoeft af te vragen of het allemaal nog wel “past”. Maar dan dus wel altijd met een beperkte nauwkeurigheid. Dat soort getallen heten floating point getallen. En het vervelende is dat zelfs sommige getallen waarvan je dat wel zou verwachten, zoals 0,01 blijkbaar, niet exact gerepresenteerd kunnen worden en dan helaas dit soort verrassende effecten geven.

    In dit voorbeeld is 3.1225e-17 is natuurlijk bijna 0. Zelfs als je het afrondt op 15 cijfers achter de komma is het nog steeds hardstikke nul.

    Kortom altijd bedenken wat de nauwkeurigheid van je berekening is en daarna zodanig afronden dat alleen de significante cijfers overblijven. Dan kan er ook bij dit soort inexacte representaties niets mis gaan.

    (Eigenijk is het net als met JPEGs. Ze zijn lossy maar als je weet wat je doet is er niets aan de hand.)

    Meer informatie:

    http://en.wikipedia.org/wiki/Floating_point

    Bijdrager
    JZ

    Hetzelfde effect treedt ook op in CAD-programma’s.
    Toen ik daar pas mee werkte als architect stoorde het me mateloos dat er ergens ver achter de komma onregelmatigheden ontstonden. Inmiddels weten we beter en zijn we tevreden zolang de afwijking in de ‘werkelijkheid’ niet meetbaar is, d.w.z. niet meer dan 0,1mm bedraagt.

    Bijdrager
    iJoost

    Je moet je ook goed realiseren dat het wel exact kan maar dat dat dan weer als gevolg heeft dat je je als gebruiker veel meer bewust zou moeten zijn van de effecten van je berekeningen op de nauwkeurigheid van het resultaat.

    Stel je spreadsheet kan exact rekenen met getallen van 4 cijfers voor de komma en 2 cijfer na de komma. Dus van -9999,99 tot 9999,99.

    Stel dat je daarmee wilt uitrekenen wat 9% van het getal 1234,56 is.
    Het juiste antwoord is 111,1104.

    Dat doe je dan door eerst met 9 te vermenigvuldigen en daarna door 100 te delen?

    9 x 1234,56 = 11111,04 Ehm… AU! Dat is meer dan 9999,99 dus dat lukt zo niet.:-(

    Mmm… Eerst delen door 100 en dan vermenigvuldigen met 9 misschien?

    1234,56 / 100 = 12,3456 Ehm… die laatste twee cijfers passen niet dus als ie het heel netjes doet (afronden in plaats van afkappen) dan wordt dat 12,35

    9 x 12,35 = 111,15 Mmm… dat laatste cijfer is duidelijk niet meer betrouwbaar. Afgerond op twee cijfers na de komma zou er 111,11 uit de berekening moeten komen. Blijkbaar ben ik door de volgorde van berekenen toch wat nauwkeurigheid verloren.:-(

    En dat is dus precies het grote voordeel van floating point representaties. Dat je als gebruiker gewoon dergelijke berekeningen kunt maken zonder gevaar voor verlies van nauwkeurigheid. Helaas is de prijs die je voor dat gemak betaalt dat diezelfde nauwkeurigheid niet onbeperkt is. Het is een dus beperkte maar constante nauwkeurigheid.

    En jawel ik weet dat er systemen zijn die wel exact kunnen rekenen met een onbeperkte nauwkeurigheid. Maar die zitten niet in je spreadsheet. En wees maar blij dat van de twee “kwaden” deze in je spreadsheet zit. Want daardoor hoef je alleen maar rekening te houden met een mogelijke kleine afwijking in het resultaat.

    Even afronden op het gewenste aantal cijfers achter de komma en klaar.;-)

    Bijdrager
    swami

    Hallo Allemaal,

    Hartelijk dank voor de snelle en uitvoerige reacties. Uiteraard moet je je goed realiseren wat de nauwkeurigheid van je berekeningen is. Werken met significante getallen en het instellen van een afronding op het gewenste aantal decimalen zou het probleem uiteraard praktisch opgelost hebben.

8 berichten aan het bekijken - 1 tot 8 (van in totaal 8)

Je moet ingelogd zijn om een reactie op dit onderwerp te kunnen geven.