Format your sheet as Accounting. A1=76931.15. B1=73579.10. In C1 subtract the first 2 numbers which yields 3352.05. In D1 type 3352.05. In E1 subtract C1 and D1 Yielding (0.00). If you evaluate that formula C1 formula evaluates out to 11 decimal places (3352.049999999). Only happens if one of the number decimals is .10. .20 is just fine. Driving me crazy. Anybody know why. Thanks for any help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel 2010 formula withing Formula problem
- Thread starter SUSANVV
- Start date
JVFriederick
IS-IT--Management
I think this long winded article may help explain :
XL sometimes 'displays' numbers out of sorts with 'reality', but you should
remain confident that it will perform the math correctly when using the numbers.
I have not found an instance yet where this behavior impacted a model.
JVF
XL sometimes 'displays' numbers out of sorts with 'reality', but you should
remain confident that it will perform the math correctly when using the numbers.
I have not found an instance yet where this behavior impacted a model.
JVF
JVFriederick
IS-IT--Management
The important part of the kb article.....
Another confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and its variations. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when it is stored in the mantissa:
000110011001100110011 (and so on)
The IEEE 754 specification makes no special allowance for any number. It stores what it can in the mantissa and truncates the rest. This results in an error of about -2.8E-17, or 0.000000000000000028 when it is stored.
Another confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and its variations. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when it is stored in the mantissa:
000110011001100110011 (and so on)
The IEEE 754 specification makes no special allowance for any number. It stores what it can in the mantissa and truncates the rest. This results in an error of about -2.8E-17, or 0.000000000000000028 when it is stored.
- Thread starter
- #4
Hi. Thank you for the reply. The answer of 0 obviously is correct. However when you format the cells as Accounting (which our accounting department does) it makes the number a negative number even though it is -.00000000001, hence the parenthesis around the 0.00. It is interesting that when you evaluate the first formula it evaluates to only 2 decimal places. It is only in the second formula that the first formula evaluates out the 11 decimal places. It will not display the accounting "-" used to indicate a zero answer.
Thanks for the input. Essentially that quote says that there is nothing we can do about those types of formatting anomalies.
Have a great day.
Thanks for the input. Essentially that quote says that there is nothing we can do about those types of formatting anomalies.
Have a great day.
JVFriederick
IS-IT--Management
Yes, that is the takeaway.
If needed, I will use the Round function to 2 decimal places to remove annoying parenthesis.
However, this should only be done on final values (not used elsewhere). Otherwise you will introduce rounding errors.
JVF
If needed, I will use the Round function to 2 decimal places to remove annoying parenthesis.
However, this should only be done on final values (not used elsewhere). Otherwise you will introduce rounding errors.
JVF
I do not remember it it was Excel or another application. But I was checking for zero values after a calculation. Even though the number displayed was zero (and should have been). The actual number stored was very small (something like .00000010). Drove me nearly crazy trying figure out why my formulas were not 'working'.
- Thread starter
- #7
Thanks for the replies. As least I know I am not crazy for that reason. It also seems to only be a problem if one of the actual numbers has .10. I am guessing because of the base 10 thing. If I change it to .20 everything works fine. Thanks again.
Sue
Sue
- Status
Similar threads
- Locked
- Question