Subtracting numbers

[Benjamin Black]

Hello, I seem to be having a problem doing the simple task
of subtracting numbers. I'm currently using Excel 2000. I
am trying to do this:

=19.95-19

However, when you format the cell to show decimal places
to 20 places it gives an answer of:

0.94999999999999900000

When clearly it should be 0.95. When I round it to 2
decimal places it does give 0.95, but it uses the full
number in further calculations which causes errors. I can
use the ROUND(19.95-19, 2) to give the correct answer, but
I'm not allowed to use this function.

What am I doing wrong????

Thanks
Benjamin Black

 

[Frank Kabel]

Hi
this is not a bug. See
http://support.microsoft.com/default.aspx?scid=kb;en;78113
http://support.microsoft.com/default.aspx?scid=kb;en;42980

 

[Stephen Bye]

I have to ask...
What do you mean by "I'm not allowed to use this function"?

 

[Jerry W. Lewis]

The references that Frank provided note that most terminating decimal
fractions are non-terminating binary fractions which can only be
approximated with finite precision. The 53-bit binary approximation to
19.95 is
19.949999999999999289457264239899814128875732421875
Excel correctly subtracted 19 from this approximation to get the answer
that you reported. The same thing would happen in any general purpose
computing package, since all use IEEE double precision binary arithmetic.

Since you know that any nonzero digits beyond the second decimal place
are junk left over from the decimal to binary conversion, using the
ROUND() function is the best way to get rid of it.

Formatting to show 20 decimal places is misleading here, since Excel
will never display more than 15 significant figures (the rest will be
arbitrarily zeroed) as documented in Help.

Jerry

 

[Benjamin Black]

I was making a bank note/coin calcualtor for a businesss
studies course and you're only allowed to use a certain
array of functions.

 

[Frank Kabel]

Hi
try
'Tools - Options - Edit' and check 'fixed decimal'

 

[Gord Dibben]

Frank

For what purpose?

Gord Dibben Excel MVP

 

[Frank Kabel]

Hi Gord
(meant precision as displayed). If the OP is not allowed to use ROUND
as a function I'd use this setting
But maybe i got the OP wrong

 

[Gord Dibben]

Frank

Assumed as much but just checking as in other post<g>

Gord