After all these years Excel STILL does bad math.

[SparkyJJO]

Through several versions Excel STILL does bad math with the basic SUM function. In my one spreadsheet I have 1,554.93 - 1,154.66 so it should equal 400.27. But no, Excel makes it equal to 400.2699999999990 instead.

It does this randomly throughout my worksheet. Far to the right of the decimal it will occasionally screw up. Then further down the sheet it randomly corrects itself. And it isn't just this one spreadsheet, I created a whole new one (one per year) and it still does it.

I only really care about the first two decimal places as this is dealing with money, and it is accurate "enough" for this, but the annoying thing is the one column that should always be 0.00 (for cross-checking purposes) ends up having random locations highlighted red, indicating a negative number. Because, in reality according to it, it is -0.0000000000009663 which again is bad math x2.

You'd think that Microsoft could at least make Excel able to do basic math. It really does make me wonder how inaccurate the numbers are that it spits out when dealing with more complex stuff!

/rant

 

[destrekor]

Through several versions Excel STILL does bad math with the basic SUM function. In my one spreadsheet I have 1,554.93 - 1,154.66 so it should equal 400.27. But no, Excel makes it equal to 400.2699999999990 instead.

It does this randomly throughout my worksheet. Far to the right of the decimal it will occasionally screw up. Then further down the sheet it randomly corrects itself. And it isn't just this one spreadsheet, I created a whole new one (one per year) and it still does it.

I only really care about the first two decimal places as this is dealing with money, and it is accurate "enough" for this, but the annoying thing is the one column that should always be 0.00 (for cross-checking purposes) ends up having random locations highlighted red, indicating a negative number. Because, in reality according to it, it is -0.0000000000009663 which again is bad math x2.

You'd think that Microsoft could at least make Excel able to do basic math. It really does make me wonder how inaccurate the numbers are that it spits out when dealing with more complex stuff!

/rant

well, .9999... = 1, so... no, it's right.

Plus, all you need to do to self-correct is limit to two decimal places. It's under advanced options for cells.
Sounds like after all these years, someone still needs more help with Excel.


But yes I agree... the program should automatically format based on inputs. If inputs only provide two decimal places, ideally the sum should also auto-format into the same number of decimal places.

 

[Anubis]

you don't even have to go into options theres a button on the bar that lets you change it at will

my only issue with excel is that it takes almost half an hour to open a 80mb work book

 

[Crusty]

Not really a flaw in Excel. There are things they can do to minimize the effect, but you're seeing an effect of simple floating point arithmetic on a CPU. The simple fact is that with the IEEE 745 floating point standard you only have a certain amount of precision and rounding errors will get introduced as operations are done on the numbers.

 

[shortylickens]

You need to learn how to format cells buddy.
Thats been standard since the DOS spreadsheet, (whatever they called it).

What I'd like to see is some high-end graphic software to replace my graphic calculator. Excel kinda sorta does it already but I'd like the ability to go into a more calculator type interface. y = mx + b and such.

 

[SparkyJJO]

well, .9999... = 1, so... no, it's right.

Plus, all you need to do to self-correct is limit to two decimal places. It's under advanced options for cells.
Sounds like after all these years, someone still needs more help with Excel.


But yes I agree... the program should automatically format based on inputs. If inputs only provide two decimal places, ideally the sum should also auto-format into the same number of decimal places.

BUT! In this case it isn't .999999 to infinity, it actually has an ending 0 after about ten 9s

I have it only displaying two decimal places. The nuisance is that it can't seem to do basic math at random times and thus shows my 0.00 as being "negative" because of failed subtraction further to the right of those two places. Even though further to the right is just a bunch of zeros it still manages to screw them up

 

[Malak]

It isn't a bug, it's an easter egg. To taunt the people that think .999999 = 1.

 

[zzuupp]

Not really a flaw in Excel. There are things they can do to minimize the effect, but you're seeing an effect of simple floating point arithmetic on a CPU. The simple fact is that with the IEEE 745 floating point standard you only have a certain amount of precision and rounding errors will get introduced as operations are done on the numbers.

This.

If you are trying to build a rocket, learn "Numerical Methods".

