LinkBack URL
About LinkBacks
Hi --
For some reason, when I write =1531316.44-1500000, it returns 31316.4399999999, and not the correct answer: 31316.44.
This is making me go crazy. Is there an issue with my excel? Does it work for you? Is there a way to fix?
Thank you
Hi --
For some reason, when I write =1531316.44-1500000, it returns 31316.4399999999, and not the correct answer: 31316.44.
This is making me go crazy. Is there an issue with my excel? Does it work for you? Is there a way to fix?
Thank you
First, try expanding the decimal places to see if .44 resolves to .439. If not, then Google "Excel Floating Point Error" and you will be able to read about this, if it's what's causing your issue.
Only way for us to be really sure is to see the workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
=round(1531316.44-1500000,2)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
A simple formula =1531316.44-1500000 will not cause floating point error. Did you derive the 2 figures from other cells?
I typed the numbers in as presented above. Simple workbook, no reference cells.
Effectively, if you copy/paste '=1531316.44-1500000' do you see the same issue, or is it returning the correct result for others?
Then show us!
There are instructions at the top of the page explaining how to attach your sample workbook.
Excel 365 (Windows) 32 bit
=1531316.44-1500000
Sheet: Sheet1
Copying and pasting from your post will not tell us anything other we can copy and paste from your post ...
Last edited by AliGW; 08-01-2021 at 10:01 AM.
No issue with my Excel 2016 version. So, it is MS365 glitch, like what you mentioned.
No, there is no glitch in MS365. I tested it (see post #6).
Please find sample workbook attached
Thank you
I can replicate it in YOUR file, but I cannot replicate it in a new file of my own (attached).
Hi Ali
When I extend what you sent to 11 decimal places or more, I'm still seeing the issue.
Did you try extending to multiple decimals places as well? When I opened the file you sent it was only extended to 6 decimal places.
Thank you
Ah, yes - it does happen then.
This is a manifestation of the floating points issue, as noted earlier. It's existed for a long, long time.
Use the ROUND workaround to get round it (pardon the pun).
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thank you Ali --
I'd say it was addressed, but not necessarily 'SOLVED'.
What's the best way forward for a situation like this?
Thank you
You need to mark this as SOLVED. We have exhausted the help we can offer and identified the issue (floating point error). It is beyond the scope of THIS forum to fix issues for Microsoft, therefore we've done our bit.
I'm sorry it's not to your satisfaction, but your only recourse now is to take it up with MS.
I understand
However, out of respect for the integrity of the forum, and posterity, I don't believe this issue could accurately be considered SOLVED.
It is also unclear to me how my problem related to the floating point issue. After reading the provided material, none of the examples given explain why '=1531316.44-1500000' returns this issue but a similarly simple equation '=1531316.44-1400000' does not.
If the forum wishes to explain this, I would feel comfortable with marking as SOLVED.
Thank you for all your help
I am happy to explain this again (in different words).
We cannot solve the issue. The problem of the floating point error is one that has been around for a long time.
Your title was: "Number Not Subtracting Correctly (Glitch?)"
Having looked carefully at your situation, the forum community has identified the glitch as part of the floating point error issue.
You also asked: "Is there a way to fix it?"
Now that we have identified what the issue is, the answer is NO, but you can work around it as mentioned earlier with the ROUND function.
Therefore, by my reckoning, we have answered your questions, therefore the issue is resolved: to show this, we ask you to add the solved tag.
As I said, it is beyond the scope of THIS forum to fix the floating point issue. You will have to ask Microsoft if you want to get an explanation about why it's affecting this particular calculation.
Last edited by AliGW; 08-02-2021 at 08:27 AM.
Thank you for that rundown.
Could you please clarify how this specific situation is a floating point issue?
I believe all I've seen so far is a link to a page that doesn't address the scenario I've experienced.
I may have missed it. What explanation would you, or the forum community give?
Thanks in advance for your elucidation.
ARTICLE
Why does 1.3240 – 1.3190 = 0.0049999999999999?
https://www.microsoft.com/en-us/micr...wrong-answers/
Have you taken a moment to read the link provided in post #10 ?
Floating point error is an inherent part of the way computers do arithmetic. As long as computers have finite memory, we will experience floating point error. And it will always seem random and unpredictable.
I don't know if these will help, but here's a collection of links https://www.excelforum.com/groups/ma...nd-errors.html
Originally Posted by shg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks