SOLVED

Simple formula shows the decimal value (1,0995-1 = 0.0994999... )

%3CLINGO-SUB%20id%3D%22lingo-sub-2173843%22%20slang%3D%22en-US%22%3ESimple%20formula%20shows%20the%20decimal%20value%20(1%2C0995-1%20%3D%200.0994999...%20)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%20encountered%20incomprehensible%20behavior%20with%20simple%20calculation%20in%20Excel.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20visibility%20for%20the%20cell%20you%20need%20to%20choose%20a%20numerical%20format%20with%20a%20discharge%20of%2020.%26nbsp%3BThe%20formula%20looks%20like%20this%3A%20%3CSTRONG%3E%3D1%2C0995-1%26nbsp%3B%3C%2FSTRONG%3EThe%20result%20is%3A%200.09949999999999999999999999999999999999999%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20the%20cell%20format%20is%20chosen%20as%20the%20same%2C%20then%20the%20formula%201.0995-1%20will%20show%200.0995.%20But%20if%20this%20result%20is%20copied%20and%20pasted%20into%20another%20cell%20as%20a%20number%2C%20it%20will%20still%20be%200.099499999999999999999999999%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EOffice%202013.%20The%20problem%20also%20manifests%20itself%20in%202007%2C%202016!%20It%20happens%20in%20any%20cell%20and%20in%20any%20book%20and%20on%20different%20PC%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2173843%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2173861%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20formula%20shows%20the%20decimal%20value%20(1%2C0995-1%20%3D%200.0994999...%20)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981818%22%20target%3D%22_blank%22%3E%40i_klemeshov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EFloating-point%20arithmetic%20may%20give%20inaccurate%20result%20in%20Excel%20-%20Office%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, encountered incomprehensible behavior with simple calculation in Excel.

For visibility for the cell you need to choose a numerical format with a discharge of 20. The formula looks like this: =1,0995-1 The result is: 0.09949999999999999999999999999999999999999

If the cell format is chosen as the same, then the formula 1.0995-1 will show 0.0995. But if this result is copied and pasted into another cell as a number, it will still be 0.099499999999999999999999999

Office 2013. The problem also manifests itself in 2007, 2016! It happens in any cell and in any book and on different PC

3 Replies
Thank you for the link! I don't understand if this behavior is a bug or not?
best response confirmed by i_klemeshov (New Contributor)
Solution

@i_klemeshov , that's a nature of binary calculations. Same way or another we have that in any software, another story how concrete application handles floating point errors.

 

Consider that as a bug or as a feature - up to you. That's just what we have to know about Excel, the thing which always will work such way. As well as year 1900 will always be considered in Excel as leap one.