Regarding your answer, Carlosdl, 346.95 is an entered number. It is not a result of any formula or anything else. The formatting for the cell does not interfere with the contents of the cell. The number was entered as 346.95.
Can you help me?
Denwood, that was my first thought too, but the final division in this case is 20/10, so, even when Whabby is using a '9' as the second parameter for the TRUNC function, the result should be 2 (exactly).
Thank you Denwood for your contribution. However, Carlosdl has already expressed my sentiments exactly on FEB 17, 2010 at 10:11PM GMT. The answer in the formula should be 2.00000, and using the "TRUNC" function with the formula result, Excel should have truncated the 0's.
This is more serious than I thought. Consider this: If you enter the following simple formula into EXCEL 2003: =(346.95-346)*100, and formatted to show 13 decimal points, you would come to a determination that the program is faulty.. EXCEL 2003 returns 94.9999999999989, when the answer should be 95.0000000000000. What can be done to correct this problem?
Interesting.
Another example:
=(127.95-127)*100
gives 95.0000000000003
But if you use the cell containing this formula to perform some calculations, the results are correct, so it seems to be just a display problem.
Take: =(127.95-127)*100
Result: 95.0000000000003
According to you, Carlosdl, the real number is 95.0000000000000 and Excel is just displaying it wrongly. Let's do a calculation, then. Take the result that Excel is returning and add 0.0000000000001. The result is 95.0000000000004. According to your theory Excel should return 95.0000000000001 but is displaying the wrong figure. Be that as it may, if it is a calculation problem with Excel or if it is a display problem with Excel, something must be done to remedy the situation.
In my excel sheet, it does show 2 in the result cell. I did it this way.
result H41 l41 a41
1.999999999 95 3 346.95
(I left the formating to all cells to excel. it shows the 1.999999999 here, but my sheet shows it as 2. Column headings are your references.)
Check your formating, and whether excel is using the decimal portion of the previous cell results in your calc. What is displayed, is not always what is used in the calculations.
I would try looking at 'INT' if you just want to use the integer portion of the cell result.
Hope this helps
I'm sorry, Chippy088, your contribution does't help. You see, the discussions of the original question have advanced beyond the original question, especially from Feb 26, 2010 onward. I invite you to read all the advanced discussions and then after you would have had a chance to evaluate what was said, then join the more advanced discussions. We welcome it.
However, on your sheet, Excel is returning 2 because you failed to format the cell to show 9 decimal places. You are not showing enough decimal places.
Furthermore, the original problem arose because Excel is using what is not displayed (through formatting) to perform calculations. And I contend that what is not displayed is erroneous.
Additionally, using the INTEGER (INT) function is no help because I contend that there is a calculation problem with EXCEL, not a display problem or a function problem or anything else (yet), but a fundamental calculation problem.
Ok, if you do not understand basic excel functions and formating, then try reading up on it.
What you have asked for has been answered. It is not an excel fault but a user fault. i.e. you do not know what you are talking about,or I do not understand your english.
If you insist on using the wrong functions then i am not suprised that it will not do what you want. You asked for our help, if you will not take it then why bother to ask......
I have two things to say:
Firstly, I am sorry, Chippy088, if I offended you. I could perceive quite clearly the anger in your writing. I did not mean to offend you. I sincerely thank you for your contribution. Again, I am deeply sorry.
Secondly, thank you, Carlosdl, for providing me with the article. It is quite long and detailed, and will take me a while to read, absorb, and understand it. I am hoping that after I would have read it, we (all of us) can continue our discussion.
I have read the artlcle, Carlosdl.
Floating Point Arithmetic is a very complex subject to understand. I even consulted several websites on the subject, and the information is still beyond my grasp. However, I learned that Excel uses the popular standard of the design of computing systems in building the programming for spreadsheets.
I am very frustrated still, because that means what I thought I could depend on in Excel is no longer dependable. At least what I would like to know from my spreadsheet is 1 + 1 = 2. It looks like I can't even depend on that!!!
I am most grateful to you, Carlosdl, for supplying the article, and shedding a bright light on the subject of Floating Point Arithmetic.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 13 Replies