sum question

  • Thread starter Thread starter BT
  • Start date Start date
B

BT

Hi

Could anyone pls calculate the following using excel?
10402.3-10251.6-150.7, the answer in excel is '-1.08002E-12'

But when using calculator, the answer is 0.

Please help and advice!!

Thanks
BT
 
BT said:
Hi

Could anyone pls calculate the following using excel?
10402.3-10251.6-150.7, the answer in excel is '-1.08002E-12'

But when using calculator, the answer is 0.

Please help and advice!!

Thanks
BT
Click to expand...
You could format the cells concerned as number with 1 decimal place.
I don't know if that is a valid option in your situation but it works in a
new spreadsheet.
Steve
 
Excel (and almost all other general purpose computer software) does
binary math, not decimal math. The binary (specifically IEEE standard
double precision) approximations to to your inputs are
2859362451403571/274877906944
5635876701654221/549755813888
2651142436893491/17592186044416
The exact result of your calculation, using these approximations to your
inputs, is
-19/17592186044416 = -1.08002495835535228252410888671875E-12
which Excel correctly reports to its documented (see Help for "Excel
specifications and limits" subtopic "Calculation specifications")limit
of 15 decimal figures.

The easiest way to think about the impact of binary approximation is to
use that 15 digit rule of thumb to consider your problem as
10402.3000000000???
-10251.6000000000???
-150.700000000000?
--------------------
0.0000000000???
which is entirely consistent with the calculated answer of
-0.000000000001...
This also suggests the simplest (and theoretically justifiable) approach
to dealing with the impact of binary approximations -- just round the
final result to discard any figures beyond what can be reliably represented.

Jerry
 
I think the best way is to format all cells requiring precise calculations
to a number or accounting with the required decimal places. This is after
rounding after calculations. i.e..
format columns b,c, d & e as a number with 1 decimal. Then

b2=10402.3
c2=10256.6
d2=150.7

then in cell e2 enter =round(b2-c2-d2,1)

result is 0.0
 
The problem is that XL, like all spreadsheets, uses IEEE double
precision floating point math. This means that all values are stored
using a finite number of binary digits, corresponding to about 15
decimal digits of precision.

Just as most numbers (e.g., one-third) cannot be exactly represented in
a finite number of base-10 digits (i.e., 0.333333333333333 <> 1/3), most
numbers cannot be exactly represented in a finite number of binary
digits.

While IEEE math uses several techniques to minimize rounding errors,
it's not capable of catching all of them.

See http://cpearson.com/excel/rounding.htm for more.

NOTE: It's usually better to explicitly ROUND(), or to decide what order
of magnitude such rounding error may generate, and make a comparison
accordingly, e.g.:

=IF(ABS(10402.3-10251.6-150.7) < 1E-10, "zero", "not zero")
 
thx for your explaination

BT
JE McGimpsey said:
The problem is that XL, like all spreadsheets, uses IEEE double
precision floating point math. This means that all values are stored
using a finite number of binary digits, corresponding to about 15
decimal digits of precision.

Just as most numbers (e.g., one-third) cannot be exactly represented in
a finite number of base-10 digits (i.e., 0.333333333333333 <> 1/3), most
numbers cannot be exactly represented in a finite number of binary
digits.

While IEEE math uses several techniques to minimize rounding errors,
it's not capable of catching all of them.

See http://cpearson.com/excel/rounding.htm for more.

NOTE: It's usually better to explicitly ROUND(), or to decide what order
of magnitude such rounding error may generate, and make a comparison
accordingly, e.g.:

=IF(ABS(10402.3-10251.6-150.7) < 1E-10, "zero", "not zero")
Click to expand...
 
Back
Top