subtraction returning incorrect calue

[Ashish_Vaidya]

We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal
places until we see the value change. For instance, one of our number was
going out to x,xxx.499999999999999999999999999999900000 while the other
number was x,xxx.5000000000000000000000000000000000. When we rounded the
numbers, one rounded up while the other rounded down. This is fine. Now
that we fixed the formula to TRUNC instead of ROUND, I thought the issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me.
Has anyone else run into this issue? How did you fix it?

 

[Joe User]

when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?

FYI, it does not make sense to format more 15 "significant digits" -- that
is, 15 digits starting with the first non-zero digit on the left.

Has anyone else run into this issue?
How did you fix it?

Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation. But
that can result in surprising results. If you choose to try PAD, be sure to
make a backup copy of your Excel file first. However, note that PAD will not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375, whereas 0.1 is exactly
0.100000000000000,0055511151231257827021181583404541015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

 

[Joe User]

the Excel implementation is half-baked. It leads
to even more confusion

Although I do not like this Excel heuristic, I do not want to leave you with
the impression that Excel has a lock on confusing behaviors. The operative
word is "more" confusion.

For example, because of the nature of 64-bit floating bit arithmetic, the
order of operations can lead to different results. For example, 10.1 - 10 -
0.1 results in an infinitesimal number (about -3.6E-16), whereas 10.1 - 0.1 -
10 results in exactly zero.

All the more reason to rely on some form of explicit rounding, be it the
ROUND function (my preference) or the Precision As Displayed calculation
option.

The advantage of PAD is that it is pervasive; no need to for you to remember
to use ROUND.

The disadvantage of PAD is that is pervasive ;-). It might apply to
formulas where you prefer not to round. For example, typically I do not
round intermediate amortization schedule formulas; otherwise, the
quantization error results in apparent errors. I only round the actual
payment, since that is constrained by real-world requirements. (You can pay
fractional cents.)

And note that PAD can have deleterious and irreversible effects on
constants, e.g. periodic interest rates, that you might want to display with
limited decimal places, but you want the value to remain "exact".

(That is, as "exact" as 64-bit floating point permits.)


----- original message -----

when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?

FYI, it does not make sense to format more 15 "significant digits" -- that
is, 15 digits starting with the first non-zero digit on the left.

Has anyone else run into this issue?
How did you fix it?

Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation. But
that can result in surprising results. If you choose to try PAD, be sure to
make a backup copy of your Excel file first. However, note that PAD will not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375, whereas 0.1 is exactly
0.100000000000000,0055511151231257827021181583404541015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal
places until we see the value change. For instance, one of our number was
going out to x,xxx.499999999999999999999999999999900000 while the other
number was x,xxx.5000000000000000000000000000000000. When we rounded the
numbers, one rounded up while the other rounded down. This is fine. Now
that we fixed the formula to TRUNC instead of ROUND, I thought the issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me.
Has anyone else run into this issue? How did you fix it?

 

[Ashish_Vaidya]

While I understand the nature of fitting a product's capabilities to a wide
variety of users and their particular needs, it becomes terribly inefficient
to try and re-write all of my formulas to a ROUND function due to this
phenomena.

Thank you for your answer as it answers my question, yet unsatisfied with
the solution.
--
Ashish

when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?

FYI, it does not make sense to format more 15 "significant digits" -- that
is, 15 digits starting with the first non-zero digit on the left.

Has anyone else run into this issue?
How did you fix it?

Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation. But
that can result in surprising results. If you choose to try PAD, be sure to
make a backup copy of your Excel file first. However, note that PAD will not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375, whereas 0.1 is exactly
0.100000000000000,0055511151231257827021181583404541015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal
places until we see the value change. For instance, one of our number was
going out to x,xxx.499999999999999999999999999999900000 while the other
number was x,xxx.5000000000000000000000000000000000. When we rounded the
numbers, one rounded up while the other rounded down. This is fine. Now
that we fixed the formula to TRUNC instead of ROUND, I thought the issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me.
Has anyone else run into this issue? How did you fix it?

 

