what is wrong with Excel?

[EKKC]

Text
i cant grasp this concept of 1-1 != 0


by the way the numbers are typed in. a 0 = zero. not decimals

 

[KLin]

The values > 0 probably have quite a few decimal places in them. Click on them and see what the actual value is at the top.

 

[WW]

yep...format the cells to numbers with 1 decimal place (or whatever you want...)

 

[EKKC]

i suspected that. i typed these numbers in!!! weird, huh?

try it, open excel up, TYPE those in and see for yourself

 

[Gibson486]

I used to get that all time. When that happened it was because excel switched my format to words from numbers, or somethig like that. When every I tried to make a script to copy teh numbers, it would always say that a number was not in the cell.

 

[KLin]

lol I just did it too and got the same results. Bug?

nvm, not a bug. changed the format to numeric with 1 decimal place and it added up to 0.

 

[EKKC]

you guys are right actually. i had to format it as a number with 2 places. but this is so weird that it wont equal to zero when you're TYPING the numbers in manually

 

[dullard]

Originally posted by: EKKC
you guys are right actually. i had to format it as a number with 2 places. but this is so weird that it wont equal to zero when you're TYPING the numbers in manually

Um, this is a well known and common problem with all computer based arithmatic. The rest of the world has gotten accustomed to it. Time for you to realize that fact too and deal with the problem. It has absolutely nothing to do with Excel or the way you entered the numbers.

 

[EKKC]

but look at the pic again, why does the calculation work when the last number to be summed is a non-zero value

 

[dullard]

Originally posted by: EKKC
but look at the pic again, why does the calculation work when the last number to be summed is a non-zero value

You are wrong with the assumption that the calculation "works" whenever the last number is a non-zero value. To disprove that statement, try moving the 39.9 to the last position instead of moving the 9748. 39.9 is a non-zero value. And it'll now display -1.456661E-12.

The problem comes down to conversion from decimal floating point numbers to binary numbers and back. Most floating point decimal numbers cannot be accurately represented in binary. The last 0 or 1 in the binary number is an attempt to round to the closest possible representation of that decimal number. A close representation, but not an exact representation. Thus the last binary digit is often incorrect. When you do arithmetic with lots of these floating point numbers, the chance that all will be correctly converted to binary and then back to decimal is slim-to-none. So you get computer answers like 1.4 + 2.6 = 3.9999999999. Just deal with it. Be an engineer (close is correct) not a mathematician (close is incorrect) when you use computers to calculate floating point arithmetic.

 

[archcommus]

Originally posted by: dullard

Originally posted by: EKKC
you guys are right actually. i had to format it as a number with 2 places. but this is so weird that it wont equal to zero when you're TYPING the numbers in manually

Um, this is a well known and common problem with all computer based arithmatic. The rest of the world has gotten accustomed to it. Time for you to realize that fact too and deal with the problem. It has absolutely nothing to do with Excel or the way you entered the numbers.

I don't think Joe Schmo who buys Excel for his small business, inputs a value from each day, wants a sum of those values, and then sees that Excel displays the wrong answer would accept Microsoft saying to him "Duh, the whole world knows computers work like that, you have to enter the numbers in a particular order!"

 

[dullard]

Originally posted by: archcommus
I don't think Joe Schmo who buys Excel for his small business, inputs a value from each day, wants a sum of those values, and then sees that Excel displays the wrong answer would accept Microsoft saying to him "Duh, the whole world knows computers work like that, you have to enter the numbers in a particular order!"

What do you want Microsoft to say? Should they say, "It is a problem with how computers work, there is no solution, so you are screwed?"

 

[EKKC]

thanks. its actually happening in a planning software and not excel...

when 0 = 0.000000000001 and you compare it to another number to calc. a percentage, your percentage looks like 200,000,000,000,000%. my client dont like it how do i explain it to him?

think about cash flow and balance sheets, they want zero. not 0.4E-19, and a percentage of 0 when comparing 0 and 50000, not 20gazillion %

 

[archcommus]

Originally posted by: dullard

Originally posted by: archcommus
I don't think Joe Schmo who buys Excel for his small business, inputs a value from each day, wants a sum of those values, and then sees that Excel displays the wrong answer would accept Microsoft saying to him "Duh, the whole world knows computers work like that, you have to enter the numbers in a particular order!"

What do you want Microsoft to say? Should they say, "It is a problem with how computers work, there is no solution, so you are screwed?"

No, but if this is a fundamental issue when adding numbers in binary that causes you to have to enter numbers in a particular order, I would imagine that should be mentioned somewhere in the manual. Is it?

 

[dullard]

Originally posted by: archcommus
No, but if this is a fundamental issue when adding numbers in binary that causes you to have to enter numbers in a particular order, I would imagine that should be mentioned somewhere in the manual. Is it?

There is no way to predict what order will give what answer. Thus, how can a manual tell you to correct a non-issue? I say non-issue since 10^-12 error on an answer of 0 is meaningless. Instead, the manual does say how to display numbers with any precision you want.

 

[kranky]

Originally posted by: EKKC
thanks. its actually happening in a planning software and not excel...

when 0 = 0.000000000001 and you compare it to another number to calc. a percentage, your percentage looks like 200,000,000,000,000%. my client dont like it how do i explain it to him?

think about cash flow and balance sheets, they want zero. not 0.4E-19, and a percentage of 0 when comparing 0 and 50000, not 20gazillion %

If you're the one providing the Excel files to the client, use the round() function on the cell that holds the total.

 

[EKKC]

the problem is coming from a non-excel planning software that I do consulting on, I tried this on Excel because I couldn't understand that error and replicated the exact problem in Excel, so I think Dullard was right that it has nothing to do with Excel but a common arithmetic error/bug in computers in general.

I know it's an easy fix in Excel, however this software I work with does not... I already called support for the software company and see if they have a fix.

thanks to all who commented.

Originally posted by: kranky

Originally posted by: EKKC
thanks. its actually happening in a planning software and not excel...

when 0 = 0.000000000001 and you compare it to another number to calc. a percentage, your percentage looks like 200,000,000,000,000%. my client dont like it how do i explain it to him?

think about cash flow and balance sheets, they want zero. not 0.4E-19, and a percentage of 0 when comparing 0 and 50000, not 20gazillion %

If you're the one providing the Excel files to the client, use the round() function on the cell that holds the total.