The Lounge - CodeProject

Well yeah - that was kinda the reference I was making D'Oh! | :doh:

cheers
Chris Maunder

So what you're saying is Excel doesn't Excel? Smile | :)

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 Laugh | :laugh:

/

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. Smile | :)

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.

Cool | :cool:

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. Smile | :)

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.

CEO at:
- Rafaga Systems
- Para Facturas
- Modern Components
for the moment...

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 OMG | :OMG:

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!! Big Grin | :-D

So...I'm just guessing that the whole of Iran won't be hungover tomorrow? Laugh | :laugh:

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 Big Grin | :-D

Come here and join to me Thumbs Up | :thumbsup:

A very kind offer, but I will unfortunately have to refuse this time round! Thumbs Up | :thumbsup:

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 Thumbs Up | :thumbsup:

Ravi Khoda

thanks for your greeting and i wish every good things for you. Smile | :)

Also best wishes to establish diplomatic relations. Rose | [Rose]

Welcome to the Lounge