Er is een lelijke rekenfout opgetreden in Excel. Het lijkt erop dat het probleem diep in de Excel-rekenengine zit en niet gemakkelijk op te lossen zal zijn.
De kern van het probleem is een simpel feit: Excel slaat 15 cijfers precisie op in één cel. U kunt getallen hebben die uit 20 cijfers bestaan, maar alle cijfers tussen het laatste significante cijfer en de komma moeten nul zijn.
Ik heb onlangs twee gevallen gezien waarin de rekenmachine van Excel de verkeerde resultaten retourneerde. Toen ik me verdiepte in het probleem en naar de onderliggende XML keek, was ik verrast om te zien dat Excel stiekem 17 cijfers in de XML opsloeg.
Het probleem is dat Excel slechts 15 cijfers weergeeft. Dus je denkt dat je een nummer hebt opgeslagen als 0.123456789012345 maar het is echt opgeslagen als 0.12345678901234567.
U kunt die laatste twee cijfers niet zien. En de meeste functies van Excel negeren die laatste twee cijfers. Als * alle * functies de laatste twee cijfers negeerden, zouden we geen probleem hebben. Maar tot nu toe gebruiken sortering, RANK en FREQUENTIE alle 17 cijfers.
Hieronder staat een bekende truc voor het rangschikken van cellen. Als je wilt dat elke rang precies één keer voorkomt, kun je RANK en AANTAL.ALS combineren. In de onderstaande afbeelding staan Claire, Flo, Ivana en Lucy vast op 115%. Met behulp van de RANK + AANTAL.ALS-formule, moeten ze worden gerangschikt op 5, 6, 7 en 8.
Maar de formule mislukt. Twee rijen zijn gerangschikt als 7. Dat gebeurt nooit. Vier formules in kolom D zorgen ervoor dat de 115% in B6, B9, B12 en B15 hetzelfde zijn. De =B6=B15
formule meldt dat beide cellen dezelfde gegevens bevatten.
Kijk, terwijl ik probeerde het probleem te isoleren, alleen naar de RANK-functie. Het zou een 4-way gelijkspel op 4 moeten rapporteren voor de mensen met 115%. Maar op de een of andere manier staat Lucy in rij 15 voor op de andere drie.
Om erachter te komen, stuurde ik een verzoek om hulp naar de andere Excel MVP's. Jan Karel Pieterse kraakte het Excel-bestand open en keek in de XML. In de XML kun je zien dat ze 17 cijfers nauwkeurig opslaan. De vier cellen die in Excel op een gelijkspel lijken, zijn niet gebonden in de XML. Een van de 115% wordt opgeslagen als 1.1500000000000001 en de andere zijn 1.1499999999999999.
Tot nu toe gebruiken sorteren, rangschikken en de functie FREQUENTIE de extra cijfers. Waarom is dat een probleem? Omdat we rekenen op RANK en AANTAL.ALS om beide hetzelfde aantal cijfers te gebruiken. Met de ene functie met 15 cijfers en de andere met 17 cijfers, heb je een probleem.
Voorlopig lijkt de oplossing al uw antwoorden te converteren met =ROUND(A4,15)
.
Elke vrijdag onderzoek ik een bug of ander visachtig gedrag in Excel. Deze rekenfout is moeilijk te detecteren en kwalificeert zich als een grote vis.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Elke keer dat je cellen samenvoegt, vermoord je een kitten"
Szilvia Juhasz