Or just learn Excel formatting if you are trying to balance your checkbook

 

[Ichinisan]

http://www.techradar.com/news/computing/why-computers-suck-at-maths-644771

 

[Venix]

It's not really bad math, it's just IEEE 754 floating point math. That's the result the processor returns when it subtracts those numbers, so it's not like Excel is doing something wrong. Any program that uses the FPU to subtract those numbers will get the same result.

Edit: Crusty beat me to it.

 

[SooperDave]

<thread jack> So we got a bar code scanner and tags at work but the guy setting it up doesn't know how to get excel to associate the bar code with the correct line item on the spread sheet. In other words the bar code is on machine BT06 and he needs the spread sheet to go to the line with the information about BT06 when it is scanned. Is there an easy answer or perhaps a tutorial somewhere? TIA <end thread jack>

 

[masteryoda34]

IEEE 754 FP issue, not a bug.

Computers represent numbers in binary. Numbers that are real and terminating when expressed in decimal (base 10) may very well be non-terminating when expressed in binary, or vice versa.

 

[dullard]

Like others have said, it is how computer processors work. Virtually ALL programs do that since it is your computer's fault and not the program. I suppose you could do a really clumsy workaround if you really wanted to (making calculations exceedingly slow). But instead, just use Excel's built-in ability to round.

Excel still has bad inexcusible graphing errors. But with math it does well.

 

[SparkyJJO]

Hmm, OK. You'd think there'd be some correction workaround built into it or something.

Guess I should go sue AMD/Intel over it

 

[Bignate603]

Not really a flaw in Excel. There are things they can do to minimize the effect, but you're seeing an effect of simple floating point arithmetic on a CPU. The simple fact is that with the IEEE 745 floating point standard you only have a certain amount of precision and rounding errors will get introduced as operations are done on the numbers.

That's what I was thinking.

 

[Bignate603]

Hmm, OK. You'd think there'd be some correction workaround built into it or something.

Guess I should go sue AMD/Intel over it

There is a work around, it's called properly formatting your cells

 

[Crusty]

Hmm, OK. You'd think there'd be some correction workaround built into it or something.

Guess I should go sue AMD/Intel over it

It's called significant digits and rounding.

 

[Matthiasa]

That is why old finance stuff uses BCD so no stupid rounding errors, just really slow operations.

 

[destrekor]

That is why old finance stuff uses BCD so no stupid rounding errors, just really slow operations.

Nobody really ever helped me figure out what laundering is....

 

[zzuupp]

<thread jack> So we got a bar code scanner and tags at work but the guy setting it up doesn't know how to get excel to associate the bar code with the correct line item on the spread sheet. In other words the bar code is on machine BT06 and he needs the spread sheet to go to the line with the information about BT06 when it is scanned. Is there an easy answer or perhaps a tutorial somewhere? TIA <end thread jack>

as described: vlookup

if it is more complicated, then read up on conditional sums

<end thread jack>

 

[The Boston Dangler]

calculator (win 7) gives me 400.27. why isn't this subject to the same error?

 

[SparkyJJO]

There is a work around, it's called properly formatting your cells

OK. How are they not properly formatted? I have them set to 2 decimal places. Yet it still turns 0.00 red because of a supposed -0.000000001. Please tell me how I am not formatting it right, I'd love to have it fixed

It's called significant digits and rounding.

Actually that makes my point even more. I have 2 significant digits, what the heck does it think it is doing going out to 10 or 12 digits?

 

[Crusty]

Actually that makes my point even more. I have 2 significant digits, what the heck does it think it is doing going out to 10 or 12 digits?

Are you really this dense or are you just trolling now?

 

[Venix]

calculator (win 7) gives me 400.27. why isn't this subject to the same error?

Calculator uses arbitrary-precision arithmetic instead of floating point for its calculations, so it doesn't suffer from the same drawbacks as Excel. It's a lot slower than floating point, but speed isn't really an issue for such a simple program.

 

[SparkyJJO]

Amazing how quick some people jump to calling someone a troll.

No, I'm not trolling. Just tired and not paying attention plus I haven't had to screw around with significant digits for a few years so it's all a bit fuzzy anyway.

<shrug>