A wrong calculation by excel
 

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75
--
BobD


"JMB" wrote:

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

As a time value, the difference between 8.74999999999999 and 8.75 is less
than 1 nanosecond. Is that not accurate enough for you? What are you
measuring that requires greater precision?

"BobD" wrote in message
...
The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75
--
BobD


"JMB" wrote:

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the
following
formula into a cell, it produces a result of 8.74999999999999 if you
expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use
17.15 and
8.3. I came across this when using a process to convert times to
decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

The problem arose because I have an error message built into my spreadsheet
that only appears if the calculated value using the formula is less than
another value calculated by a different means. This then creates a problem
as we are instructing the users that error messages must be cleared, but in
this case, the error message is incorrect. The spreadsheet calculates
working hours and then determines how much are ordinary hours, how much is
overtime, shift penalties etc. The bizaar thing is that it only happens with
a start time of 8.30 and finish at 17.15. If you change either of those two
by even a minute, it doesn't happen. For example, start time of 7.30 and
finish time of 16.15 produces exactly 8.75 hours.
--
BobD


"Stephen Bye" wrote:

As a time value, the difference between 8.74999999999999 and 8.75 is less
than 1 nanosecond. Is that not accurate enough for you? What are you
measuring that requires greater precision?

"BobD" wrote in message
...
The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75
--
BobD


"JMB" wrote:

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the
following
formula into a cell, it produces a result of 8.74999999999999 if you
expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use
17.15 and
8.3. I came across this when using a process to convert times to
decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

On Wed, 9 May 2007 17:44:00 -0700, BobD wrote:

The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75

Here's a link to an explanation of why it does occur, and what you can do about
it.

http://support.microsoft.com/kb/214118/en-us
--ron

A wrong calculation by excel
 

Have you read the link??

"BobD" wrote:

The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75
--
BobD


"JMB" wrote:

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

Thanks Ron - very useful. I am using the precision as displayed option and
it fixed the issue.

Cheers

--
BobD


"Ron Rosenfeld" wrote:

On Wed, 9 May 2007 17:44:00 -0700, BobD wrote:

The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75

Here's a link to an explanation of why it does occur, and what you can do about
it.

http://support.microsoft.com/kb/214118/en-us
--ron

A wrong calculation by excel
 

Excel, like almost all computer software does binary math. Most terminating
decimal fractions are non-terminating binary fractions that can only be
approximated (just as 1/3 can only be approximated as a finite decimal
fraction).

The decimal value of the binary approximation to 17.15 is
17.14999999999999857891452847979962825775146484375 , so that the first term in
your calculation, =17.15-17, correctly returns
0.14999999999999857891452847979962825775146484375, which Excel displays to
its documented 15 digit limit as 0.149999999999999

Similarly, the decimal value of the binary approximation to 8.3 is
8.300000000000000710542735760100185871124267578125 , so that another part of
your calculation, =8.3-8, is
0.300000000000000710542735760100185871124267578125 , which Excel displays to
its documented 15 digit limit as 0.300000000000001.

The final subtraction, which you presume to be 17.25-8.5 is actually
17.24999999999999644728632119949907064437866210937 5
-8.500000000000001776356839400250464677810668945312 5
----------------------------------------------------
8.749999999999994670929481799248605966567993164062 5

Excel's math is exactly correct, given the initial approximations to numbers
that cannot be exactly represented in binary. The rounding that solved your
problem works because for the type of calculations you are doing, the net
result is to reduce unwanted residues of these approximations. More
generally, standard programming practice for over half a century is to not
test for exact equality with floating point calculations, but rather to test
for approximate equality, where the acceptable level of approximate equality
is defined by the type of calculations being done.

Jerry

"BobD" wrote:

The rounding error should not occur. If you do the calculation with a
calculator, it produces an answer of exactly 8.75
--
BobD


"JMB" wrote:

Rounding error. Use Round function to 2 decimal places.

http://cpearson.com/excel/rounding.htm


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

--
E. Polizzi


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

Your problem is exactly the same as mine. I found that there was an error in
the 13th decimal place when subtracting exactly $1,015.00 from $1,183.30!
Get around this error by writing a formula that accepts a calculation as
correct if it matches the expected answer to within $.01 or within .01%, etc.
example: IF(AND(A1<B1+.01,A1B1-.01),"OK","error").
--
E. Polizzi


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15 and
8.3. I came across this when using a process to convert times to decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD

A wrong calculation by excel
 

This so-called error is the result of the computer converting decimals
numbers (base 10) to binary (base 2)
See one or more of these
IEEE 754
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precision
http://support.microsoft.com/default...NoWebContent=1

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Captain" wrote in message
...
Your problem is exactly the same as mine. I found that there was an error
in
the 13th decimal place when subtracting exactly $1,015.00 from $1,183.30!
Get around this error by writing a formula that accepts a calculation as
correct if it matches the expected answer to within $.01 or within .01%,
etc.
example: IF(AND(A1<B1+.01,A1B1-.01),"OK","error").
--
E. Polizzi


"BobD" wrote:

I am using Excel 2003 Professional Edition. If you include the following
formula into a cell, it produces a result of 8.74999999999999 if you
expand
the decimal places to 16. The correct answer is 8.75. The formula is
((17.15-17)*10/6+17)-((8.3-8)*10/6+8). This only happens if you use 17.15
and
8.3. I came across this when using a process to convert times to
decimals
and then subtract one from the other. Anyone have any ideas?
--
BobD