Det finns ett fult beräkningsfel som har dykt upp i Excel. Det verkar som att problemet går djupt in i Excel-beräkningsmotorn och inte blir lätt att fixa.
Kärnan i problemet är ett enkelt faktum: Excel lagrar 15 siffror med precision i en cell. Du kan ha siffror som har 20 siffror, men alla siffror mellan den sista signifikanta siffran och decimalen måste vara noll.
Jag har nyligen sett två fall där Excels beräkningsmotor gav fel resultat. När jag grävde in i problemet och tittade på den underliggande XML blev jag förvånad över att Excel i hemlighet lagrade 17 siffror i XML.
Problemet är att Excel bara visar 15 siffror. Så du tror att du har ett nummer lagrat som 0.123456789012345 men det lagras verkligen som 0.12345678901234567.
Du kan inte se de två sista siffrorna. Och de flesta av Excels funktioner ignorerar de två sista siffrorna. Om * alla * funktionerna ignorerade de två sista siffrorna skulle vi inte ha något problem. Men hittills använder sortering, RANK och FREQUENCY alla 17 siffror.
Nedan följer ett välkänt trick för att rangordna celler. Om du behöver att varje rang ska visas exakt en gång kan du kombinera RANK och COUNTIF. På bilden nedan är Claire, Flo, Ivana och Lucy bundna till 115%. Med hjälp av RANK + COUNTIF-formeln bör de rankas 5, 6, 7 och 8.
Men formeln misslyckas. Två rader rankas som 7. Det händer aldrig. Fyra formler i kolumn D ser till att 115% i B6, B9, B12 och B15 är desamma. Den =B6=B15formel rapporterar att båda cellerna innehåller samma data.
När jag försökte isolera problemet, titta bara på RANK-funktionen. Det bör rapportera en 4-vägs oavgjort vid 4: e för folket med 115%. Men på något sätt är Lucy på rad 15 rankad före de andra tre.
För att räkna ut det skickade jag ut en begäran om hjälp till de andra Excel-MVP: erna. Jan Karel Pieterse sprängde upp Excel-filen och tittade i XML. I XML kan du se att de lagrar 17 siffror med precision. De fyra cellerna som ser ut som en slips i Excel är inte bundna i XML. En av de 115% lagras som 1.1500000000000001 och de andra är 1.1499999999999999.
Hittills använder extra siffror sortering, rangordning och FREKVENS-funktionen. Varför är det ett problem? Eftersom vi räknar med att RANK och COUNTIF använder samma antal siffror. Med en funktion som använder 15 siffror och den andra med 17 siffror, har du ett problem.
För närvarande verkar lösningen konvertera alla dina svar med =ROUND(A4,15).
Varje fredag undersöker jag ett fel eller annat fiskigt beteende i Excel. Detta beräkningsfel är svårt att upptäcka och kvalificerar sig som en stor fisk.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Varje gång du slår ihop celler mördar du en kattunge"
Szilvia Juhasz
