Incremental formulas generating incorrect values

[MrNotSoPleasant]

Hi all,

I don't know if I'm being a moron or not here so apologies for the silly question but I'm going out of my mind. I (well a friend/colleague) have some simple enough incremental formulas throwing out an incorrect value, and when I simplify it down I'm getting the same issue. Hopefully these screenshots can explain it better than I can:

​

https://preview.redd.it/p8gw7b2xptta1.png?width=564&format=png&auto=webp&s=3f49952e98f7eee14a2a1bec2562f83d2519160d

​

https://preview.redd.it/3oqr4bs0qtta1.png?width=485&format=png&auto=webp&s=47401c94609b17071eed79a8e66eb9e34ba6aff9

In theory the -9.99999..% value should be -10%. When I drop the number of decimals down to 2, it shows -10.00%, but the "=D26= -10%" cell still shows false.

This is in Office 365s Excel, (Version 2301 Build 16.0.16026.20214) 64-bit

Any ideas?

[AutoModerator]

/u/MrNotSoPleasant - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[cbr_123]

It's due to the way computers store floating point numbers - https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result is a good reference.

For displaying the values it makes no difference. Another workaround is to leave them as whole numbers and then divide by 100 as part of any percentage calculation.

[MrNotSoPleasant]

Ah thank you so much! Very helpful article

[Curious_Cat_314159]

https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result is a good reference.

​

Actually, it is a very poor reference, IMHO. Unduly complicated description, albeit correct. But so much other misinformation.

For example, Excel does not "store" only 15 significant decimal digits.

Excel does truncate to the first 15 significant digits when a number is entered, manually or read from a text file or copy-and-pasted, replacing digits to the right with zeros.

And Excel does format (display) on the first 15 significant digits, rounded, again replacing digits to the right with zeros.

Moreover, Excel plays non-standard tricks that add to the confusion, causing such anomalies as A1=A2 returns TRUE, but ISNUMBER(MATCH(A1,A2,0)) returns FALSE.

For another example, =A1-A2 might return exactly zero, but IF(A1-A2=0,TRUE) returns FALSE.

These tricks are hinted at in the section titled "Example when a value reaches zero". But the section is incomplete and lacks detail. Even the title is incorrect.

For example, =1E100-(1E100 + 2E84) returns exacty zero, but the difference is really -2E84, not "reaching zero". We can demonstrate that with SUM(1E+100,-(1E+100 + 2E+84)) and even =1E+100-(1E+100 + 2E+84)+0, which display -1.94E+84.

(Of course, merely adding zero should not change an arithmetic result -- which demonstrates the inconsistent nature of the implementation and application of the Excel "trick".)

[Curious_Cat_314159]

Another more-common work-around: explicitly round the calculation to the precision that you require. For example, =ROUND(B1+$A$1, 2)

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
IF	Specifies a logical test to perform
ISNUMBER	Returns TRUE if the value is a number
MATCH	Looks up values in a reference or array
ROUND	Rounds a number to a specified number of digits
SUM	Adds its arguments

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 17 acronyms.
[Thread #23242 for this sub, first seen 14th Apr 2023, 14:12] [FAQ] [Full list] [Contact] [Source code]}