|
It actually starts in A2 but since I left the formatting as "general format" it only show 1833.34. If I hard code that A12 = 0.
This was a quick calculation that was discarded, so I did not spend time on it. I just made a comment on people not realizing nothing is exact.
|
|
|
|
|
I think also that the OP complains for all the wrong reason. Given that he confirms that one value is the result of a formula and the 170 is only the value shown, the result is to be expected. Will give the same result in pretty much all progamming languages and improper selected output/format options as well...
|
|
|
|
|
You realise this is how @DalekDave is making his fortune. He's siphoning off the fractions of a cent. Bit...by...bit...
cheers
Chris Maunder
|
|
|
|
|
Presumably that means he's going to build a giant computer that'll do whatever you tell him to tell it to do?
BTW, now he's got your email address, you can expect to see an increase in spam on such diverse topics as printer cartridges, UK politics, red buckets, and exterminating Time Lords. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
We need a "closes eyes and prays for patience" emoticon.
cheers
Chris Maunder
|
|
|
|
|
I absolutely second that!
I will never again mention that Dalek Dave was the poster of the One Millionth Lounge Post, nor that it was complete drivel.
|
|
|
|
|
I also heard that he discovered he was doing it the wrong way and it drained his bank account.
|
|
|
|
|
Eh - he learned that trick from Richard Pryor in the Superman 3 movie. He did just that - siphoned the rounded off cents off of all the paychecks and stuck it in his own.
One of the few movies he was in where he wasn't over the top funny....
|
|
|
|
|
Well yeah - that was kinda the reference I was making 
cheers
Chris Maunder
|
|
|
|
|
So what you're saying is Excel doesn't Excel? 
Along with Antimatter and Dark Matter they've discovered the existence of Doesn't Matter which appears to have no effect on the universe whatsoever!
Rich Tennant 5th Wave
|
|
|
|
|
Corporal Agarn wrote: I know of a number of companies
try every single financial institution you are ever likely to deal with.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What bothers me more is that no matter what code I write to create the most amazing applications that can make difficult calculations at light speed it doesn't matter for our customers, because all they'll ever want is Excel  / 
|
|
|
|
|
Either A11 or B11 is actually some other value, it is just rounded. If you put real or calculated values that are exactly 170 in both cells then the result will be zero.
|
|
|
|
|
Actually I did that, but as stated, this was only a quick calculation to be discarded.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
