|
Some others on here point out and I agree - the 170 you perceived in each cell could not have been EXACTLY 170 or the result would be EXACTLY 0.
The source cells must contain decimal content you aren't seeing most likely due to formatting. Do not (though I imagine there are many who do) confuse formatting with actual content.
This is not an Excel issue. It is merely doing what it is told.
Nothing to worry about.
|
|
|
|
|
It's called "round off error", the values may have displayed as 170, but were rounded for the display. And I said round off error, because the calculations did not give a precise answer. You are aware that computers use binary, not decimal for calculations (unless told to), right? Floating point binary numbers do not map directly to floating point decimals. Think 1/3 = 0.333333333... Calculators used to have the problem that 1/3*3 would give you back 0.9999999 because they used a set number of digits. Then we took to keeping two extra digits hidden and then rounding the display. Binary floating point has similar repeating values, just in different places. 0.10 (decimal) is a repeating binary floating point value, so 1/10*10 would not give you 1 without rounding up the result.
Subtracting a "clean" value from a calculated value will generally give you remainders, just look at your result,
Corporal Agarn wrote: -2.3E-13
That's -0.00000000000023 (if I counted my zeroes correctly) and that's a small value difference.
Close enough for bankers, or should be.
Story from my past...
A program was converted from one machine architecture to another. The difference between the two programs over a $10 million dollar total was $0.35. A meeting was called involving about ten people from upper management and the programming staff. Management demanded a reason for the difference. Explanations of machine difference in numerical processing went over their heads. As the meeting raged on, my buddy (one of the programmers) said he had to overcome an overwhelming urge to stand up and throw thirty-five cents on the conference table and shout, "Problem solved, gentlemen!" The cost of the meeting, even if a solution was found, would not have been recouped for over a century.
Psychosis at 10
Film at 11
Those who do not remember the past, are doomed to repeat it.
Those who do not remember the past, cannot build upon it.
|
|
|
|
|
Thanks.
What everyone seems to miss is I stated I knew the reason. I was just ranting that someone who did not might have a $0.35 error. 
|
|
|
|
|
If excel didn't show you the 0.00000000000023 then excel wouldn't be exact and then there would be a problem
|
|
|
|
|
-2.3E-13 = 0.00000000000023
Which is exact
You are misleading people
|
|
|
|
|
Corporal Agarn wrote: that if a company uses Excel for their financial records it could cause a problem.
The accountants that I have known appeared to be competent and were aware of technical problems in terms of computational problems. This tended to be larger companies since a company has to be big enough to hire an accountant (or more than one) in the first place.
Smaller companies have more problems with finances but it seldom a small problem but more like badly negotiating a contract, not tracking expenses at all, etc.
|
|
|
|
|
The excel executes user code, user code executes system code, system code runs floating-point calculation on the processor, floating-point calculation is done with discrete mathematical algorithm and discrete mathematical algorithms are not exact.
This seems does not worry the financiers, since they always use rounded to 2-4 digits (after point) result.
|
|
|
|
|
Finances will be fine, most of the companies don't use more tan two digits (so this will be rounded to zero), and even the most picky ones don't use more than 10.
|
|
|
|
|
Don't you just love floating point rounding? I lost track of the number of VB6 "bug" blogs that were nothing more than floating point rounding issues.
|
|
|
|
|
Heavens, no!
It's off by 0.00000000000023 
|
|
|
|
|
Nah...
It is people that cause problems.
Out of all the programs that I use, it is probably Excel that is the one that I wish that I wrote.
It is sensational.
I can still output old biff file format and Excel will recognise and open it immediately.
Give any user a great and powerful application and of course they can cause havoc and hell.
Its the way things are and always have been.
...and Yep I have also elephanted up a spreadsheet and was horrified by my error or errors.
But Hey... How good is it.
"Rock journalism is people who can't write interviewing people who can't talk for people who can't read." Frank Zappa 1980
|
|
|
|
|
I don't know about the USA, but in the EU - all financial calculations must be performed in DECIMAL arithmetic with a specific accuracy (IIRC, 5 digits after the decimal point). Rounding is also performed using a specific algorithm (IIRC - rounding after conversions from legacy currencies had to be performed as "round to nearest or away from zero").
Excel's calculation method does not satisfy any of these requirements, so no large company based in the EU would use Excel for record keeping.
I grant you that when programming financial models, rounding errors can accumulate catastrophically and give totally bogus results. The first part of the solution to this is a good course in Numerical Analysis...
|
|
|
|
|
Just 20 min left until Iranian New Year which is Called Nowruz!! 
|
|
|
|
|
So...I'm just guessing that the whole of Iran won't be hungover tomorrow? 
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
Which hungover? we here enjoy much a lot  
Come here and join to me 
|
|
|
|
|
A very kind offer, but I will unfortunately have to refuse this time round! 
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
|
happy new year to all the all the Iranian people. wish you luck for the best future
Ravi Khoda
|
|
|
|
|
thanks for your greeting and i wish every good things for you. 
Also best wishes to establish diplomatic relations. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Get rid of the politicians, and we probably could.
|
|
|
|
|
Happy New Year to all Iranians!!!
|
|
|
|
|
Thanks Dude
|
|
|
|
|
|
Yes, ok its cool, but how about learning c++ in 2 minutes 
I am just gonna leave this here
Microsoft ... the only place where VARIANT_TRUE != true
|
|
|
|
|
Chuck Norris can do it in one!
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
