digits being added on importing

[William McNeill]

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!!!

 

[sally t]

I'm sure there are other ways but Excel is using 15 digits wherever it can so
when numbers are a result of calculations they can be v. long. You could
always incorporate the ROUND function somewhere which will force the number
(in a new column) to round to the specified places - this example is 4
places. Then you could use that as a paste special Values if you want to
completely remove the 15 digits for good (or record all that on a macro
maybe)?

=ROUND(A1,4)

this would take contents of A1 and create a new number but rounded to only 4
places - could choose 2 or 6 places etc etc

Sall

 

[JoeU2004]

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,98105681964233326652902178466320037841796875 and
-0.00419999999999999,9740485367993869658675976097583770751953125.

(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 -----

 

[JoeU2004]

PS....

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 -----

 

[William McNeill]

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!!!

 

[William McNeill]

Thanks for your solutions. I will try them. I guess my concern is when I
refresh the data, how will the round function or the refresh function react.
I will try and see. Thanks again!!!