After all these years Excel STILL does bad math.

[Cerb]

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?

It is calculating the result, in a way that is necessarily lossy, due to using floating point numbers. If you want quality arithmetic for money, don't use Excel. If you use Excel, you should learn about how it works. IEEE 754 likely cannot exactly represent the numbers you are using, so instead, you get a number that is very close. Once you apply arithmetic, rounding can be a source of inaccuracy, as well.

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

We use Decimal or bignum, in these modern times, with new software. Or, in DBs, integrated currency types, of course. If there was many-digit BCD support in x86, we would probably still use it. As it exists, it is too limited, and offers no meaningful advantages over working directly on bytes.

 

[ioni]

Type "500000000000002-500000000000001" into Google. Zomg Google can't even do basic math!

 

[Howard]

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

Do tell.

 

[KillerCharlie]

Excel is a terrible terrible tool to use for... most things. I practically never use it at work. At the very least I'll write something in matlab or perl or Fortran or something - anything but that pile of crap. I look down on engineers that use it excessively.

 

[Cheesetogo]

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.

There's always the really powerful stuff like Maple and Mathematica. For something simpler, you could try the TI Nspire software.

 

[edro]

Excel is a terrible terrible tool to use for... most things. I practically never use it at work. At the very least I'll write something in matlab or perl or Fortran or something - anything but that pile of crap. I look down on engineers that use it excessively.

Depends on what you're engineering and your allowable tolerance.
99% of things don't need Matlab. Excel can do most mechanical/electrical engineering equations easy enough.

 

[sdifox]

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

Do a save as to another file name and try again.

 

[Good4Me]

Try this http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9caca722-5235-401c-8d3f-9e242b794c3a

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.

 

[rasczak]

Try this http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9caca722-5235-401c-8d3f-9e242b794c3a

Lurker spotted!!! I caught him!

Oh and nice tool.

 

[AeroEngy]

Here is an example using single precision floats to show you where the error comes from. Excel probably uses double precision? but this is just an example.

1154.66 converted to SP Float is 010001001100100000101010100011110 or 4490551E in Hex. If you convert back to decimal from the binary representation you get 1154.6599

1,554.93 converted to SP Float is 010001001110000100101110111000010 or 44C25DC2 Hex. If you convert back to decimal from the binary representation you get 1554.9299

Some decimal numbers just can not be represented exactly in the IEEE 754 standard. Hence the source of your error.

 

[bignateyk]

Looks like it's doing the math just fine to me. You just need to tell it how many decimal places to show.

 

[SparkyJJO]

Looks like it's doing the math just fine to me. You just need to tell it how many decimal places to show.

Congrats on not reading the thread

 

[IceBergSLiM]

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

what version? 2003-2007-2010

 

[jman19]

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

No one thinks .999999 = 1. Fail.

 

[mb]

Actually, Excel 2010 solves =1554.93-1154.66 as exactly 400.2700000.

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

what version? 2003-2007-2010

Anubis is incorrect. That only changes how many decimals are displayed. With the default settings, it won't actually change the number itself.
Unless of course your question about what version relates to his second statement about how long it takes to open a work book.

But for old versions, the round formula comes in handy.

=ROUND(1554.93-1154.66,2) will give you 400.2700000.

 

[JulesMaximus]

Works fine for me with Excel 2007.

 

[Bignate603]

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

If it's messing up because it's still a tiny negative number you could use the round function to truncate it at 2 decimals.

 

[Juddog]

If you use Excel, you should learn about how it works.

^^ This. User error doesn't mean the program is bad.

 

[Anubis]

what version? 2003-2007-2010

2003, some of it is do to shitty work computers, and some of it is do to the network

 

[Macamus Prime]

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>

That's because these forums are full of asshat know-it-alls, that really don't know anything.

All they know is how to pick at minor points and errors and flip it to somehow make you look like the moron. Their egos are fragile and need constant defending.

Also, they are 350pd, 48 year old virgins that eat dog food and live in their parents basement.

 

[Numenorean]

Odd because when I put in 1,554.93 - 1,154.66 it gives me the right answer :hmm:

 

[Fear No Evil]

Has anyone told the OP that this is normal behavior yet?

 

[Train]

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

If you have an 80MB Excel book, you are doing it wrong. WAY wrong.

If I found someone in my company trying to use an 80MB excel book, I'd nix that shit right quick, and I'm not even the IT guy.

 

[Anubis]

If you have an 80MB Excel book, you are doing it wrong. WAY wrong.

If I found someone in my company trying to use an 80MB excel book, I'd nix that shit right quick, and I'm not even the IT guy.

ove tried to tell people to use access for this but they dont listen
its got like 9 pivot tables in it

 

[IcePickFreak]

No one thinks .999999 = 1. Fail.

Gas stations still do, just sayin.