Bizar afrondingsprobleem Excel

[Ralbers]

Hallo allemaal,

Ik heb een bizar raar afrondingsprobleem in excel

Ik heb een zeer grote tabel die op een bepaalde manier gesorteerd moet worden om vervolgens een berekening op de gelijkwaardige regels uit te voeren
Dat werkte perfect

Totdat 1 regel een fout begon te geven
Deze werd namelijk ineens tot diep achter de komma afgerond
er werd van 7745,11319561 ineens 7745,11319560999 gemaakt
Dit is zelfs met diverse vormen van de formule afronden niet op te lossen

Het bizarre is dat dit alleen bij deze getallen combinatie fout gaat
Deze formule word namelijk iedere week op vele tientallen regels uitgevoerd maar het is nog nooit fout gegaan

Ook als ik het getal gewoon intik lukt het mij niet om de correcte waarde te geven

Ik heb ook verschillende versies van excel geprobeerd dit geeft echter ook de verkeerde waarde
In de bijlage een voorbeeld
groene cel is als tekst opgeslagen oranje cellen gaan fout

Heeft iemand ervaring met dit probleem?

Bekijk bijlage Afrondings probleem.xlsx

 

[wampier]

Ik kan het niet reproduceren. bij mij zijn groene en oranje cellen exact gelijk. Er komt bij mij exact 0,0000000000000 en 1,0000000000 als antwoorden uit.

 

[gelens]

Is apart.
Wampier krijgt geen verschil, maar ik wel.

Ik werk met Excel 2007

 

[Ralbers]

Ik werk met Excel 2010

Ik krijg het al als ik een nieuw werkblad open
en daar =7745,11319561 intik

 

[wampier]

In mijn 32-bit installatie krijg ik het probleem wel in 2007. Lijkt me dus bugje voor microsoft

 

[popipipo]

Je zit tegen of over de grens van het kunnen van excel
excel kan 15 decimalen aan.

 

[dnties]

Ik bevestig het verhaal van Ralbers en Wampier.

Als ik (bijv.) =7745,11319562
intype, dan blijft het getal wel in deze vorm gehandhaafd (maakt niet uit hoeveel cijfers achter de komma ik ook aangeef).

Zie bijlage, waar de resultaten van beide getallen in te zien zijn in Excel 2010, 32-bit (beide cellen als getal met 17 cijfers achter de komma opgemaakt.)

Tijs.

 

[Ralbers]

Je zit tegen of over de grens van het kunnen van excel
excel kan 15 decimalen aan.

Maar het zijn er geen 15 het zijn er 8

en als correctie maakt hij er juist een langer getal van
en ik heb nog honderden regels meer die wel goed gaan en die precies even lang zijn

Ik kan natuurlijk wel een omweg verzinnen. Maar ik vond het een bizar probleem wat niet zo zou moeten

 

[Cobbe]

Ik zou alle cellen met zogenaamde getallen eens omzetten naar standaard en daarna naar getal.

Het zou eens kunnen werken.

 

[dnties]

@cobbe: het afrondingsprobleem begint al als de cel gewoon (nog) op Standaard staat.... Zelf getest/geconstateerd.

Verder: Het maakt niet uit of je begint met Getal formattering of Standaard of andersom: Ik constateer in alle scenario's dezelfde afronding.

Tijs.

 

[Ralbers]

Helaas geen resultaat
Als ik de decimalen achter de komma weer weergeef krijg ik alsnog hetzelfde probleem

 

[popipipo]

Decimalen was misschien niet het juiste woord karakters/cijfers is beter:
7745,11319561

Dit zijn nl 16 karakters/cijfers

 

[dnties]

@popipipo: Prima, maar zie posting #7, waar =7745,11319562 het gedrag niet vertoond, terwijl dat alleen een 2 heeft ipv. een 1 op het einde, dus dezelfde getallengte, cijfers achter de komma etc.

Ik vermoed hier een bug, net zoals Wampier in posting #5.

Tijs.

 

[Ralbers]

@popipipo: Prima, maar zie posting #7, waar =7745,11319562 het gedrag niet vertoond, terwijl dat alleen een 2 heeft ipv. een 1 op het einde, dus dezelfde getallengte, cijfers achter de komma etc.

Ik vermoed hier een bug, net zoals Wampier in posting #5.

Tijs.

Inderdaad het gaat om heel veel regels
7745,11319562
7745,11319561
7745,11319571

Dit voegt een unieke code toe
Dus er zijn heel wat combinaties mogelijk
Alleen bij dit getal gaat het fout
Maargoed waarschijnlijk is het opgelost met het verplaatsen van de komma
Even een vervelend klusje omdat dan ook alle rijen die er afhankelijk van zijn aangepast moeten worden
Maar komt goed

Als iemand meer weet over dit mystery probleem hoor ik het graag

 

[pixcel]

Het is geen bug maar by design. Doe bijv het volgende testje:
in A1: 0,1
in A2: =A1+$A$1
kopieeer deze formule een paar honderd regels naar beneden, en maak de kolom op met zo'n 20 decimalen.
Als het goed is verschijnt vanaf rij 60 je bug.

Dit is het gevolg van het binair opslaan van getallen. Zoals 1/3 alleen als een repeterend decimaal getal kan worden opgeslagen, zo kan decimaal 0,1 alleen als repeterend binair getal worden opgeslagen. In beide gevallen krijg je na een x-aantal bewerkingen conversie/afrondingsverschillen.
Normaal gesproken zul je hier nooit tegen aan lopen. Maar als je de grenzen opzoekt is het wel handig om je van deze beperking bewust te zijn. Die geldt niet alleen voor Excel. Bij Excel 64 bits zal er een andere beperking zijn.

Een oplossing: vervang de formule door =afronden(A1+$A$1; 2)

 

[Ralbers]

mm ok interessant

conclusie enige oplossing is dus in mijn geval komma verplaatsen
aangezien mijn getal simpelweg te lang is vervangt hij het na het afronden simpelweg weer in de "bug"

Bedankt voor de reacties

Ik zet topic op opgelost

 

[popipipo]

Je zit tegen of over de grens van het kunnen van excel

Het is geen bug maar by design

Dit is nu wat ik ook bedoelde maar iets beter verwoord en uitgelegd.