|
I know of a number of companies that live by Microsoft Excel. What worries me is I just did a simple calculation A12 = A11 - B11.
So A11 = 170 (actually it is a formula but this is the value)
- B11 = 170
------------
A12 = -2.3E-13
Now I know why this happens, but it worries me that if a company uses Excel for their financial records it could cause a problem.
|
|
|
|
|
I think there is a dilbert for this
|
|
|
|
|
Corporal Agarn wrote: Excel
I went to a "Big Data" conference yesterday where I was told the "Data Scientist" is dead. There will only be Business Analysts and if they don't get the answer in 30 seconds they will move to another tool.
I did note it "was the answer" he said and not "the right answer".
So don't worry.
|
|
|
|
|
And what was the teller selling?
|
|
|
|
|
Contract solutions to make Hadoop "fast".
|
|
|
|
|
Eggbert Bartholomew Bligh wrote: I was told the "Data Scientist" is dead.
The relationship between a Business Analysts and a Data Scientist is on a par with the relationship between a web developer and a software developer.
Meaning that many of them seems to perform their work using Google, copy and paste ... and they still manage to get it wrong.
|
|
|
|
|
(disclaimer: INAEU)
Are you certain the result in A11 is really 170 (and not 169.9999999 or 170.0000001 or something like that )
Did you set the A12 cell to the proper type ( I assume currency) ?
(I tried in Excel 2003 and it works both ways)
I'd rather be phishing!
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