[Joe User]

While I understand the nature of fitting a
product's capabilities to a wide variety of
users and their particular needs

Actually, this problem arises for the lack of that, IMHO.

unsatisfied with the solution.

Then perhaps the Precision As Displayed option is the right thing for you.

You will need to format all cells whose values that you want rounded to some
numeric format that specifies the number of decimal places, e.g. Number or
Accounting. You might have that already.

Also, you might want to format all cells whose values that you do __not__
want rounded to General. For example, typically we want annual interest
rates and periodic rates derived from them to be "exact" (to the degree
permitted by the 64-bit floating point form). Of course, that means you lose
control over the appearance of the cell.

Then you might need ROUND only in formulas where you compare expression,
like IF(A1+B1 = C1-D1, ..., ...). That should be IF(ROUND(A1+B1,2) =
ROUND(C1-D1,2), ..., ...). Or you could just wait and see if a problem
arises ;-).


----- original message -----

While I understand the nature of fitting a product's capabilities to a wide
variety of users and their particular needs, it becomes terribly inefficient
to try and re-write all of my formulas to a ROUND function due to this
phenomena.

Thank you for your answer as it answers my question, yet unsatisfied with
the solution.
--
Ashish

when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?

FYI, it does not make sense to format more 15 "significant digits" -- that
is, 15 digits starting with the first non-zero digit on the left.

Has anyone else run into this issue?
How did you fix it?

Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation. But
that can result in surprising results. If you choose to try PAD, be sure to
make a backup copy of your Excel file first. However, note that PAD will not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375, whereas 0.1 is exactly
0.100000000000000,0055511151231257827021181583404541015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal
places until we see the value change. For instance, one of our number was
going out to x,xxx.499999999999999999999999999999900000 while the other
number was x,xxx.5000000000000000000000000000000000. When we rounded the
numbers, one rounded up while the other rounded down. This is fine. Now
that we fixed the formula to TRUNC instead of ROUND, I thought the issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me.
Has anyone else run into this issue? How did you fix it?

 

[Fred Smith]

Why do you need more than 15 digits of precision? You can calculate the
distance to the sun to the nearest micrometre in 15 digits. What application
can need more precision than that?

Regards,
Fred

While I understand the nature of fitting a product's capabilities to a
wide
variety of users and their particular needs, it becomes terribly
inefficient
to try and re-write all of my formulas to a ROUND function due to this
phenomena.

Thank you for your answer as it answers my question, yet unsatisfied with
the solution.
--
Ashish

when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?

FYI, it does not make sense to format more 15 "significant digits" --
that
is, 15 digits starting with the first non-zero digit on the left.

Has anyone else run into this issue?
How did you fix it?

Yes, this is very common. "Everyone" runs into this issue at some point.

The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND
function.

For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.

(Alternatively, you might use the Precision As Displayed calculation.
But
that can result in surprising results. If you choose to try PAD, be sure
to
make a backup copy of your Excel file first. However, note that PAD will
not
help in the example above.)

The longer answer can become quite involved. In short, Excel stores
numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.

But the point to note is: most decimal fractions cannot be represented
exactly.

Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the
same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109375, whereas 0.1 is
exactly
0.100000000000000,0055511151231257827021181583404541015625.

The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even
more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.


----- original message -----

We have a spreadsheet that subtracts one number from another. We did
not
understand why Excel is rounding the way it did so we decided to add
decimal
places until we see the value change. For instance, one of our number
was
going out to x,xxx.499999999999999999999999999999900000 while the
other
number was x,xxx.5000000000000000000000000000000000. When we rounded
the
numbers, one rounded up while the other rounded down. This is fine.
Now
that we fixed the formula to TRUNC instead of ROUND, I thought the
issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that
comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense
to me.
Has anyone else run into this issue? How did you fix it?