PS....

I wrote:
So you probably want to correct this

Of course, an alternative is to recognize the fact that those "long numbers"
are indeed what your data are, if my assumption is correct.

So perhaps it is correct to accept and use them as they are.

It's a judgment call that only you can make.


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

"JoeU2004" wrote in message
...
"William McNeill" wrote:
I have an Excel spreadsheet and I am importing data into it from Access.
for some reason I have a lot of extra digits added to the number and the
nunumber is also changed slightly. For instance, the number -0.0034 in
Access becomes -0.00340000000000007 and -0.0042 becomes
-0.00419999999999998.

Apparently, those are the "exact" values as they are represented in
Access, up to 15 significant digits.

I say that because in Excel, the constants -0.0034 and -0.0042 are
represented internally exactly as
-0.00339999999999999,981056819642333266529021784663 20037841796875 and
-0.00419999999999999,974048536799386965867597609758 3770751953125.

(The comma is my way of demarcating the first 15 significant digits.)

Moreover, the difference between the Access values and the Excel constants
is sufficient enough for them to be considered unequal.

So you probably want to correct this, unless you can tolerate it in your
Excel application.

I don't know anything about importing from Access into Excel. So I don't
know if there is an easy remedy.

Unless someone offers one, I would suggest that you do the following.

If the imported Access values are in A1:A100, and you want 4 digits of
precision, enter =ROUND(A1,4) into B1 and copy down through B100, then
copy B1:B100 and paste-special-value into A1:A100.

PS: As noted, the simplest solution might be to tolerate it in your Excel
application. Numerical "aberrations" like these are likely to arise in
your Excel calculations, too; so you will probably need to tolerate them
anyway.

You "tolerate" them by the prolific, but prudent use of the ROUND()
function in all of your formulas.

Alternatively, use "fuzzy logic" when comparing values. For example,
instead of IF(A1=B1,...), you might use IF(ABS(A1-B1)<0.0001,...).


I have formatted the cells to show only 4 digits, which it does,
but when you have the cell selected, you can see the very long
number in the box. It seems that Excel is using the long number

Correct. Formatting only affects the appearance of displayed values. It
does not change the underlying value. The underlying value is used in
computations, unless you explicitly ROUND them. For example,
IF(ROUND(A1,4)=ROUND(A2,4),...).

For an average, you might use an array formula like the following
(commited with ctrl+shift+Enter instead of just Enter):

=AVERAGE(ROUND(A1:A100,4))


I wonder if there is something that I am doing wrong.

Probably not. It's a side-effect of how Excel, Access and most
applications store and use numbers with decimal fractions on binary
computers. For overwhelming details, see the following:

http://support.microsoft.com/kb/78113/en-us

http://support.microsoft.com/kb/42980


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

"William McNeill" wrote in
message ...
I have an Excel spreadsheet and I am importing data into it from Access.
for
some reason I have a lot of extra digits added to the number and the
nunumber
is also changed slightly. For instance, the number -0.0034 in Access
becomes
-0.00340000000000007 and -0.0042 becomes -0.00419999999999998. I have
formatted the cells to show only 4 digits, which it does, but when you
have
the cell selected, you can see the very long number in the box. It seems
that Excel is using the long number in instances where I am doing
averages
and other functions. What can be done about this? I get new data in
Access
weekly and then refresh in Excel afterwards, and I wonder if there is
something that I am doing wrong. Thanks!!!