MS Excel question

[Kenazo]

Ok, so I was entering some numbers, told Excel to sum them, and they don't equal zero. I had it to two decimal places and I get the following answer: ($0.00). Negative zero? Huh? then I increased the decimal places and it equalled(0.000000000000738964445190504000). The computer is a dual Xeon win2003 terminal server running Office XP.

How the heck can excel screw up in adding? None of the numbers were formulas, just straight plugged in numbers.

WTH?

8,632.34
(4,736.58)
(13,265.77)
800.00
555.00
3,141.47
1,296.21
600.00
1,530.01
(88.81)
995.61
(27.66)
114.97
78.91
24.74
349.56

 

[mugs]

Originally posted by: Kenazo
How the heck can excel screw up in adding? None of the numbers were formulas, just straight plugged in numbers.

Because most non-integers have no exact binary representation.

 

[krcat1]

Remember how the computer stores floats vs. integers.

Isn't there a way to tell Excel to store currency value in an exact format?

You could always use the rounding function on the values being added.

 

[Kenazo]

That's more work than it's worth. It doesn't really bother what I'm needing to do anyway (add checks on a client's journal entries), I just found it bizarre that excel couldn't add properly.

 

[oboeguy]

Werid. I copy-pasted your column there and got a real answer (single P4 with HT running WinXP + Office XP).

 

[WannaFly]

Originally posted by: oboeguy
Weird. I copy-pasted your column there and got a real answer (single P4 with HT running WinXP + Office XP).

 

[fs5]

26,531.54 ?

 

[oboeguy]

Yeah I think that was it. 26K-something.

 

[Kenazo]

Oops, the column was supposed to be equal to zero. The $13,265.77 was supposed to be a credit (negative in this instance).

8,632.34
(4,736.58)
(13,265.77)
800.00
555.00
3,141.47
1,296.21
600.00
1,530.01
(88.81)
995.61
(27.66)
114.97
78.91
24.74
349.56


Can someone try it now? I still get my wrong number. Use about 30 decimal places.

 

[dderolph]

I get the exact same result you got, Kenazo. This is on my Win XP/Office 2003 machine, with an Athlon XP 2200 processor. You don't have a problem; this is a known phenomenon. If the brackets around the 0.00 bothers you, you might multiply the results by -1. I think you'll also find that changing the formatting to Number or currency will cause the brackets to disappear.

 

[ArmchairAthlete]

The float problem is universal across computers, languages, programs I think.

 

[oboeguy]

Ah, now your problem makes sense. This is, as others have mentioned, a well known problem. In short, your computer isn't doing exact arithmetic. So then the problem of "subtracting one number from another" turns out to be "unstable" when the two numbers in question are close to each other (obviously the case when you are hoping to get zero from adds and subtracts). I hope you realize that for numerical people, "0.000000000000738964445190504000" IS zero (though not in the same way as .9999... = 1 ). If it bugs you, write a conditional around the sum which sets the cell value to zero is the sum is smaller in absolute value than some tolerance (like smaller than 1 cent or whatever applies to your situation). Should be pretty trivial to do.

I did it for you anyway: =IF(ABS(B1)<0.01, 0, B1)

Obviously replace "B1" which your sum in both places (a little sloppy, I guess).

 

[ShadowBlade]

i got 0.00 also (Excel 2003)

 

[fs5]

stop using 30 decimal places?

 

[Kenazo]

Of course it didn't bother anything I was doing, I just found it bizarre that 0=/0

 

[SirPsycho]

oboeguy has the right idea. It's an inherent problem with using floating-point decimal representations. One workaround is to tell Excel to use the displayed precision as the actual precision. In Office 2000, you do it by going to Tools | Options | Calculation, and checking the "Precision as displayed" checkbox under "Workbook options". However, the data will be permanently, irrevocably truncated to however many decimal places you have it set to display, so be sure that's really what you want to have happen. If you're dealing only in dollars and cents, I don't think you should have any problems by doing so, but do so at your own risk.

 

[Kenazo]

thanks for that info, psycho. My problem was coming b/c some of my if statements weren't working, since 0=/0, but w/ that option, then it would again.