Rekenen met formules gaat (afhankelijk van deling) na x maal scheeflopen

[dnties]

Beste helpers,

Zie bijgesloten Excel sheet (gedrag gezien in Excel 2010 en Excel 2007, mogelijk ook bij andere versies van toepassing?):

Als we gaan rekenen met een doorgetrokken formule dan gaat de berekening na 18x fout lopen (soort afronding), dus in kolom S, waardoor kolom U niet resultaat 0 laat zien maar iets anders.
Rekenen we echter zónder veel formules de aftrekking door (zie B1 t/m C27) dan komt het wel goed.

Apart genoeg: Als de deling in A3 wordt gedaan door 2x de originele deling (dus delen door 40 ipv. 20), dan gaat het 2x later mis lopen met rekenen (=na 36x rekenen, kolom AK). Lijkt bijna aan elkaar gerelateerd, maar weet ik niet zeker.

Bij meerjarenplanningen lopen we tegen deze afronding aan.

Handmatig afronden kan altijd nog (of een drempelwaarde voor de afronding instellen voor bepaling of een cel "dicht genoeg" bij 0 ligt), maar ik heb dus de volgende vraag:
Kan iemand mij licht verschaffen over dit gedrag en (indien mogelijk) een manier om dit probleem te tackelen?

Alvast bedankt voor alle hulp en inzichten.

Tijs.

 

[zapatr]

Dat is een bekend probleem waarover al meer dan 20 jaar discussie aan de gang is.
Het heeft te maken met het feit dat Excel binair rekent (zoek maar eens met Google).
het probleem is op te lossen door getallen waarmee je berekeningen uitvoert, af te ronden.
Bijvoorbeeld, als je op 3 decimalen wil afronden:

=AFRONDEN(SOM(A1:A20);3)

 

[dnties]

@zapatr: Doel je op het 15-cijfers rekenen binnen Excel? Zoals in deze link besproken?
Dan had ik verwacht dat het ook scheef zou lopen in C8:C27, want het enige verschil tussen de formules is dat bij de "horizontale" formule er niet van een constante (=A2) wordt afgetrokken maar van de uitkomst van een vorige aftrekking.
Zie ook dat alle waarden in de C: kolom exact overeenkomen met de waarden in rij 4 tot T, waarbij T4 "uit de pas" gaat lopen omdat S6 "verkeerd berekend" is.

Hopelijk komen er nog reacties (van jouw kant of van anderen).

Alvast bedankt,

Tijs.

 

[zapatr]

De webpagina waarnaar je vcerwijst, gaat speciaal over vele decimalen naar de komma, maar ook bij afronden op 1 of 2 decimalen kan het probleem zich voordoen. Een eenvoudige uitleg is bv. hier te vinden:

http://www.h2o-betterwin.nl/know-how/tutorials/omgaan-met-rekenfouten-van-excel

en een wat diepgaande ruitleg van Microsoft hier:

https://support.microsoft.com/nl-nl/kb/78113

 

[dnties]

In ieder geval werkt het verhaal met =AFRONDEN goed, dus dat heb ik alvast intern kunnen communiceren.

Bedankt voor alle hulp/tips tot nu toe. Ik laat nog even de vraag open staan voor evt. andere inzichten/links/informatie.

Tijs.

 

[zapatr]

Voor de dagelijkse praktijk hoef je ook alleen maar te weten dat je met =AFRONDEN() het probleem kunt oplossen.

 

[dnties]

Ik sluit de vraag.
Mochten anderen nog toevoegingen hebben dan kunnen die altijd nog bijgeplaatst worden.

Dank voor alle input.

Tijs.

 

[snb]

In VBA:

Sub M_snb()
    ReDim sn(20)
    
    sn(0) = [A2]
    For j = 1 To UBound(sn)
       sn(j) = Round(sn(j - 1) - Round(sn(0) / 20, 3), 3)
    Next

     Cells(30, 1).Resize(, UBound(sn) + 1) = sn
End Sub

NB. het werkt op deze manier omdat afronden op 3 decimalen exact overeenkomt met het bedrag van A2/20